Zulfiqar's web

mdx query: Head

I didn't plan to address Head function today specially after listing Tail function couple of days ago. Well, if you want to return the very first, certain number of tuples in a set, Head would be very useful. Here is a sample script against ss2k5 AS sample Adventure Works olap database.

select
--HEAD( «Set»[, «Numeric Expression»] )

head(
    [Product].[Product Categories].[Category]
    ,
    2
    )

on 0
from

[Adventure Works]

Happy Cubing,

ZULFIQAR SYED

Seven Habits

                                                                  

Excellent article.. Enjoy !!!!

Seven Habits of Highly Effective DBAs

ZULFIQAR SYED

mdx query: Tail

Lets say that you have a set of tuples and you want to get the very last tuple or some number of last tuples. What would you do? There was a similar question in the news group and some one suggested using Tail function. Its pretty neat and you could use it as follows:

select
    tail(
    [Product].[Product Categories].[Category].members

    ,2
    ) on 0

from
[Adventure Works]

Here, Tail function returns last 2 tuples of category members set.

Happy Cubing,

ZULFIQAR SYED

Olap Cube: BI Certs

Microsoft BI Certifications Line up.

Check it out.

ZULFIQAR SYED

Olap Cubes: Reports in Browers

Some time back in olap news group it was mentioned that Report Portal could be used to render olap reports in the browser.

Check it out, it looks great.

Happy Cubing,

ZULFIQAR SYED

Olap Cube: Pentaho

Chris webb posted an excellent review on Pentaho. Its regarding an open source olap server. Check it out.

Happy Cubing,

ZULFIQAR SYED

mdx query: Grand total

Excellent post in the news group addressing grand total at the end of rows just like in BIDS.

Check it out.

ZULFIQAR SYED

mdx query: avg function

The following query illustrates a simple example on how to use avg function in mdx.

WITH MEMBER Measures.x as
avg(
    descendants
    (
        [Date].[Calendar].currentmember
        ,
        [Date].[Calendar].[Calendar Quarter]
    )
    ,
    [Measures].[Reseller Sales Amount]
)
select
[Date].[Calendar].[Calendar Semester].&[2002]&[1]
.children
on 1 ,
measures.x on 0
from
[Adventure Works]

HTH..

ZULFIQAR SYED

mdx query: Tuple and Sets

Tuple and Sets

Executing the query ...
The 'Geography' hierarchy appears more than once in the tuple.
Execution complete

Have you gotten this kind of error before. You must have if you do lot of mdx programming. Unless, we take our time to REALLY understand tuple and sets, we will continue to run into this error. Tuple is a cell or section of a cube. Set is a collection of tuples. Its that simple. When you try to access a cell in a cube, you have to keep mentioning each dimension until you narrow down the cell or section of the cube.

I am running these queries against cube sample that comes with AS2005

Following query would return aggregates from each geography section of the cube.

SELECT
-- set of 2 tuples
{
[Geography].[Geography].[Country].&[United States]
,
[Geography].[Geography].[Country].&[Canada]
}
ON 0
FROM
[Adventure Works]
GO

This query would return total aggregates of each tuple sliced by 2 individual tuple slicer.

SELECT

FROM
[Adventure Works]
WHERE
{
[Geography].[Geography].[Country].&[United States]
,
[Geography].[Geography].[Country].&[Canada]
}
GO

HTH..

Happy Cubing,

ZULFIQAR SYED

mdx query: From

From clause is used to indicate either the cube itself or sub section of the cuber. These 2 queries show the use of from clause respectively.

select
from
[Adventure Works]
go
select
from
(
select
[Date].[Calendar].[Calendar Year]
on 0,
[Geography].[Geography].[Country]
on 1

from
[Adventure Works]
)
where [Date].[Calendar].[Calendar Year].&[2002]

HTH..

Thanks

Happy Cubing,

ZULFIQAR SYED