August 2, 2011 at 4:47 pm
Hi,
I have the whole SQL server 2008 suite, and I'm currently extracting many reports using views.
The thing got more complex than expected and now I have bought a new server in which I want to build a Data warehouse and copy my transactional DB to a warehouse on the new server for converting it into a reporting server.
The thing is that I already have the views that extract my reports and now I want to build a fact table for each view. The issue is that the views use many tables, subqueries and functions, joins and all kinds of stuff to be able to generate them, so I don't know how to start.
I would like to know how to use several data OLE DB data sources, and how to build the many joins of the view. I know how to do the lookups from one transactional table to "fill" or "update" one dimensional fact table.
Any suggestions, reading, videos or any kind of help that I should use?
Thanks in advance,
Vic
August 9, 2011 at 1:49 am
I'd suggest you'd read the Datawarehouse Toolkit by Ralph Kimball on how to design/model a data warehouse.
Once you've modeled it as a star/snowflake schema, you can populate it with data using SSIS.
On top of that, you build your SSAS cube.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 10, 2011 at 3:59 am
I agree with the above, other than to say that you should be aiming for a star schema. There are uses for the snowflake, but in most cases it's an attempt to normalize the db according to OLTP rules even though those rules to apply to OLAP, the form a data warehouse database would take.
This is a very different approach to design, so be prepared that the final product is going to be, from my read of your expectations, very different than what you expect.
August 10, 2011 at 7:50 am
I agree with the other comments if you have the time and desire to build a data warehouse. It is a big task if you are trying to learn dimensional modeling at the same time, data warehousing theory is not easy.
If that seems too big, what you might want to try is creating sets of reporting tables on your new SQL Server instance, the one to be used for reporting. Then you can use SSIS to ETL data into the reporting tables periodically. Once the data is populated, you can point your existing reports to the tables instead of the views, reducing load on the transactional system. Also, after you have it setup, index the tables to boost report run times.
I know this is not the proper BI solution to your problem, but sometimes small steps can work out better. But, this also is very limited, for example it will not support SSAS well. If you plan to use cubes, go with a dimensional model like the other people suggested.
August 15, 2011 at 1:40 pm
I think Ryan is onto something. I'm not sure you need a full on data warehouse, nor have the time, etc., to build one, but, a reporting server could be very helpful to you.
I'd list out the tables used in your reporting set and see if you can do some flattening out from the relational to the denormalized. Could speed up your reports since many joins would be gone, you control indexing and the weight comes off the production system.
August 17, 2011 at 6:22 am
I was also thinking you could periodically load data using a scheduled task that executes a stored procedure to "refresh" the data, or perhaps add more data based on transaction date. Would this be a viable solution?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply