New Layer in DWH for Reporting

View previous topic View next topic Go down

New Layer in DWH for Reporting

Post  Informer30 on Mon Aug 15, 2011 10:00 am


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

Informer30

Posts : 8
Join date : 2010-07-05

View user profile

Back to top Go down

Re: New Layer in DWH for Reporting

Post  pcs on Mon Aug 15, 2011 12:29 pm

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.


pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: New Layer in DWH for Reporting

Post  ngalemmo on Mon Aug 15, 2011 3:32 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: New Layer in DWH for Reporting

Post  Informer30 on Mon Aug 15, 2011 3:59 pm

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....


Informer30

Posts : 8
Join date : 2010-07-05

View user profile

Back to top Go down

Re: New Layer in DWH for Reporting

Post  ngalemmo on Mon Aug 15, 2011 6:39 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: New Layer in DWH for Reporting

Post  John Simon on Mon Aug 15, 2011 7:42 pm

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.


John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: New Layer in DWH for Reporting

Post  Informer30 on Tue Aug 16, 2011 4:06 am

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

Once again many thanks....

Informer30

Posts : 8
Join date : 2010-07-05

View user profile

Back to top Go down

Re: New Layer in DWH for Reporting

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum