Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Monday, May 6, 2013 11:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
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?

Thanks
Post #1449814
Posted Tuesday, May 7, 2013 6:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:54 AM
Points: 820, Visits: 2,117
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