Zulfiqar's web

Olap cubes: Filter Function, WITH statement etc.

Example code for filter function in mdx query for sql server 2005

select
{
filter
(
    [Gender].[Gender].[Gender],
    ([TIME].[Year].&[2001],[Measures].[Registration]) <      ([TIME].[Year].&[2002],[Measures].[Registration])
)

} on columns,
{
[TIME].[Calendar Time].[Year]
}
on rows
from
    my_cube
where (
[Measures].[Registration],
[Ethnicity].[Ethnicity].&[Subcontinent Indian/Pak/Bengladesh],
[Income].[Income].&[$50,000 to $74,999]
)

Here is an example on how to write a WITH statement in mdx query in sql server 2005.

WITH SET [PERSON INCOME] AS
'{ [Income].[Income].&[$25,000 to $34,999]  ,
[Income].[Income].&[$35,000 to $49,999]    ,   
   [Income].[Income].&[$50,000 to $74,999]
}'

SELECT
{
[PERSON INCOME]
} ON COLUMNS,
[TIME].[Calendar Time].[Year] ON ROWS
FROM my_cube
WHERE
    [Measures].[Registration]

Here is an example on cross join queries with non empty keyword to get rid of nulls.
SELECT CROSSJOIN (

{ [Gender].[Gender].[Gender] }, [Ethnicity].[Ethnicity].[Ethnicity]
)
    ON COLUMNS,
{   
    [TIME].[Year].[Year]
}   
     ON ROWS
FROM
    my_cube

SELECT NON EMPTY CROSSJOIN (
{ [Gender].[Gender].[Gender] }, [Ethnicity].[Ethnicity].[Ethnicity]
)   ON COLUMNS,
{    [TIME].[Year].[Year] }   
     ON ROWS
FROM
    my_cube

Happy cubing,

ZULFIQAR SYED

Olap cubes: Sets and Tuples

Tuple: This is a single cell location based on intersection of each dimension in the cube.
Sets: This is a set or collection of each cell. Here tuples are collected to come up with a set.

Happy cubing,

ZULFIQAR SYED

Olap cubes: sample mdx queries

Here are some mdx queries. More details on them later..

select from my_cube

select { [Gender].[Gender].&[Female], [Gender].[Gender].&[Male]} on columns from my_cube

select {
    [TIME].[Year].[Year]
    } on columns,
    {
    [Ethnicity].[Ethnicity].[Ethnicity]
    } on rows
from my_cube

select crossjoin( 
    {[Gender].[Gender].[Gender] },
    { [Ethnicity].[Ethnicity].[Ethnicity] }
    )
      on columns,

{ [TIME].[Calendar Time].[Year]}
on rows

from
    my_cube

Happy cubing..

ZULFIQAR SYED

San diego Code camp: Data mining and olap sessions

San Diego code camp is scheduled for Saturday 6/24/06 and Sunday 6/25/06.

My talks on data mining and olap cube got approved today. Here are the sessions that I will be presenting:

1. Data mining using SQL SERVER 2005
2. Relating SQL SERVER 2005 DATA MINING to Business Issues
3. OLAP cubes using SQL SERVER 2005

Here are the downloads for these sessions.

Download INTRO_DATA_MINING.ppt
Download intro_data_mining_script.dmx

Download Intermediate_DATA_MINING.ppt
Download market_basket_script.dmx
Download market_basket_script1.dmx

Download introduction_to_olap.ppt

In the last code camp session, participants gave me pretty good feed back in person. The demos worked very well and I felt really good about the whole presentation. Just like last time, I plan to use very few slides, may be 6 or 7, and code live the rest of the time. Hopefully more people will get excited about data mining, olap cubes, and analysis services in general.

I hope you can make it to the code camp and attend one or all of my sessions.

See you there...

Happy data mining, cubing, sqling, and all that you do...

ZULFIQAR SYED

Have you sliced or mined your data today?

Olap cubes: Hierarchy and Schema

Star Schema: Each dimension table contains all the hierarch information.

Snow Flake Schema: Each level of hierarchy is stored in its own dimension table.

Happy Cubing...
ZULFIQAR SYED

Olap cubes: cube or database dimensions

Database dimensions: For a complicated design, dimensions are created independently at the database level. These could be shared across databases. These dimensions are listed under solution explorer within database.

Cube dimensions: These dimensions are used in the context of a single cube. A database dimension used in a specific context of a cube is called a cube dimension. These are listed under Dimesions section within cube structure tab.

 

Happy Cubing..

ZULFIQAR SYED

Olap cubes: Hierarchy

A note for all of us...

A state has many cities. So this is a hierarchy in terms of olap cubes. This understanding is very much needed when creating dimensions even using the wizards.

Happy cubing..

ZULFIQAR SYED

olap cubes: registrations report by attributes

Today, I worked on the following assignment from the issues list.

Create a report on totals of subscriptions by different attributes such as gender, education, income etc. (OLAP cubes)

First I imported the necessary fact table and dimension tables into the datasource view based on the proper data source connection. Using wizards to create dimensions and measures was lots of fun. Then I would browse the cube in the browse menu after deploying the cube. Dragging and dropping measures/dimensions in the appropriate column/row section and watching the numbers come alive is awesome. Then I added some more dimensions and used the dimension usage screen to make sure that dimensions were tied to the correct measure group with the right foreign keys.

So much for now...

Happy Cubing..

ZULFIQAR SYED

Have you sliced your data today?

Olap cubes: Deploy and Process

Notes:

• Deploying the project: Creates objects in the specified analysis services database.
• Processing the project: Data gets copied from the underlying data source to the cubes objects.

If you can not browse the cube, it could be that its not deployed to an analysis services instance yet. When the cube is deployed, the underline objects are created on an instance of the analysis services database. Upon processing, the cube objects are populated from the data sources in that instance of the analysis services database.

For more detailed information, please refer to the BOL

Happy data slicing,
ZULFIQAR SYED
Have you sliced your data today?

olap cubes: excel reporting

Have you looked into olap excel reporting based on analysis services 2005? If not, you don't know what you are missing. Go ahead and open the Adventure Works DW Standard Edition project file from default installation folder for the samples. If you have not installed it at the default location then open it from your installed location. Right click on project name in the solution explorer and build the project. It could take about couple of minutes if you are deploying it for the first time depending upon your hardware setup.

Start up a new pivot table report in Excel 2003. Point to the above mentioned project cube using external data source connection wizard. Pivot table Field list comes up when you are all done. Simply drag and drop measures and dimension in the row column area and watch the numbers dance! Double click on some of the measures and see them drill down in awe!!! It’s all very awesome stuff.

Next time you see your report writers/query designers complaining about a very complicated report in OLTP, simply show them excel reporting capabilities as a short term solution and they will be ecstatic. Reporting Services would be the next step up obviously.

Happy pivoting!!

ZULFIQAR SYED

Have you pivoted your report today?