|
Home > Archive > MS SQL Data Warehousing > January 2006 > Ad-Hoc Reporting on DW with many Slowly Changing Type 2 Dimensions
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Ad-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...)
"Bruce" <bruce@defreitas.com> wrote in message
news:1138288149.090867.192040@g14g2000cwa.googlegroups.com...
>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
>
| |
|
| Hello J=E9j=E9,
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=E9j=E9 wrote:[color=darkred
]
> 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...)
>
> "Bruce" <bruce@defreitas.com> wrote in message
> news:1138288149.090867.192040@g14g2000cwa.googlegroups.com...
| |
|
| 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!!!
"Bruce" <bruce@defreitas.com> wrote in message
news:1138339447.692671.277810@z14g2000cwz.googlegroups.com...
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:[color=darkred
]
> 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...)
>
> "Bruce" <bruce@defreitas.com> wrote in message
> news:1138288149.090867.192040@g14g2000cwa.googlegroups.com...
|
|
|
|
|