Filtering rows for dimensional data

[bdbits]

I created some reports on a cube and ran into performance problems. After reading a lot - especially PaulM's admonitions and links here, thank you! - I thought I learned a lot about RS and OLAP data sources. My performance problems are likely due to the tens of thousands of rows from a cross-join in an intermediate result set, as I was using detail filters. Ok, no problem, I think I need to filter the rows, but for the life of me I cannot get a working filter() expression. I picked one of my simple crosstab reports, with some prompts. My row expression is

    [PR_Cube].[All Expense Accounts].[All Expense Accounts].[Level 3]

and the existing and working detail filter is

    [PR_Cube].[All Business Units].[All Business Units].[PR Business Unit] in ?pBusinessUnit?

My thought was I should be able to combine these:

filter([PR_Cube].[All Expense Accounts].[All Expense Accounts].[Level 3],[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit] in ?pBusinessUnit?).

The report prompts pop, but when it tries to actually build the report it returns an error: "Unable to evaluate a value expression on a set with more than one member". I assume the "value expression" is the condition in the filter(). All I am trying to say is "give me all of the rows where the business unit is the one the user selected". On a whim I did try replacing "in" with "=" but I get the same result, and tried a few other expressions with assorted errors, but I don't think they were valid expressions to start with. So...

I know this is going to be something simple, but my head is starting to hurt from banging it against my desk. :-P

Any help or pointers would be tremendously appreciated.

[CognosPaul]

Dimensional querying has a fairly vertical learning curve. Once you pass the hurdle, however, you'll realize how easy it is compared to Relational.


It sounds like you're trying to do two different things here. Please correct me if the following is incorrect:

• You want to filter the query by multiple Business Units which the user can select.
• Only Level 3 Expense Accounts that meet a certain criteria should be shown.

Neither of these demands are difficult.

For the first request, instead of using a detail filter, you should use a slicer. Try placing the following prompt macro into the slicer:
#promptmany('pBusinessUnits','memberuniquename','[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit]->[All]','set(','[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit]',')')#

Before I go any further I'll explain that macro.
#promptmany( <-- Tells the macro engine to expect multiple parameter values.
'pBusinessUnits' <-- Name of the parameter.
,'memberuniquename' <-- Data type. I'm assuming that the prompt you've created is populated by a level.
,'[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit]->[All]' <-- This is the default. If the user doesn't select anything it will return the All member, effectively leaving the query unsliced.
,'set(' <-- If the user does select something it will place this before the members
,'[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit]' <-- If you didn't create a prompt for the users Cognos will generate a value prompt with this level as the source. It also makes things easier for you if you want to test an individual page instead of running the entire report just to see the prompt page.
,')<-- If the user does select something it will place this after the members. So you'll have set(member1,member2,member3)
')#<-- Closing the macro.

My suggestion is to create a very simple crosstab: All Expense Accounts.Level 3 in the rows, and Measure in the columns. Take a look at the generated MDX with that macro in the slicer and again with the detail filter. In my experience the detail filters make the MDX ugly and bloated.

The thing to remember about slicers is that it only effects the measures, it doesn't touch the levels.

Now you'll need to filter Level 3.

Use the filter function:
filter([PR_Cube].[All Expense Accounts].[All Expense Accounts].[Level 3], [Measure] is not null)

Now it looks like there may be a direct connection between Business Units and All Expense Accounts. You could try using the generate function, but I haven't had much luck with it.

[bdbits]

Thanks much for your reply. I am continuing to explore but it is working. I'm still uncertain about performance when I get to more detailed crosstabs with many, many more rows to be filtered, more on that below.

    * You want to filter the query by multiple Business Units which the user can select.

Actually, I wanted to filter down to a single Business Unit with no default. I managed to convert the #promptmany to a #prompt and made it a slicer. I love prompt macros anyway and it seems to work. :)

#prompt('pBusinessUnit','memberuniquename','','set(','[PR_Cube].[All Business Units].[All Business Units].[PR Business Unit]',')')#

"The thing to remember about slicers is that it only effects the measures, it doesn't touch the levels."

So the thing is, it will still retrieve all the Level 3 accounts for the intermediate result set and then apply the filter(), correct? This is the Big Thing(tm) I want to avoid, and for the 'lower level' crosstabs using much wider dimensions this could be a significant issue. This is why I thought I should have a filter on the row set. As I think about this, it seems that without a direct relationship between the dims in the models, using a slicer is probably the only way to do this, really, unless there is more magic to cube processing than I am aware of. The only relationship between the two dims is through the facts. Although, I am not familiar with the generate() function you mention and will have a look at it.

Just FYI and kind of after-the-fact, the Transformer hierarchies look like this, and the fact table query has surrogate keys that link it to each of the dim queries (included in both the FM source model and the Transformer model and queries). I am basically trying to get the facts (expenses) for a user-selected business unit aggregated by Account Level 3.

Expense            Business
Accounts          Units
--------           --------------
Account SetID   PR BU Set ID
Level 1             PR Business Unit
Level 2
Level 3
Level 4
Level 5
Account

[CognosPaul]

If you only need a single Business Unit then you can get rid of the 'set(' and ')' from the prompt macro. One less operation for the cube to deal with.

In other cube technologies, such as Analysis Cubes, you can create a relationship between different hierarchies. Instead of displaying a cross-join, nesting products on countries would only show the products that have data for that specific country. In this case the generate function would work very well for you. I do not believe that Transformer is capable of making that type of relationship though.

Unless you have millions of members in Level 3, the performance shouldn't be too bad. The filter function should work fairly well.

Consider the following report that I just made:


Log ID     Factory   Measure
------    --------   --------
All       All        RunTime
Log ID    Factory    Stoppages
          Dept       Packs
          Machine


There are roughly 100,000 log ids in this cube, 380 machines in 7 or so departments in 4 factories. I could make the following crosstab:


Corner | Stoppages
-------+---------
Log ID | 1234
Sliced by Factory 1
Log ID is: filter([Cube].[Dim].[Hier].[LogID],[Stoppages]>0)

It took about 12 seconds to generate an excel sheet with 20,000 rows, 2 columns.

[dana180]

thanks Paul for your detailed answer but  i need this
macro on the field who serve as the use value in the value
prompt.
when i used your macro in the slicer nothing happen.
any help will be very appreciated.
ayelet

[CognosPaul]

Hi Ayelet

I'll need a bit more information before I can help you. Are you trying to retrieve a single or multiple members? Is the prompt based on a relational or dimensional or static values? Is the use value related in any way to any of the attributes of the level or a measure?

