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: http://imgur.com/iKNfAXF Second: http://imgur.com/IZjW1W6

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.

Answer 2

You could construct the dimensions you need by views that based on distinct queries (i.e. SELECT DISTINCT) from the source data. These can be used to populate the dimensions. You can make a synthetic date dimension fairly easily.

Then you can create a DSV that joins the views back against the fact table to populate the measure group.

If you need to fake a primary key then you can use a view that annotates the fact table with a column generated from row_number() or some similar means. Note that this is not necessarily stable across runs, so you can't rely on it for incremental loads. However, it would work fine for complete refreshes.