New Layer in DWH for Reporting

Hi All,

I am in the process of designing a DWH star solution for my organisation with the help of 3rd party DWH experts.

The source systems are relational dbs in SQL Server 2008 r2.

After reading the kimball book, I understand the data must first come into the ODS stage. Then transformed into
staging and finally presentation area i.e. stars for reporting and feeding into the cubes.

However this 3rd party is proposing ods, staging and then a flat tables for reporting purposes and then stars?

I am confused as to what purpose would the stars be then apart from feeding the cubes?

Anyone come across this before? Any advise around this will be good....

Many Thanks

I think you are doing the right thing by questioning the approach presented - they are working for you and should be able to clearly explain and defend the use and purpose of each proposed step.

The ODS is optional. You only build one if you have a need for operational/tactical (i.e. one of) reporting. I have no idea why they would create flat report files before the stars.

Thanks for the replies, to make sure I am getting this right and go prepared please can you advise/confirm the purpose of stars is for operational reporting hence I would assume no need for this flat file structure????

Sorry, but when you say ods is option...is that not going against the purpose of interrupting the live system i.e. ods is a day behind?

Many Thanks for your help....

An ODS is typically implemented in somewhat 3NF which is better suited for short queries of specific information, such as 'what are the pending orders for customer X'. Usually this is used as an adjuct to the operational system to offload the work or for access by users (such as customer service) where it may not be desireable for them to access the operational system directly. If you have such a need then an ODS makes sense.

Star schema are better suited for analytic queries involving larger amounts of data, such as 'what was shipped today, or the past week', 'how do sales compare with last month', etc... If the need is purely analytics for tactical and strategic use, then an ODS is not necessary. If a more operational requirement develops in the future an ODS can always be built and would not directly impact the data warehouse.

So, creating an ODS is purely based on requirements and budget. It is not needed to support analytics or the data warehouse. That is why I say it is optional.

As far as the flat files go, they are superfluous. A properly constructed dimensional data warehouse should be more than sufficient to support reporting.

Also understand that I am speaking in general terms as to the basic architecture of a generic solution. There are certainly circumstances where deviations from the basic architecture make sense.

It sounds to me that they are proposing an ODS because then they can bill you for longer

The only reason to have an ODS is for intra-day reporting. It would also be on a separate server than your DW to reduce IO contention.

As Neil said, the ODS is most probably superfluous. As are flat file tables. These alone would make me question either their skill/knowledge, or their intent.

Thanks All...very helpful....I will chase these points up...

Once again many thanks....