 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / January 2006Ad-Hoc Reporting on DW with many Slowly Changing Type 2 Dimensions
A requirement of our DW project is that we store Dimensional changes, and it appears the SCD Type 2 method sounds best for that.... But say you have 30+ Dimension tables, all SCD T2. Another requirement is the users can do Ad-Hoc reporting, where they could potentially join many of those Dimensions together for point-in-time reporting. If you go SCD T2 then if you even do know the typical combinations of joins, for fact tables, the combination of Dimension Keys for daily snapshots, could get very large. We could deal with those typical joins, but wonder if anyone has ideas on the Ad-Hoc queries against NON-typical joins of SCD T2 Dimension tables? Am considering a 2 column Key on the SCD T2 tables to keey the base ID and add a sequence # column. Any thoughts on that method, which would at least keep the ID of the SCD T2 Dimension the same across rows. I don't see much on this Ad-Hoc issue, seems most references talk about Fact tables where joins back to Dimensions are known. Is an answer to maybe have some very large factless fact tables, and link them together somehow maybe? Thanks, Bruce which ad-hoc tool do you use? why do you use 2 keys (key + sequence) instead-of a unique surrogate key? (reducing the columns in a join improve performance) what are "non typical" joins for you? what is your RDBMS server? (I presume SQL Server...) >A requirement of our DW project is that we store Dimensional changes, > and it appears the SCD Type 2 method sounds best for that.... But say [quoted text clipped - 16 lines] > > Thanks, Bruce Hello Jéjé, They'll be using Cognos for reporting, but I'm sure there will be various reporting tools in that mix. The 2 column composite key, was for the Slowly Changing Dimenions, that are Type 2. I'm not clear on how to relate numerous dimensions together in factless fact tables (?), so users can join them in reports.... and then add the fact that the Dim tables are all SCD T2 type... so if you have 30-50 DIM SCD T2 tables, we'd want to relate the Dims for the users, especially if they're going back in time for snapshot data. Byt typical joins, it would be the Dimensions that are normally joined, like what might end up in typical canned reports.... but if the Ad Hoc user wants to join a number of Dims in some strange report, then those are the non-typical joins and not sure how to represent THOSE in a fact table... again, they could join any of 30 Dim tables, not all those but any combos (yes that somewhat are related)... The database platform will be SQL Server 2005... Thanks, Bruce > which ad-hoc tool do you use? > why do you use 2 keys (key + sequence) instead-of a unique surrogate key? [quoted text clipped - 22 lines] > > > > Thanks, Bruce Does it cognos reportnet? giving access to everything its not the solution. try to propose reduced models with only required dimensions for specific answers only. you'll reduce the risk to generates bad queries. create sample reports has source (or templates) to help the users. don't use composite keys, use only simple keys to force the user to go through the dimension table and to improve performance. Verify if you really need to give access to all the historical data, maybe 1 year of data is enough for most of the queries. (then only summary data for the history) setup SQL Server to not run too heavy queries (query governor limit) and finally train your users!!! there is no miracle, if you give access... the users can do everything!!! Hello Jéjé, They'll be using Cognos for reporting, but I'm sure there will be various reporting tools in that mix. The 2 column composite key, was for the Slowly Changing Dimenions, that are Type 2. I'm not clear on how to relate numerous dimensions together in factless fact tables (?), so users can join them in reports.... and then add the fact that the Dim tables are all SCD T2 type... so if you have 30-50 DIM SCD T2 tables, we'd want to relate the Dims for the users, especially if they're going back in time for snapshot data. Byt typical joins, it would be the Dimensions that are normally joined, like what might end up in typical canned reports.... but if the Ad Hoc user wants to join a number of Dims in some strange report, then those are the non-typical joins and not sure how to represent THOSE in a fact table... again, they could join any of 30 Dim tables, not all those but any combos (yes that somewhat are related)... The database platform will be SQL Server 2005... Thanks, Bruce Jéjé wrote: > which ad-hoc tool do you use? > why do you use 2 keys (key + sequence) instead-of a unique surrogate key? [quoted text clipped - 22 lines] > > > > Thanks, Bruce |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.