November 12, 2008 at 5:39 pm
We are relatively new to OLAP concepts within SQL Server and we are looking to revise our data warehousing strategy. We have at least 2 data sources that we need to pull data from for reporting. Eventually, we are looking to setup an OLAP database and a BI tool of some sort but for now we need a homogenized data center for reporting purposes. I believe this has been called an Operational Data Store (ODS).
Would it be recommended to start with SSIS to get the data into the homogenized data center then (later) building our OLAP cubes from that data center? Or, would building an OLAP database that takes its data directly from the multiple initial data sources work as the homogenized data center thus eliminating the SSIS layer? We are a little unclear if by using SSAS a new database would be created that we could query via TSQL (or Reporting Services ultimately) or if it creates cubes (instead of tables) that can only be accessed via MDX?
Any advice greatly appreciated.
Thanks.
November 12, 2008 at 6:22 pm
Derek,
I'd advise you to take a look at this book
You're right that an ODS is a pretty good place to start, and yes, you would normally use an ETL tool (such as SSIS) to load the data in to the ODS from your SOP system(s) along with any transformations necessary.
The Kimball methodology often talks of dispensing with this ODS layer and using SSIS to directly populate a dimensional data store (DDS) however many people still find some value in the ODS for reporting.
You're right that you would use SSAS to build cubes, but best practice to do so by building a DDS from which to load the data in to the cubes. These can then be queried by Excel, SSRS, Proclarity (there are also a number of other 3rd party OLAP tools around) using MDX - many tools are out there that have good enough interfaces to save you needing to learn MDX to build OK reports, however if you need to do much beyond the basics you'd be best off learning MDX...
Kind Regards, Will
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply