Building Cubes with No DataWarehouse

[Nassy]

Hi,

I do a lot of on-site client work. I just wanted to get some general advice on something.

Suppose a customer asks for some cubes to be created. If it is clear that the star schema created to source these cubes would have no function other than to serve these very same cubes then would there by anything wrong with doing the following ? :


1. Creating Views in SQL for the Fact and Dimension Tables

2. Loading the Dimensions and Fact Table nightly.


Or is this too simplistic? Other than maybe to increase performance, I can not see any reason why for example I would want to get into building a data warehouse incorporating slowly changing dimensions or anything approaching that sort of complexity.

I'd be very interested to hear different peoples' opinions on this.

cheers

Nassy

 

[MDXer]

The basis of OLAP is designed for high performance reporting. Just as the OLTP system is designed for High performance of data insertion. Can you build cubes off and OLT system? Yes. Should YOU? in my opinion no.

A DW designed to support cubes will be created with everything geared towards getting the data out of the DW as quickly as possible. THis would entail star schema, Physical Dimension and fact tables.

If your worried about encorporating SCDs into a star schema then it sounds like it is needed for solid reporting. Building SCDs of a OLTP system would probably be extremely difficult using a view(s).

If this is a Customer requesting this and you are unsure of how to proceed then you really only have 2 options
1) Read and research Cubes OLAP and DW until you are comfortable in providing the Solution.
2) Find someone more versed in these technologies and refer your client to them.

Option 2 would probably be the more respected route in that many OLAP implementations FAIL, are shelved, or take much longer and cost much more simply for lack of planning. Giving the failure rate you would not tarnish you reputation by being responible for the potential failure.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

Obviously a solution such as yours would be impractical for 24x7 operations or global solutions.

However, one of the major tenets of DW is to trade off-hours cycles for reduced query times during peak user hours. Your solution will have met that tenet.

In addition, another tenet is to take the non-user friendly relational data model and switch to measures and dimensions which are more user friendly. This is also accomplished.

You mentioned slowly changing dimensions - this concept is either a business requirement or not. There is no technical reason to apply SCD if the user does not require it.

Now for some of the drawbacks.

Drill-through to detail, an OLAP feature, will be awkward due to the views and underlying OLTP structure. Again, if this is not a user requirement, no problem.

You do not have any audit trail for anything produced in the past as you have indicated that you will overwrite every night. Reports produced on older cubes can no longer be validated. Again, if this is not a user requirement, no problem.

Another principle of DW is to transform or conform data. This may or may not be possible.

You have design a simple solution, if the users later want more, you will be forced to reinvent the process anyway.

There are probably some other drawbacks which if I revisit this forum, I will list.

The most important aspect to your situation is this....
The business has a problem. If you can solve the problem at user acceptance levels of Quality, Cost, and Timeline, you have achieved success at some level. Many projects cannot deliver such results.







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

 

[Nassy]

Hi,

Thanks for the replies. My first step is to do a thorough analysis of the business requirements. Fistly I wouldn't build cubes directly from an OLTP system unless it was a last resort - most likely I would look to copy data into a staging area, do transformations and then pipe the data into a star schema. Change management (Slowly changing Dimensions) would really complicate things and I would only add SCD's to the mix if it was clear it was necessary.

One of the things I find that makes datawarehousing such a complex topic is that there are so many articles that tell you 'how things should be done' (sometimes contradictory) and this can detract from the need to really think about solutions and use common-sense...

cheers

Nassy

 

[KingCrab]

Anything that tells you 'how things should be done' should always be translated to 'how things could be done'

There are to many variables in systems, requirements, abilities, and the weather to make a DW a "my way or the highway project"

Books are good to read for ideas but are impractical to put into practice 100% on site.


My 2 cents