Dynamic Time Groups in an OLAP Cube

One of the challenges presented to me while I was helping out at the DHHS in New Hampshire was to find a way to make user defined year groups in the report. While it’s trivial to create year groupings in a cube, it becomes impossible when you need to account for every possible combination reports require. Sometimes you may need sets of 5 years starting at 1991. Other times the requirement may be every 2 years starting at 1980. Sometimes the users may want to see each year, 1991-1995, 1992-1996, 1993-1997; while other times the overlap is unnecessary, 1991-1995, 1996-2000, 2001-2006. Each measure would have to be aggregated for each grouping, increasing the size of the cube.

At first glance my solution is a bit complex. It uses JavaScript to control the appearance of the prompt and OLAP functions to set up the groups. Finally report expressions are used to control the labels in the chart and crosstab. Since the Cognos PowerCube samples have a limited set of years, I’ve adapted the technique to work on months instead.

In this example, the user is presented with three prompts. The first and second prompts allow the users to select the months they want. The first prompt has no overlap, if the group size selected is 6, it will show Jan-Jun and Jul-Dec. The second prompt will show Jan-Jun, Feb-Jul, Mar-Aug and so on. The third prompt shows the group size, defaulting to 6.

The non-overlapping month prompt takes some work to get working. It is, essentially, filtering the month level where mod(monthNumber,groupSize) is 0. It is a little more complex, as that alone won’t work. First, the mod function isn’t supported by the cube, and will result in local processing. Second, it should be monthNumber – 1:

```(((total(
[One] within set periodsToDate(
[sales_and_marketing].[Time].[Time].[Time]
, currentMember([sales_and_marketing].[Time].[Time])
)
)-1)
/ #prompt('Group Size','integer','6')#)
-
floor((( total(
[One] within set periodsToDate(
[sales_and_marketing].[Time].[Time].[Time]
, currentMember([sales_and_marketing].[Time].[Time])
)
)-1)
/ #prompt('Group Size','integer','6')#)))
* #prompt('Group Size','integer','6')#
```

If you want the group to have a different start month, change the -1.

That will give us a set of every sixth month. 2010/Jan, 2010/Feb, 2011/Jan, 2011/Feb. But now we need to modify the appearance of the prompt values. For that we need JavaScript.

This JS will loop through the prompt, convert the month name into a numeric value, add the selected group size, then convert that number back into a month and concatenate it onto the label again.

```<script>
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

function parseMonthName(name) {
var Months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
for (i=0;i<12;i++){ if(Months[i]==name) {return i+1; break;}}
}

month=month==0?12:month;
return month;
}

function getMonthName(month) {
var Months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
return Months[month-1];
}

function setMonthNames(startPrompt,endPrompt){

var Size
, Start = startPrompt
, End = endPrompt
, Month , newMonth, newYear

// Loop through the groupSize list to set the size variable. Subtract 1 from the value since the value includes the current month.
for(var i =0;i<fW._oLstChoices_groupSize.length;i++){if(fW._oLstChoices_groupSize[i].selected) Size=fW._oLstChoices_groupSize[i].value -1}

for (var i = 0;i<Start.length;i++){
if(!Start[i].getAttribute('Orig')) {Start[i].setAttribute('Orig' , Start[i].getAttribute('dv')); };
Month = Start[i].getAttribute('Orig');

// If the group size is set to 0, use the original label. This is for on the fly changes.
if(Size==0) {
Start[i].setAttribute('dv' , Month);
}
else {
newMonth = parseMonthName(Month.substr(5,3)) ;
Start[i].setAttribute('dv' , Month + '-' +  newYear + '/' + getMonthName(addMonths(newMonth,Size)));
}
Start[i].innerHTML= Start[i].getAttribute('dv') ;
//if(i>=Size-1) {Start[i].display='none';} else {Start[i].display='';} //Really, IE? Really?!
}
}
</script>
```

So far this has only served to make it easier for the end user to understand how he’s filtering his report. The prompts themselves are still passing single member unique names to the query. Another calculated data item will be needed to group the years.

```member(
total(
currentMeasure
within set
lastPeriods(
0-#prompt('Group Size','integer','5')#
,currentMember([sales_and_marketing].[Time].[Time])
)
)
,'Grouped Total'
,'Grouped Total'
,[sales_and_marketing].[Time].[Time])
```

