You are currently browsing the category archive for the ‘analysis services’ category.

I had a requirement recently where the customer wanted a measure to be formatted differently, based on its value.

In this case, it was two numeric measures with 2 decimal point precision, and for cases where there were no decimal digits, it was supposed to look like an integer.

I looked for a MOD function in VBA or SSAS that can be used here, but came up empty 😦

I used the SCOPE statement to override the default formatting

SCOPE({[Measures].[Sales], [Measures].[Cost]});
FORMAT_STRING(This)= IIF(Round([Measures].CurrentMember.VALUE, 2)=[Measures].CurrentMember.VALUE, “#,###;;0;”, “#,###.00;;0;”);
END SCOPE;

For the measures in question, the SCOPE statement compares its ROUNDed value to its actual value. If these are equal, that means that the number is a whole number, and has to be formatted without decimal places shown. Hence the FORMAT_STRING expression.

Many a times, people ask the question on how to disable or hide the Total and Grand Total rows in the result set when using the Cube Browser in SSMS (SQL Server Management Studio).

Here is a quick way to do that

When you browse a cube, you see Totals as below

Click on the dimension header in the Row Fields area

Right-click the highlighted region to bring up the menu as

Uncheck the Subtotal, and voila – it disappears
 

For more, just repeat the above steps

 

Created a report in SSRS (SQL Server Reporting Services) with the SSAS (SQL Server Avalysis Services) cube as the data source. In order to make the report dynamic, since the date hierarchy (defined as day, month, quarter) and the week hierarchy (defined as day, week) don’t align (weeks can span months, quarters, etc), I defined a parameter in the report, type text (SSAS can only pass string data to the reports as parameter values), named @ParameterValue.

The behavior is dynamic as based on the passed parameter, the underlying query changes the hierarchy

This report was then linked to the cells in the cube with the Reporting Action defined in the Actions Form Editor in the cube definition in SSAS, as

Target Type: Cells
Target Object: All Cells
Condition: [Ticket Report Date].[Date Hierarchy].CURRENTMEMBER.Level.ORDINAL > 1
Report Server Name:
<ReportServer URL>
Report Path: ReportServer?/CubeReports/TicketsOpenedDetails
Parameters: UrlEscapeFragment(MEMBERTOSTR( [Ticket Report Date].[Date Hierarchy].CURRENTMEMBER ))

For week report data, the following were changed:

Condition: [Ticket Report Date].[Week Hierarchy].CURRENTMEMBER.Level.ORDINAL = 2 
Parameters: UrlEscapeFragment(MEMBERTOSTR( [Ticket Report Date].[Week Hierarchy].CURRENTMEMBER ))

As one of the tasks on hand, I had to figure out the backlog which by definition is a running total of all ‘open’ incidents at the end of a day. The dimension table being used has all the incident details and related attributes such as status, priority, category, etc 

So, I created a caculated measure, named [Incident Backlog Count], based on the [Incident Count] measure in the measure group, as: 

IIF ([date hierarchy].CurrentMember IS NULL,
 NULL,
 IIF(IsEmpty(Measures.[Incident Count]),
  IIF([date hierarchy].Parent.FirstChild IS [date hierarchy].CurrentMember,
   (Measures.[Incident Backlog Count], [date hierarchy].Parent.PrevMember),
   (Measures.[Incident Backlog Count], [date hierarchy].PrevMember)),
  IIF([date hierarchy].LEVEL IS [date hierarchy].[Date],
   (Measures.[Incident Count]),  IIF(IsEmpty((Measures.[Incident Backlog Count],[Date Hierarchy].LastChild)),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild.PrevMember),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild))
  )
 )
)
  

The recursion worked well and the MDX seemed to simulate the LastNonEmptyChild function, except that at the leaf level, all the values were getting populated – even for the empty members !   

For the life of me, I could not figure out the reason why this was happening, until I went in the Calculated Member Form Editor for the calculated measure and set the Additional Property Non Empty Behavior to the default measure [Incident Count] 

Here’s what Microsoft has to say about the Calculated Member Form Editor 

If the Non Empty Behavior property is blank, the calculated member must be evaluated repeatedly to determine if a member is empty. If the Non Empty Behavior property contains the name of a measure, the calculated member is treated as empty if the specified measure is empty. 

  

  

There are 2 columns in my fact table, order_date and ship_date. Each row has an order_date, but ship_date is NULL, possibly for orders that are yet to be shipped, or were cancelled and never shipped. Pulling it in a cube kept generating the error message
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘table_name’, Column: ‘Column_name’, Value: ‘0’. The attribute is ‘attribute_date’.

This was happening when trying to lookup the key values for NULL/missing ship_date against time dimension

To resolve this, we need to let the Fact table ignore NULLs or zero’s in the data

In BIDS, open the Cube Structure Tab
In the Measurements pane, select the Fact table the error is related to
Change ErrorConfiguration property to (custom)

Expand the ErrorConfiguration property,
 set KetNotFound property to IgnoreError
 set NullKeyNotAllowed to IgnoreError

This will pass-through all NULL or 0 key values to the dimension as “Unknown Member”, leaving all error handling for referential integrity issues for the Dimension to handle.

Open the related Dimension
In the Dimension Structure pane, select the Dimension name (at root level)
Change UnknownMember to Hidden

Process the Dimension, and then process the Cube

I’ve been working with the other components of BI (SSIS, SSAS, SSRS, PPS) for the past 6 weeks and haven’t had much chance to work on the MOSS component.

Here is something I had to struggle with and hope you guys find it useful:
In the cube, one of the most common tasks is to do data analysis/comparison for different time periods and almost all the time, the reference point is NOW.
How did our numbers compare to a period in the past w.r.t. today/this month/this quarter, etc

Here are some MDX examples to solve 90% of those worries. Might come in handy if you want to create a named set based on such:

1. Current Month MDX
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”))

2. Current Quarter MDX
The VBA function format() does not have a provision for returning the current quarter, so here is what you do – as above, you get to the current month and lookup its parent to get to the quarter. Now, the caveat is that your date hierarchy should be defined as such (Month to be a child of Quarter). In my case, it is Year -> Quarter -> Month -> Date
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”).Parent)

It saved my day and hope it helps you.
In case you know of another (better or worse) way, let me know.

Timeline

June 2017
M T W T F S S
« Mar    
 1234
567891011
12131415161718
19202122232425
2627282930  

Blog Stats

  • 42,228 hits