זה נראה לי שאנגלית היא לא השפת האם שלך. תשאלי בעברית ואני אנסה לתרגם עבור כולם, כאשר אני עונה.
(That's assuming you understand what I just wrote there)

[dana180]

הי פול המון תודה על התשובה המהירה.
אני עובדת באגד בגרסא 8.4 .כל הדוחות מבוססי קוביה.מעבירה מדוח
קודם פרמטר של דגם אוטובוס .תמיד יעבור רק ערך אחד של דגם אוטובוס ובדוח הנוכחי
צריכה להציג את כל האוטובוסים באגד מדגם זה.כמובן לסניף מסויים ורק כאלה שצרכו כמות סולר גדולה מאפס אבל זה לא רלוונטי לצורך הפילטור כרגע.הבעיה שכאשר בדוח הנוכחי המשתמש רוצה לבחור לדוגמא אוטובוס אחד או יותר לשליפה נוספת יש לו בעצם את כל האוטובוסים בסניף ולא רק לדגם שעבר כפרמטר מהדוח הקודם ואני מקבלת את אותה הודעת השגיאה שדווחה במעלה העמוד.כשמנסה לפלטר את הדגם ביחד עם האוטובוסים.הדגם והאוטובוסים נמצאים במימדים שונים.

[CognosPaul]

For everyone else:

Ayelet is working for a public transportation company and is working with an OLAP cube. PowerCubes?

From what I'm gathering her cube is set up as follows:

DIM     |    DIM   |   DIM      |   Fact
--------+----------+---------------------------
Branch  |  Bus     | Bus Model  | Diesel Fuel Usage

(Is this correct, Ayelet?)

She passes the model of the bus from another report to here. In this report she needs to display all of the buses for this model. In addition to the buses displayed the user will then want  to filter on specific buses.

I may have missed some things because my Hebrew isn't so great.

Assuming this is correct:
Do you have the ability to modify the cube, or have someone modify it per your specs? It seems that the bus dimension should be merged into the bus model dimension.
If that isn't possible maybe you could add the bus model to one of the bus attribute fields.

Without knowing more I'm going to throw out a suggestion:
Add a slicer for Bus Model:
#prompt('Model','mun','[Cube].[Dim].[Hier].[AllLevel]->[PC].[AllMember]')#
and
#promptmany('Bus','mun','filter([Cube].[Dim].[Hier].[Buses],Measure is not null)','set(','',')')#

[dana180]

good morning to Paul and Jerusalem
the slicer is not working .the 2 dimensions must be combined together as in version 8.2.
this is what written in the data item (use value property of value prompt) in version 8.2:
FILTER(
[Timchur Meshek Cube].[סניפי שייכות].[סניפי שייכות].[מספר רישוי+ פנימי]
,

[Timchur Meshek Cube].[Measures].[כמות סולר]
<> 0
AND

[Timchur Meshek Cube].[דגם].[דגם].[דגם1]
=

?Pdegem?)

-----------------
-----------------
[סניפי שייכות].[סניפי שייכות].[מספר רישוי+ פנימי]  is the bus number
and the report in version 8.4 returns an error: "Unable to evaluate a value expression on a set with more than one member"  even when there is only one value of Pdegem.

[CognosPaul]

איך ידעת שאני בירושלים? את עובדת במשרדים כאן או בתל אביב?

There's no connection between [Timchur Meshek Cube].[סניפי שייכות].[סניפי שייכות] and [Timchur Meshek Cube].[דגם].[דגם].

If ?Pdegem? is returning a single member try changing your function to:

FILTER( [Timchur Meshek Cube].[סניפי שייכות].[סניפי שייכות].[מספר רישוי+ פנימי]
, tuple([Timchur Meshek Cube].[Measures].[כמות סולר],#prompt('Pdegem','mun')#)<> 0)

[dana180]

הי ערב טוב.אני יודעת שאתה בירושלים כי אוהד מליבי בא לעזור לי עם דו"ח והתקשרנו אליך לקבל עזרה.
כשאני מנסה את הפתרון שלך שנראה גאוני אני מקבלת את הודעת השגיאה
"Cannot parse an empty expression"
וזה אומר שאין ערך בפרמטר Pdegem.
האם עלי להגדיר את #prompt במקום נוסף ?
המון תודה איילת

[CognosPaul]

Loose translation:
She's trying my suggestion but getting the error "Cannot parse an empty expression", which means Pdegem is empty. Should she create the #prompt in another place?

My answer:
Generally you get that error when you have an empty data item somewhere in the query. Doublecheck all of the data items, filters and slicers to ensure they all have something in them.

If the Pdegem is ever empty, for example you can expect to open the report without passing the model member to it, then you should modify the filter function to:

Code Select Expand

FILTER( [Timchur Meshek Cube].[סניפי שייכות].[סניפי שייכות].[מספר רישוי+ פנימי]
, tuple([Timchur Meshek Cube].[Measures].[כמות סולר],#prompt('Pdegem','mun',' defaultMember ([Timchur Meshek Cube].[דגם].[דגם])')#)<> 0)

The third parameter of the macro prompt is the default value. If nothing is passed to the macro it returns whatever is in it, in this case it will find the default member of your hierarchy and use that in the tuple.

[dana180]

just wanted to say thanks. this solution solved the problem:

tuple   (   ( [Cube_Timchur_Meshek].[דגם].[דגם].[דגם1]   ->  ?Pdegem? )  ,
[Cube_Timchur_Meshek].[Measures].[עלות סולר]  )
IS NOT NULL.
עכשיו אציג בעיה חדשה בדף חדש מסובכת יותר

[MFGF]

Thanks.  And thanks to Paul too, both for his fiendish deductive and dimensional reporting skills, and also for being our translator du jour!

MF.