The lastPeriods will return the next Group Size members on the same level. This calculated member can then be used as a tuple on any measure that needs to be grouped. In the attached report I simply added it as the default measure in the charts and crosstabs.

That will create the groupings, but the labels on the chart and crosstab will still only show a single month. To fix that we can use a report expression with the same logic as the JavaScript from the prompt page:

```case
when ParamValue('Group Size') = '1' then [Report].[No Overlap]
else
[Report].[No Overlap]  + '-'
+ number2string(
string2int32(substring([Report].[No Overlap],1,4))
+ case
when mod (
case substring([Report].[No Overlap],6,3)
when 'Jan' then 1
when 'Feb' then 2
when 'Mar' then 3
when 'Apr' then 4
when 'May' then 5
when 'Jun' then 6
when 'Jul' then 7
when 'Aug' then 8
when 'Sep' then 9
when 'Oct' then 10
when 'Nov' then 11
when 'Dec' then 12
end
+ string2int32(ParamValue('Group Size'))-1,12) between 1 and (string2int32(ParamValue('Group Size'))-1)
then 1
else 0
end
)
+'/'+
case mod (
case substring([Report].[No Overlap],6,3)
when 'Jan' then 1
when 'Feb' then 2
when 'Mar' then 3
when 'Apr' then 4
when 'May' then 5
when 'Jun' then 6
when 'Jul' then 7
when 'Aug' then 8
when 'Sep' then 9
when 'Oct' then 10
when 'Nov' then 11
when 'Dec' then 12
end + string2int32(ParamValue('Group Size'))-1,12)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Nov'
when 11 then 'Oct'
when 0 then 'Dec'
end
end```

Simply change the text source on the crosstab or chart node member to Report Expression and paste that in.

Report XML

Secondary Cell Suppression on OLAP

I recently had the great pleasure of working with the Department of Health and Human Services in New Hampshire. They have employed a company called Abacus Service Corporation to develop a wonderful dashboarding system in order to assist them in their goals of greater transparency (which I hope to review on here eventually, it’s a combination of Metric Studio and Workspace that is significantly easier for end users). Their team is extremely skilled, and they only needed a few days of my time to get them past a few difficult problems. They’ve agreed to let me publish the solution two of those problems.

One of their goals is to publish reports, directly to the public, on various health related issues. While transparency is to be commended, smaller data sets present possible privacy law violations. If data was sparse enough, people could theoretically back calculate in order to determine who specific people are from the data. In order to prevent people from being able to back calculate, the need to suppress additional data. They were able to accomplish their goal, but at a cost of a runtime of around 15 minutes. For a simple dataset, and one is supposed to be consumable by end users on the fly, any run time of more than a few seconds is unacceptable.

Let’s reproduce a sample using the Sales and Marketing cube. In this example we’ll say that any value below 20,000 and above 0 is sensitive and must be suppressed.

In that screenshot we can see the quantity sold for each product line for each quarter. Here we can see that not a single value is below 20,000. However, what happens if we slice by a specific country?

Here we see the same data set sliced by Italy. We can instantly see many instances of sensitive data being displayed (remember, anything below 20,000 and above 0 is sensitive). For ease of understanding, I’m going to call rename Quantity as “Check Measure”.
Suppressing those values is a simple matter of:

`if([Check Measure] not between 1 and 20000) then ([Check Measure]) else (null)`

Save that expression in a new data item called Pass 1.

Now we can see sensitive cells are suppressed. Unfortunately it is trivial to recalculate those values where only one cell is suppressed in a column, especially considering that we’re showing a totals row. Each column and row must have either >1 or 0 suppressed cells. To make it even easier to understand, I’ll rename Years to Columns and Product Line to Rows.
First thing to do, create a two new items that finds the next minimum value in the columns. We will want to suppress those values in order to secure the suppressed value. The expression is written to use the memberUniqueName in the rows in order to avoid issues with the next value being a tie.

```Columns Min
#/*
This finds the minimum value of Check Measure after the values have been filtered out.
If there are any values suppressed in the first pass, the second pass will filter by values greater than what this returns, effectively suppressing a minimum of 1 more cell than are suppressed.
roleValue(
'_memberUniqueName'
, item(
bottomCount(
[Columns]
, 2
, [Check Measure]
)
, 1
)
)
```

