Data Mart/ Data Warehouse question

[HouDog]

Hello Everyone,

I just a very general question. I have a client application that uploads real-time sales data to a web site which is then stored on a SQL Server backend.

Potentially, the size of my sales data tables can become enormous causing my online reports to take a very long time to process the rows and rows of data.

As an alternative, I want to offer a summarized view of the data. Initially, I considered writing an Insert Trigger to propagate the data into another summary table containing running totals and stuff but I'm not sure if this is the way to go.

I want to make all the data available but i also want better performance.

I have been reading a bit on Data Warehouses and Data Marts and wonder if either one of these might be an alternative solution.

I am looking for any thoughts anyone might have.

Thanks a bunch.

 

[JayKusch]

Well ... going the datawarehouse / datamart route is a totally diffrent beast than relation databases.

You first will have to install Analysis Services(AS), if going the MS path.

You will then need to build your schema to be condusive for AS to use. These schemas will usually fall into the categories of a star or in some cases a limited snowflake design.

We then port your current data into the new schema. This can be done w/ SQL commands or DTS.

Once in place you fire up AS and design your cubes. Once the cubes are in place you need to find a tool to report against your cubes. Usually to start out w/ you can use Excels Pivot Table Services. I only say to use Excel as a beginner tool because most folks running SQL Server usually have MS Office around. I am sure you will hear other folks pipe in on the tools of choice when this thread gets going.

You then will need to get versed w/ MDX, Multidimensional Expressions, in order to get intimate with your cubes. This is analogous to T-SQL that is used to interogate SQL Server Databases/Tables.

SO ... as you can see, it is quite a leap to go to AS to get your reports. You need to review if this option is adventageous to your company.

On the other side of the fence, you could create aggregation tables as you eluded to. This will take alot of processing to keep these numbers "real-time" but maybe a night run to create the aggregates may be afforded to you.

SO ... educate yourself on AS and that may be the way to go. It seems many business are heading down this path and maybe now is the time for you to get into the game.

Good Luck!

Thanks

J. Kusch

 

[HouDog]

Thanks Jay,

Valuable insight. It seems I have a lot more reading to do. I'm also left wondering what other companies are doing in situations like this where there is a lot of raw data that is required for reports and such. Hmmm.

Thanks again for your reply.

JH.

 

[JayKusch]

If you are think about the OLAP solution, Analysis Services, you may want to peruse this forum ... forum960

Thanks

J. Kusch