Big questions on data warehouse architecture - urgent help needed!

Hi,
I am a relative newcomer to data warehousing and OLAP. We are in the process
of planning a general way forward and an architecture for our warehouse, and
I am seeking advice as to best practice ways forward. We are using SQL
Server 2000, and will extend this with Analysis Services as and when
necessary. The ultimate goal of the project is to provide online reporting
capabilities using the new SQL Server Reporting Services, early next year.
My biggest headache is in designing our dimensions. I have ploughed through
books by Ralph Kimball ( => the authority on dimensional analysis, and
denormalised data warehouse structure with multiple marts making one big
whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
warehouse, with dimensional data marts hanging off this). We are likely to
take the Imhoff route and have a relational DW, largely because we want to
have a place to keep historical data in a strcuture similar to the structure
it originated from. But we will use a lot of ideas from Kimball to construct
our data marts.
To the main headache...We have a number of dimensions that originate from
recursive relational tables (namely 'geographic region', 'market sector' and
'business division'). They all have unpredictable numbers of levels in their
hierarchy, and they are type 2 (slowly changing) dimensions - so it's
important to preserve historical relationships. These recursive tables also
are snowflaked - i.e. they form many/many relationships with other dimension
tables.
My question is how we should collapse/explode/denormalize these dimensions?
For our first stage of work we don't really need to do OLAp style analysis -
we can just report on our data as-is - OLAP analysis will come later.
Kimball suggests using 'bridge' tables to descibe the parent/child
hierarchies - these bridge tables are built at the DW load stage - does
anyone have experience doing this?
OR, could we use Analysis Services to handle the recursion and snowflaking -
AS makes big claims about how efficiently it can do this - and then create
cubes from our fact/dimensiosn and report from this?
Any suggestions or criticisms or anything else very gratefully received. If
I am barking up the wrong tree, then please let me know - as I said, I am
new to this.
Thanks, in anticipation.
Chris LewisHi
You may want to ask people of OLAP newsgroup.
"chrislewis@.etsolutions.com" <chrislewis@.etnospamsolutions.com> wrote in
message news:3fe6e368$0$45676$65c69314@.mercury.nildram.net...
> Hi,
> I am a relative newcomer to data warehousing and OLAP. We are in the
process
> of planning a general way forward and an architecture for our warehouse,
and
> I am seeking advice as to best practice ways forward. We are using SQL
> Server 2000, and will extend this with Analysis Services as and when
> necessary. The ultimate goal of the project is to provide online reporting
> capabilities using the new SQL Server Reporting Services, early next year.
> My biggest headache is in designing our dimensions. I have ploughed
through
> books by Ralph Kimball ( => the authority on dimensional analysis, and
> denormalised data warehouse structure with multiple marts making one big
> whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
> warehouse, with dimensional data marts hanging off this). We are likely to
> take the Imhoff route and have a relational DW, largely because we want to
> have a place to keep historical data in a strcuture similar to the
structure
> it originated from. But we will use a lot of ideas from Kimball to
construct
> our data marts.
> To the main headache...We have a number of dimensions that originate from
> recursive relational tables (namely 'geographic region', 'market sector'
and
> 'business division'). They all have unpredictable numbers of levels in
their
> hierarchy, and they are type 2 (slowly changing) dimensions - so it's
> important to preserve historical relationships. These recursive tables
also
> are snowflaked - i.e. they form many/many relationships with other
dimension
> tables.
> My question is how we should collapse/explode/denormalize these
dimensions?
> For our first stage of work we don't really need to do OLAp style
analysis -
> we can just report on our data as-is - OLAP analysis will come later.
> Kimball suggests using 'bridge' tables to descibe the parent/child
> hierarchies - these bridge tables are built at the DW load stage - does
> anyone have experience doing this?
> OR, could we use Analysis Services to handle the recursion and
snowflaking -
> AS makes big claims about how efficiently it can do this - and then create
> cubes from our fact/dimensiosn and report from this?
> Any suggestions or criticisms or anything else very gratefully received.
If
> I am barking up the wrong tree, then please let me know - as I said, I am
> new to this.
> Thanks, in anticipation.
> Chris Lewis
>
>