Next, the second pass checks if the current row has one suppressed cell, and if so, it will suppress the cell where the column matches the result from Columns Min

```#/*
2nd Pass
This will count the number of cells in the columns, and deduct the number of cells remaining. If the value is greater than 0, then a cell is being suppressed - suppress any cells which are equal to the minimum value remaining.
*/#

member(if([Pass 1] is null) then ([null]) else (
if(
total([One] within set [Columns]) - total([Pass 1] within set [Columns])=1)
then (
if(roleValue('_memberUniqueName',[Columns]) =[Columns Min]) then ([null]) else ([One])
)
else ([One])
))
```

Now the same basic idea for rows.

Rows Min:

```#/*
This finds the minimum value of Check Measure after the values have been filtered out.
If there are any values suppressed in the first pass, the third pass will filter by values greater than what this returns, effectively suppressing a minimum of 1 more cell than are suppressed.
*/#

roleValue(
'_memberUniqueName'
, item(
bottomCount(
[Rows]
, 2
, [Check Measure]
)
, 1
)
)```

and Pass 3:

```#/*
3rd Pass
This will count the number of cells in the rows, and deduct the number of cells remaining. If the value is greater than 0, then a cell is being suppressed - suppress any cells which are equal to the minimum value remaining.
*/#

if([Pass 2]  is null) then ([null]) else (
if(
total([One] within set [Rows]) - total([Pass 1] within set [Rows])=1)

then (
if(roleValue('_memberUniqueName',currentMember(hierarchy([Rows]))) =[Rows Min]) then ([null]) else ([One])
)

else ([One]))```

Again, first it finds all columns that have 1 suppressed cell in the rows then it suppressed the next lowest value:

In this case, there are no more instances of unsecured sensitive cells. However, what happens if we had a data set containing only one sensitive cell? It would suppress another cell in the same row and column, but those two cells would now be at risk. If we slice by the United States the third pass returns the following set:

One more pass is needed to hide the remaining intersection:

```if([Pass 3] is null) then (null) else (
if ([cc]  = 1) then(
if(roleValue('_memberUniqueName',currentMember(hierarchy([Rows]))) =
roleValue('_memberUniqueName',
order(filter([Rows], ([cr] >0)and ( [Pass 2]  is not null) )  , [Check Measure] ,basc
),1),0)
) ) then (null) else ( [Final Measure])
)
else  ( [Final Measure])
)```

cc is a very simple: total([One] within set [Rows]) – total([Pass 2] within set [Rows])
with cr being: total([One] within set [Columns]) – total([Pass 3] within set [Columns])

This is similar to pass 2. It will count the number of suppressed cells in the column. The big change is instead of looking for and suppressing the smallest value the column, we are now looking for a row that already has at least one suppressed cell. It will find the matching rows, order them ascending by number of suppressed cells, and take the last one in the list. Instead of returning a 1 or null, this will return the final measure, in this case revenue.

And the final results, for the United States:

The end result will always be a minimum of 2 rows hidden on each row or column, or none at all. This satisfies the demand for data security on a row level without having to run extremely complex calculations for each possible combination of data in the ETL.

At this point the run time is about 2 seconds (on my laptop), still a long time considering the original query, but far more manageable. It should be mentioned that this is useful for smaller data sets, additional columns and rows will increase the run time significantly. Nested nodes will cause additional complexity, and should probably be avoided.

Sample report can be found here.

Suppressing tree prompts

This post uses the Sales and Marketing cube as a source. An example report XML is attached as usual.

A while back I wrote an article about why you should never use filters in OLAP. I mentioned there were only two cases where I permitted developers to use them. This is one of those cases.

When you use a tree prompt, Cognos will send a query to the database consisting of the use item, filters, slicers. Whenever you open a node (such as 2005) it will send another query containing children(2005), filters, and slicers. It will then append the results of that query into the tree prompt.

This behavior makes it difficult to control exactly what is returned. The general method of hiding members is to use the filter function. But since the filter is ignored to create the children function, we have to use something else. Slicers will also not work, as they effect the measures.

Instead, we can use detail filters. Cognos will apply the detail filters to the edge nodes, and since the edge node in a tree prompt consists solely of the hierarchy we want filtered, this will work nicely.

