Build cubes to distribute to many customers/servers 2

[MissouriTiger]

I would gratefully appreciate any ideas suggestions.

I'm a junior developer at a small software company (on the job less than 2 weeks). Our software utilizes a MS SQL Server database with over 250 tables, and our customers run their entire business on this software & DB.

We want to build some standard cubes to distribute to all of our customers to allow them to more easilly analyze their data. Is it possible to do this? If so, how?

Any suggestions are most welcome.

-Greg

________________________________________________
Constructed from 100% recycled electrons.

 

[johnherman]

You need to do dimensional analysis and then dimensional modeling. First, find out the most popular reports being used. This will help identify the measures or facts you need in the cube(s). Next, look at the reports' selection criteria. Anytime you see or infer the word "by", you are looking at a dimension. For instance, Sales by Region infers a Region dimension. Time will always be a dimension. Another clue as to the important dimensions is to look at subtotals or report breaks. If there are subtotals by sales representative, then Sales Rep is a likely dimension.

The next step is to analyze the dimensions for dependencies. If there are sales by State and also by Region, and if a Region consists of one or more States, then the dimension has a hierarchy with States rolling up into Regions. If Sales Representative has many regions, then Sales Representative becomes the next higher level. Then if Sales Reps roll up to District Managers, then you have another level. For this example, the hierarchy from lowest to highest is:

State --> Region --> Sales Rep --> District Mgr

If you want additional help, after you have done a preliminary dimensional analysis, list all of your potential dimensions and we'll try to do a basic model. This is not trivial stuff - people make a living doing nothing but dimensional modeling. It's not difficult to do, but it is difficult to do properly.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[MissouriTiger]

Hi John,

Wow, I really appreciate the information. As usual, the good folks at Tek-Tips come through.

Fortunately, my mentor here has 9 years experience in development here, and has a pretty good idea what reports are needed, and is giving me some guidance. However, OLAP is brand new to him to, so I am really grateful for your willingness to help.

I'll be back in touch.

Thanks again,

Greg Norris



________________________________________________
Constructed from 100% recycled electrons.

 

[justins57]

MT,

While modeling your dimensional data from reports is a start there are many pitfalls to avoid. I recommend you visit the Ralph Kimball website to review design tips for a good dimensional model. He is the leading authority on Dimensional Modeling.

Here is the link:

http://www.kimballgroup.com/

Good Luck!

Justin

 

[MissouriTiger]

Muchos Gracias for all the tips! You guys are the greatest!

-Greg

________________________________________________
Constructed from 100% recycled electrons.