• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

OLAP - Basic How To

I have a database that is a series of normalized and denormalized tables from various sources in a banking enterprise.  

In trying to build a basic OLAP cube should I create a star schema from these sources and link them with primary and foreign keys?

0
michaelrobertfrench
Asked:
michaelrobertfrench
  • 2
3 Solutions
 
RWrigleyCommented:
Essentially, yes, but it depends on what kind of cube you're building.  If you're looking at SQL 2005, then creating a data warehouse will make building the cube much simpler, and will help ensure that you're able to get predictable and reliable results.  However, it's always possible to build your cube directly against the raw data.  The difference will be the amount of time it takes to process the cube.
0
 
srnarCommented:
Do you really need an Olap? Will be end users doing analysis (using slicing, drill down etc.) or do you have end users only in reports? Are there some complicated aggregations (many levels,  distinct counts)?

As each of layers: it takes time to build, process and maintain.
0
 
michaelrobertfrenchAuthor Commented:
This is a slice dice analysis type operation.  
A typical question posed to the OLAP might be:

How many transactions are made each month for those in a given credit score?

Basically, I am taking aggregates like:
Credit Card purchases, payments, interest paid, etc for each period

And putting them into dimensions like:
Branch Location, APR, credit score, number of transactions etc

I have written a sp that takes an integrated database(which has a utility in and off itself unrelated to OLAP) and creates a star schema that for my aggregates and dimensions.

Is this a typical method of deriving cubes and setting up an OLAP?
That is, a stored procedure to create an OLAP from an OLTP.
0
 
RWrigleyCommented:
No, the more typical solution would involve a ETL process to extract the data from your OLTP environment, transform it and load it into a dedicated data warehouse.  

As a general rule, OLTP database's are optimized for rapid posting of new data.  You wouldn't normally want to do any kind of reporting (OLAP or otherwise) against these database, becuase they dont' have the indexes required to process these efficiently.  And you wouldn't want to ADD those indexes to an OLTP database, becuase of the potential performance hit (indexs increase write time).

What IS somewhat common is using a SP as part of the ETL process, especially in cases where the ETL process isn't all that robust or is spanning database types.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now