The first thing to remember is that Cognos will readily allow you to mix dimensional and relational functions. This is bad as it will result in local processing. So the filter if(substring(caption(currentMember([sales_and_marketing].[Time].[Time])),6,3)
=’Jan’) then (0) else (1) = 1 will result in:

MDX

```WITH MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact] AS 'NULL', SOLVE_ORDER = 1, MEMBER_CAPTION = '' SELECT HIERARCHIZE(UNION({[Time].[].CURRENTMEMBER}, [Time]..[Year 2].MEMBERS, ALL)) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing] WHERE ([MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact])
```

SQL

```with
Years4_0_oqpTabStream4 as
(select
Years4_0_oqpTabStream."Data Item11/_memberUniqueName"  as  Data_Item11__memberUniqueName,
Years4_0_oqpTabStream."Data Item11"  as  Data_Item11,
Years4_0_oqpTabStream."Year/_memberUniqueName"  as  Year__memberUniqueName,
Years4_0_oqpTabStream."Year"  as  Year4
from
TABLE(_ROWSET("Years4.0_oqpTabStream")) Years4_0_oqpTabStream
)
select distinct
Years4_0_oqpTabStream4.Data_Item11__memberUniqueName  as  Data_Item1__memberUniqueName,
case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end   as  Data_Item1,
Years4_0_oqpTabStream4.Year__memberUniqueName  as  Year__memberUniqueName,
Years4_0_oqpTabStream4.Year4  as  Year4
from
Years4_0_oqpTabStream4
where
(case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end  = 1)
```

It’s dumping the entire hierarchy into memory and processing it in SQL. If the hierarchy is large (I’ve got some clients with hundreds of thousands of members in their hierarchies) this can be problematic.

Instead, we need to use dimensional friendly functions. Let’s start with hiding all members where the Profit Margin is less than 0.405.

For this, it’s a very simple detail filter of [Profit Margin]<0.405. The results will then work on every level:

In the preceding image, March of 2004 is missing from the tree prompt as the Profit Margin for that month was 40.7%. Q1, 2005 is also missing, as the Profit Margin for the entire quarter was 40.7% as well.

Next, we can suppress an entire level. Note that this will not allow you to skip a level; you can’t find the children of a member which isn’t rendered. For this, all you need to do is to filter on the ordinal or levelNumber of the member. roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) < 3

And finally we let’s try suppressing Januaries.
if(roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue(‘_memberCaption’,currentMember([sales_and_marketing].[Time].[Time])) contains ‘Jan’) then (0) else (1) = 1. In this case, it checks that the current level is 3, and that the caption contains ‘Jan’ and returns 0. The MDX is valid:

```WITH SET [COG_OQP_INT_s1] AS 'FILTER([Time]..[Year 2].MEMBERS, IIF(([Time].[].CURRENTMEMBER.PROPERTIES("LEVEL_NUMBER") = 3 AND CONTAINS([Time].[].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), "Jan")), 0, 1) = 1)' SELECT [COG_OQP_INT_s1] DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing]
```

And the result:

In each case, the tree prompt will behave exactly as expected, while suppressing unwanted members.

Report XML:

```<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No suppression</staticValue></dataSource></textItem></contents><style><CSS value="white-space:normal"/></style></tableCell><tableCell><contents><selectWithTree parameter="Parameter1" refQuery="Years"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Only members with Profit Margin &lt; 0.405</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree parameter="Parameter2" refQuery="Years2"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No months</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years3" parameter="Parameter3"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No Januarys</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years4" parameter="Parameter4"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" rowsPerPage="99999" refQuery="Data">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>

<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Profit Margin" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><queries><query name="Data"><source><model/></source><selection><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Quarter]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Month]</expression></dataItem></selection></query><query name="Years"><source><model/></source><selection><dataItem name="Year" aggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection></query><query name="Years2"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Profit Margin]&lt;0.405</filterExpression></detailFilter></detailFilters></query><query name="Years3"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Level Number"><expression>roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time]))</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression> [Level Number] &lt; 3</filterExpression></detailFilter></detailFilters></query><query name="Years4"><source><model/></source><selection><dataItem name="Data Item1"><expression>if(roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue('_memberCaption',currentMember([sales_and_marketing].[Time].[Time])) contains 'Jan') then (0) else (1)</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Data Item1] = 1</filterExpression></detailFilter></detailFilters></query></queries><reportName>suppressing tree prompts</reportName></report>
```