Recently started implementing multidimensional model in SSAS. OLTP has a table which stores the multiple measures based on Attribute and Entity columns. Attribute column has various dimension names. If in a row the Attribute value is Dim1 then Entity column will have Dim1's Value. Similarly, Attribute column can have any dimension name and its value will be in the Entity column. We also have some tables where we have multiple Attributes and entity columns. e.g. Attribute1 and Attribute2. Both are dimension names and entity1 and entity2 are storing their respective values. Measures also depends on the order of the dimensions. To be specific, tables are storing the Value at the Risk(VAR) values calculated at different compression. VAR calculated at fund and Sector compression are different from the VAR calculated at the Sector and fund compression. OLTP stores it as attribute1 (Fund),Attribute2(Sector) and Entity1 (Fund's value) and Entity2(Sector's value). Querying it is also easy with where caluse [where attribute1 = 'Fund' and attribute2 ='Sector'] or [where attribute1 = 'Sector' and attribute2 ='Fund']
How this can be effectively modeled in a Cube?

My current approach is creating a Fact table which has a separate not null foreign key column for each each dimension . If i need to save the data for the dimension1 then its foreign key value will be saved in dimension1 (which is foreign key to Dimension1 table) other dimension keys(dimension2,dimension3..) will point to N/A value of respective dimension.

How this approach can be improved? Pros and cons ? How the ordering of the dimensions can be achieved in cube design

Thanks

Praveen

Hi ANREV107,

According to your description, you have attribute column storing different dimension names. It's quite strange to store data in this way because each dimension will slice the fact data into different grain. If you only use one column to store all dimensions' names in you fact table, you can never build the relationships between fact data and all dimensions.

For example, sales amount can be sliced on both product level and date level, which means in the fact table, every record should have date key and product key. If you only use one key column for all dimensions, how can we know the sales amount of Product A in Year 2012?

So in the fact table, you must have different columns for different dimensions because fact data is related to all dimension. There's no significance to make a "huge" dimension containing all dimension which is related in fact table through only one column. 

Regards,