Zulfiqar's web

Olap Cubes: Cube Driven SSRS

Hi all,

Check out this report using SQL SERVER 2005 REPORTING SERVICES (SSRS) driven by AS 2005 olap cube.

Pretty exciting !!!! Don't you think???

I added a new SSRS project wizard to an existing olap cube solution project file. Using the wizard, I pointed to olap cube database. Design Query was pretty awesome in a sense that I could drag and drop desired measures and dimension hierarchies and it would flatten it out in the result set for me.

Profile Amount column is actually a calculated member based on Reseller Sales Amount and Internet Sales Amount. Once you are done designing query in the query wizard, you could check out the actual query text by toggling the top-right most design button. This way we could get the raw query text.

Next step would be is to layout the report. Top Hierarchy Category would be on the page level, Sub Category on the group level, and rest of the columns are on the detail levels. Don't forget to enable the Include Subtotals and Enable Drilldown check boxes.

I formatted the Reseller Sales Amount cell as follows:

=Format(FormatNumber(Fields!Reseller_Sales_Amount.Value,2),"C")

This was lots of fun and I hope that you start playing around with the SSRS reports driven by olap cubes.

So much for now.

Happy Cubing..

ZULFIQAR SYED

Have you sliced your cube today?

Olap Cubes: Semi Additive Measures

This type of measure could be used to calculated averages. Typically measures are aggregated over period of time but there are cases when we need to calculate the average such as average sales amount and sales amount at the end of the month.

AggregateFunction property called AverageOfChildren would help us calculate average and LastNonEmpty would help us determine measure at the end of a certain period hierarchy such as month, day, hour etc.

Happy cubing,

ZULFIQAR SYED...

Olap Cubes: Grain basics

Dimension grain basics

DimTime table could have a primary key column TimeKey that uniquely identifies a row in terms of calendar day as opposed to hour, minutes etc.

This TimeKey could become the dimension key in the DimTime Dimension. This key could be used to relate to a fact table such as FactResellerSales via OrderDateKey.

In a regular dimension relationship, a dimension table relates to the fact table via a foreign key.

Dimension key defines the grain of relationship when its used to relate the dimension to the measure group.

In the above case, the Dimension key grain would be calendar date.

HTH..

Happy Cubing...

ZULFIQAR SYED

Olap Cubes: Server Time Dimensions

In SQL Server 2005, there is some thing really awesome called Server Time Dimensions. If your OLTP database lacks designated time dimension which is normally the case then you could create this type of dimension using cube wizard.

Following things could be specified in cube wizar for Server Time Dimension :

1. Time Range and Time Periods.
2. Provide additional four calendars in addition to the regular calendar.
3. Hierarchies and Levels to be generated.

I have worked with it and its truly COOL. Please check it out.

Happy cubing,

ZULFIQAR SYED

Olap Cubes: Measure Types

Measures could be of types: Additive, Semi-Additive, and Non-Additive.

Additive: For example sales amount. These measures are aggregated across different dimensions.

Semi-Additive: Aggregated by only certain dimensions and not all the dimensions. For example product inventory measure would be aggregated by product dimension.

Non-Additive: These are not summed at all. For example order percentage.

Happy Cubing,

ZULFIQAR SYED

Olap Cubes: Non-Leaf Caption

I learned this technique from "Applied Microsoft Analysis Services 2005" by Teo Lachev.

In a parent-child hierarchy (such as Employee), the non leaf member being level 0, level 1, level 2 could be annoying.

We could put a caption on those levels by modifying Naming Template of the hierarchy level Employee Dimension attribute as follows: CEO;Vice President;Sales Manager;Sales Representative;

Browse the cube again after reprocessing and reconnecting. We would we get nice named caption for non-leaf level hierarchy.

This is a real cool technique and I really love it.

HTH..

ZULFIQAR SYED
Happy cubing...

OlapCubes: RootMemberIf Property

Some types of tables such as Employee refer to itself through some key to store employee hierarchy. In this type of dimension, RootMemberIf property could have value ParentIsBlankSelfOrMissing.

ParentIsBlankSelfOrMissing combines all three self explanatory combinations: ParentIsBlank, ParentIsSelf, and ParentIsMissing. Any of these combinations are used to indicate if the member is a higher level member.

Happy Cubing,

ZULFIQAR SYED

Olap Cubes: Role playing dimension

Lot of times a fact table would have different kinds of dates such as departure date, arrival date, Order date, Shipped Date etc. These dates would be served by one dimesion namely Date dimension. Hence, Date dimension has to play several roles for dates. This kind of dimension is called Role playing Dimesion.

HTH..

Happy cubing,

ZULFIQAR SYED

Olap Cubes: Attribute Usage

Key: Every dimension has to have a dimension key like customerid in customer dimension.

Parent: Some dimensions such as DimEmployee could have parent child relationship among its members. EmployeeParentID could be a key which would define supervisor for the employee in the dimension.

Regular: An attriubte usage other than Key or Parent is Regular. For example, weight in DimEmployee could be a Regular attribute. It just gives extra information about the employee in the DimEmployee dimension.

HTH..

Happy Cubing,

ZULFIQAR SYED

Olap Cubes: Closing Period etc

Here is an example of closing period expression:

(ClosingPeriod([Due Date].[Calendar Time].[Calendar Month],[Due Date].[Calendar Time].currentmember),[Measures].[Order Quantity])

Expression for Max Value:

max(descendants([Due Date].[Calendar Time].currentmember,[Due Date].[Calendar Time].[Calendar Month]),[Measures].[Order Quantity])

Happy Cubing,

ZULFIQAR SYED