Slicers from same Dimension

[Steve]

So I have a cube that has a product Dimension which has 3 levels.

Level 1 (Highest level) - Product Type
Level 2                         - Product Family
Level 3                         - Product

The report has Value Prompts for all 3 levels and they are cascading prompts, so the 2nd and 3rd prompts are cascading on the 1st and 2nd level prompts respectively.

I can create a slicer for the product Type prompt as:

#prompt('ProductType','mun','members([Product Cube].[All Products].[All Products].[Product Type])')#

When I try to create the slicer for Product Family, it tells me that slicers have to come from different dimensions !!

So how do I filter the query for the other 2 prompts ?

[CognosPaul]

Each slicer needs to be a member or a set of members from a single dimension. In a standard relational model you'd be able to do something like [Product Type] = 'Travel Machines' and [Product Family] = 'Time and Space' and [Product] = 'Tardis'. At the end of the day, you don't really need the Type and Family filters, as the Product is the most atomic level.

An OLAP query must have it's slicer as a set of members from a single hierarchy (they can be from the same dimension, that gets a bit confused in PowerCubes). Consider the following, if you tried the filter [Product]='Delorean' and [Product]='Tardis' you wouldn't expect to get any results. The two filters would cancel each other out. A cube works basically the same way, but there's a little more error checking involved.

So the problem then becomes, how can we slice by Product, but if no values are selected slice by Product Family, but if no values are selected slice by Product Type?

The default parameter of a prompt macro doesn't need to be a static value. It could be, for instance, another macro function. Like the prompt macro function.

Try the following:

Code Select Expand

#
prompt
(
     'Product'
   , 'mun'
   , prompt
   (
        'ProductFamily'
      , 'mun'
      , prompt
      (
           'ProductType'
         , 'mun'
         , '[Product Cube].[All Products].[All Products].[Product Type]'
      )
   )
)
#

The nested prompts should return the expected results.

[Steve]

Fantastic !! Thanks a lot !

One more thing -

I also have a list report in which I have the column 'Product Type' and I also have that in the prompt. So the slicer slices the data (measures in this case) but the List still keeps all other values from the Product Type level even if I select one particular Product Type. I mean when I select a Product Type, it shows blanks in the measure for all other Product Types but still shows those product Types in the List. So I guess it does not slice the data container even if it slices the query. So I removed that column for Product Type and created a calculated column with this -

filter ([Product Cube].[All Products].[All Products].[Product Type], caption ( [Product Cube].[All Products].[All Products].[Product Type]) = ?ProductType?   )

It validates but throws an error when I run the report that an unequal no. of operands have been passed to that data item. Any clue ?

[CognosPaul]

You're still thinking in terms of a relational query. Slicers effect only the measure, and can even be overridden if necessary. A much simpler way would be to directly set the members being displayed. You wouldn't even need to use the slicer (unless you have an edge that isn't effected by that node, but since you're using a list that's not the case).

Code Select Expand

#
promptmany
(
     'ProductType'
   , 'mun'
   , '[Product Cube].[All Products].[All Products].[Product Type]'
   , 'set('
   , ''
   , ')'
)
#

If the user doesn't select any product types, this returns the entire level. If the user does select one or more, it'll put the set( before and ) after making it a valid set expression.

[Steve]

That worked ! Thanks again !

So, what you mean is that we should first build a set of members (so that it reads only those members from the cube) and then slice (to slice the measures) ? Is it because the way data is structured in the cube vs a relational database ? I think the powercube is an ascii file so that means the same data gets repeated in the file if it belongs to the a different member at the higher level too ? 

[CognosPaul]

Ignore the way the data is stored on the file system. That's irrelevant to how Cognos deals with OLAP data.

It's well past my bedtime, so the following probably seems fairly random.

When I teach OLAP reporting theory, there are a number of analogies I like to present. I include props, like a rubix cube, and draw diagrams of what look like pie charts.

The important thing to remember in OLAP is that the data is already aggregated (not entirely true, but true enough for the purposes of this post). While in a relational database the SQL engine needs to retrieve each row and sum the data from those rows, in a cube it's already calculated. This is one of the things that increases the speed of the cube.

Let's say that you want to see the total number of sales for a specific year. In SQL it would be "select sum(sales) salesTotal from fact f where f.year = 2011" The SQL would be run against the index and return the result fairly quickly. But remember, it's actually putting in a fair amount of work into that simple query. It's finding every row, then summing it.

Against an OLAP database the MDX would look something like select ([measure],[2011]) on 0 from cube. The results would be returned instantly as that aggregation already exists.  The parens define a tuple of Measure and 2011.

Let's take it another step. A cube is naturally a crosstab. I generally frown on using lists against an OLAP source. Consider the following crosstab. rows: products. columns: 2010, 2011, growth%. measure: sales.

MDX: select {[2010],[2011],(([2011]/[2010])-1)} on 0, {[Product 1],[Product 2],[Product 3]} on 1 from cube where [Sales]

The mdx is explicitly creating a set of years and a calculated member of growth. (Note, the MDX is slightly simplified, the growth calculation should be defined earlier.) That set is put onto the columns. There is an explicit set of products defined on the rows, and the default measure of the crosstab is Sales.

At this point we have a nice two dimensional crosstab. But try to imagine it with a third dimension, lets say order method. Phone, Email, Fax. The easiest way would be to think of a rubix cube. 3 rows, 3 columns, and 3 deep. By putting where [Phone] we're effecting only that third dimension. It doesn't effect the edges, only the measures themselves.

So by explicitly creating a set on one of the edge, you're naturally limiting what the crosstab returns. You can reference another hierarchy in the slicer to slice on that third dimension.

[Steve]

Got it ! Thanks for explaining it !