how create and query a cube olap

I'm not familiar with ORACLE, so I'm not up with the OLAP features that may or may not be built into the product. But in essence to get Cube type functionality from a relational engine, you need to build a Star or Snowflake schema represenation of your data.

This involves categorising your data between FACTs and DIMENSIONs.

Put simply, FACTS are the things you want to analyse, count, averag, aggregate etc. Examples of which are "OrderValue", "Quantity", "Count" etc. As you can see all of these are numeric types, so arguably FACTs are always numeric fields.

DIMENSIONs on the other have are the things you want to analyse by, or the things that describe the FACTs. Examples of dimensions would be "Customers", "Products", "OrderDate" etc.

So, if I have an "OrderValue" of £100, I can say that this £100 was spent by "Customer" ABC, on "Product" Widget on "OrderDate" 24/03/2010. Hence the dimensions describe each fact

You place all of your FACTs into a FACT table, and your DIMENSIONs into separate DIMENSION tables, one for each. Then link the individual DIMENSION rows to the fact using keys.

eg.

FACT table = FAC_ORDERS (one row for each Fact to be aggregated) Structure FactID, OrderDateID, CustomerID, ProductID, OrderValue, OrderQty

DIMENSION table = DIM_Product Structure ProductID, ProductName

DIMENSION table = DIM_Customer Structure CustomerID, CustomerName

DIMENSION table = DIM_TimeDay Structure OrderDateID, ActualDate, Year, Month, Week, Day

Alternatively, if you have access to a SQL Server with Analysis Services running on it, you can avoid this by building an OLAP Cube in SQL Server using a tool like CUBE-it Zero. It's a FREE SQL Server OLAP Cube building tool that builds OLAP Cubes in SQL Server Analysis Server as it's data source, but there is an optional add-on you can buy for taking data from ORACLE as well. check it out at http://www.bi-lite.com

Good luck Rob

ansaurus

tags:

views:

answers:

how create and query a cube olap

related questions