Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Designing a Dim table for SSAS and SSRS Expand / Collapse
Posted Monday, May 6, 2013 11:28 AM


Group: General Forum Members
Last Login: Friday, October 23, 2015 4:25 PM
Points: 153, Visits: 441
New to SSAS. I'm working on my first cube. I'm creating a dim table for the accounts. The level of detail that we would use for SSAS is the accounts county, but for SSRS we would want to be able to show the street address. We want to be able to use the dim tables for reporting out of SSRS as well as the cube. So if I add the street address to the Dim table, but I do not use it as an attribute in SSAS, is that considered a No-No in the SSAS world? Is there a major negative impact on the Cube if I do include the data in the table?

Post #1449814
Posted Tuesday, May 7, 2013 6:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 1,193, Visits: 3,284
There's not enough information here to give you a good suggestion for your case, particularly in terms of dimension record count. Here are some guidelines.

If you absolutely need it there as a business requirement, then you need it. Usually in the dimensions this is not an issue, unless it is a degenerate dimension. If the dimension has about the same number of rows as the fact table, it is probably degenerate.

Street addresses in the fact table are not automatically bad, but they are usually not good. If the fact table is millions of records, the presense of these long fields makes everything slower. Whether it is too slow is up to you and your users. Ideally the fact table consists of pointers to dimensions so that the rows are as small as possible. As long as the trade-off is understood, then do it. I personally prefer my operational reports requiring that detail to come from the operational database.

Post #1450108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse