Suggestions needed: Warehousing

Question

Hi all,

I find myseilf in the following stuation: I have an application with a huge database and external "clients" consuming data from this db. These clients are Excel Pivot Users, Reporting Services Report consumers, other application developers that use/read some of our data  ... 

All the technical interfaces for providing this data to these so called "clients" are spread all over this database in form of Views, Stored Procedures, non-normalized tables that contain copies of other relational tables.... and all of these objects do not have any naming convention and have been modified and added from time to time as needed.

So what I am looking for is a general solution and way out of this mess. I was thinking of creating a second database that is kind of a data warehouse, containing all required data in form of non relational tables, ready to be consumed without any requirement to calculate aggregates on the fly, join tables to lookup descriptive names of primary key values or extract e.g. months, quaters... of datetime fields .. These tables should be filled by carefully planned stored procedure jobs sitting in this new warehouse db and getting the data as needed from the production db every specified period of time.

I looked at analysis services, but I think this high learning curve will never really pay off.
I do not know if integration services could help?
I am thinking about doing all of this manually on my own in form of my own sp's (that partially already exist).

I am also thinking about possible naming conventions - often the most interesting core tables of the application are being used with lots of different combinations, aggregates... so all of those instances have to be well named and documented (and idealy also be shown e.g. on reports footers) for debugging. On the other hand I can't show "Table001" to "Table199" (being filled by "proc001" to "proc199") to my excel pivot users connecting to this warehouse db...

I was hoping anyone having some more experience could give me some directions on this?

TY, Markus

edit: Just realize this might be better placed in the DataWarehouse Forum? http://social.msdn.microsoft.com/Forums/en-US/sqldatawarehousing/threads

Answer 1

HI  Markus Pöhler

Thanks for your posting. I am moving this thread to data warehouse forumwhere more experts will help you out

Many Thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Answer 2

welcome to the BI world,

for creating very fact and robust reports and analytic on your operational databases the final answer is to create a BI solution.

and the BI solution consists of some steps:

1- Create a Data Warehouse with Dimensional Modelling

I strongly recommend Kimball methodology for dimensional modelling, the best resource for this is Kimball's book :The Data Warehouse Toolkit:

http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247

Data Warehouse is a database designed for reporting and analysis , you will learn how to build dimension and fact tables in Kimball's book

2- Transfer data from operational database(s) to Data Warehouse

here you will need SSIS; there are lots of transformations and tasks to help you to transfer data and transform it as you need in the destination DW

for SSIS you can use SSIS books or vides:

http://www.packtpub.com/microsoft-sql-server-2012-integration-services-expert-cookbook/book

http://www.radacad.com/CoursePlan.aspx?course=1

3- You can build OLAP cubes on the top of DW;

OLAP cubes are file multi dimensional structures which will make querying very fast (much faster that querying directly on DW)

for creating OLAP cubes you will need SSAS, there are lots of good books on SSAS

4- you can build your report with SSRS which query OLAP Cubes

also you can use Performance Point to query olap cube with lots of graphs with ability to slice and dice

this is an end to end BI solution

you may want to do this partially, but for having good performance and very fact reports and better analysis on existing data an end to end solution is very efficient. and yes, you will need to learn lots of things for Dimensional Modelling, Data Integration and ETL , OLAP Cubes, Data Visualization... but it all worth to spend time on it.

---

http://www.rad.pasfu.com

Answer 3

Hi Reza,

thank you for this detailed information. 

The problem is, as I already montioned, I can't and do not want to spend weeks for learning all of this at this point. All I want is some practical ideas/suggestions to realize it with my own means. And, in the first instance, I want to do it quickliy. 

TY, M

Answer 4

If you don't have time to spend on the whole end to end BI solution, then just go for SSAS part, make OLAP cubes on existing operational databases and you will see queries will respond much faster.

---

http://www.rad.pasfu.com