Creating an OLAP Cube from a flat table in SSAS/SSRS

Question

I'm new to that topic. I've got a database with a flat fact table, which contain data like date, product group, product subgroup, product actual name, and some calculations/statistics. All I need to do is create a report using olap cube. I have got two ideas how to create that, but dont know which draft is better (if even correct). The original DAILY_REPORT... table has not a primary key. Its just a data table. In first concept I have created every table (which will be as a dimension) with a ID, and connected the product->family of product->project->building in a hierarchy. Another concept is without all ID's and hierarchy. Relation created automatically based on names. Can somebody explain me in which direction I should tend...?

First idea: https://i.sstatic.net/BTd9V.jpg Second: https://i.sstatic.net/aPSpy.jpg

Thanks in advance!

Answer 1

You can follow these steps to create your cube:

1. Create a separate view for each of the dimensions you want to have. Group similar type of data in one view, for e.g. Product Name, Product Group, Product Sub-Group, etc.
2. Keep the data in your dimension view as DISTINCT data. for e.g. SELECT DISTINCT [Product Name], [Product Group], [Product Sub-Group] FROM TABLE
3. Keep an 'ID' column in each dimension view, for e.g. Product ID in Product view
4. Create a view for your fact. Include 'ID' column of each dimension in your Fact view. This will help you to create relationship on 'ID' column, which will be a lot faster than relationship created on top of names.
5. For creating hierarchies in dimension attributes, SSAS provide drag and drop functionality.

If you need more details let me know.