This week I am switching gears to talk about a cool design technique for the Reporting Services matrix.

At least it is cool to me J and hopefully it will be helpful to you.

In some design scenarios, you may require two “subtotals” for the same group of data that point to different database fields. This is a little tricky with the matrix given that there is one subtotal per matrix group. You can, of course, use the InScope function to apply different subtotal expressions to various groups, but in this scenario, InScope is not immediately helpful because different subtotals are needed for the same group.

I have mocked up a very simplified version of a business scenario where this can occur:

The business requirements behind the table are as follows:

·         The Count of Completed and Planned Events is a database field containing the total count of the completed and planned events by month. From a business perspective, they are combined together to calculate the overall event workload.

·         The Quarter Totals and Grand Total are normal subtotals of the database field by quarter and year.

·         Now the interesting requirement. The YTD Total should only display the total for the Year-To-Date completed events. The completed events measure is actually a different database field that stores completed events by month.

So put simply I need a Grand Total that sums one data field and I need a YTD total that sums a different field.

·         To interpret the example data in the table, assume that Today is February 15^th. In this scenario, the YTD Total includes the January events (10) and the competed February events as of February 14^th (2). The YTD total is 12. There are 3 other remaining events in February.

To solve this problem, I thought of a few different ways to do this on the database side and even considered reverting to a table, but the most appealing to me was to create a dummy group so that I could use InScope J

So the requirement above ended up can be implemented with the design as below.

1. Create Matrix Column Group 1 On Months
2. Create Matrix Column Group 2 On Quarters
3. Add in normal subtotals for Month and Quarter to get the Quarterly Total and Grand Total

Here is the trick

1. Create Matrix Column Group 3 on Years – This is the dummy group. Even though there is only one year in the table, creating this extra group allows you to use InScope to apply a different expression to each group subtotal.
2. Add in a Subtotal for Year to create a placeholder for the YTD Total and then use the Position property to place the subtotal before the data detail.

For the Data Expression, you can use the InScope expression as follows:

=IIF(InScope("matrix1_GroupYear"), Sum(Fields!TotalCount.Value),Sum(Fields!YTDCount.Value))

So InScope was handy once again with a little bit of extra work to get there!

Hope this helps you in your next matrix report.

One of the greatest enhancements in Analysis Services 2005 is the ability to use SQL Server Profiler to troubleshoot performance issues during querying and processing. Even cooler is the ability to import Perfmon data into Profiler so you can analyze system and Analysis Services performance counters at every step of querying and processing.

Importing the Analysis Services counters into Profiler is a fairy straightforward process; however, this is a trick along the way before you can claim success. Read below to find out more.

Setup Work

1)  First you need to set up a Counter Log in Perfmon and select the System and Analysis Service counters that you want. You should pay attention to customize the appropriate sampling interval for your workload. Essentially the interval needs to be small enough so that you can see the processing or querying events fire, but not so small that you are overwhelmed by all of the data points. Also note the location of the log file. You will need to know the location in order to import the data into Profiler.

2)  And now for the trick… Thanks to Eric Jacobsen (MSFT) for this helpful tip. When you configure the Perfmon Counter Log properties, in order to capture the Analysis Services performance counters, you must use “Run As” and supply a username/password. The reason is that the Analysis Services logging is performed under an account that does not have access to the counter DLL. If you do not supply the username / password, the counters will be missing from the log!  See screen shot for the RunAs specification.

It’s Go-Time

It’s time to troubleshoot. Let’s say that you are troubleshooting a problematic query. You can perform the following steps to coordinate the Perfmon data with a Profiler trace.

1)  First decide whether or not you want to test the query with the cache populated or cleared. If you want to test on an empty cache, then it is a good idea to clear the cache prior to starting the logs– just to keep the files clean.

2)  Create and Start a Profiler trace with the relevant events for querying. In a future blog, I will discuss the most useful events for troubleshooting. Below is a list of some of the more helpful ones :

·        Progress Report Begin / End

·        Query Begin / End

·        Query Cube Begin / End

·        Get Data from Aggregation

·        Get Data From Cache

·        Query Subcube

·        Query Subcube Verbose (the readable translation of the subcube)

3)  Start the Perfmon log

4)  Run the query

5)  When the query is done, stop the trace file and stop the Perfmon log.

6)  Note that the Perfmon log is automatically stored in the file that was initially specified in the Counter Log properties dialog. The Profiler trace is NOT automatically stored. Therefore, you must save and name the Profiler trace file.

7)  Finally it is time to import the erfmon log data. If you click on the File menu in Profiler, you may notice that the Import Performance Data option is grayed out, i.e. it is not available. To make this option available, close and re-open the trace file and then the Import Performance Data option will be available.

8)  Once you select Import Performance Data, you can choose which performance counters you want to display. Finally, you should end up with something like the screen shot below.

Happy Troubleshooting!

Mention aggregations to any OLAP geek and you will likely hear that aggregations are the cornerstones of OLAP databases.

In the new attribute paradigm of Analysis Services 2005, designing effective aggregations can be a somewhat challenging task. In fact there are many components that go into building an effective aggregation design.

In this entry, I am going to highlight the design components that determine which attributes are considered for aggregation:

·         Aggregation Definition- An aggregation summarizes measures by a combination of attributes.

·         Attribute Relationships - Creating EFFECTIVE attribute relationships BETWEEN your attributes is a best practice for EVERY cube implementation. Having the correct attribute relationships in your dimension can have all sorts of benefits beyond aggregations since attribute relationships influence how data is stored, retrieved, and queried. Unfortunately, the inverse scenario is also true, keeping the default attribute relationships that directly relate every attribute to the key attribute can lead to querying and processing performance issues. Wherever possible, the ideal scenario is to create cascading attribute relationships that indirectly relate attributes to the key attribute.

·         Aggregation Candidates –Aggregation candidates are attributes that are considered for aggregation. By default, not every attribute is considered for aggregation. It has to be invited, of courseJ. You can view the aggregation candidates for a partition when you use the Aggregation Design Wizard to design aggregations (see screen shot below). On the Specify Object Counts page of the Aggregation Design Wizard, the bolded attributes represent the aggregation candidates. If an attribute is not bolded, then it is not an aggregation candidate.

·         Aggregation Usage - The Aggregation Usage property determines whether an attribute is considered for aggregation, i.e. whether or not it is an aggregation candidate. By default, it has the value of, you guessed it, Default. Default has a variety of rules that it applies depending on the attribute type. Below are the highlights of the Default rule for a standard dimension design:

o        For the granularity attribute, no aggregation restrictions are placed on the aggregation design, i.e. the granularity attribute is a candidate.

o        If the dimension has natural hierarchies, then all attributes participating in the natural hierarchy are candidates.  Remember that a natural hierarchy is a special instance of a user-defined hierarchy with many-to-one attribute relationships defined for each level in the hierarchy. If attribute relationships are not defined for every level in the hierarchy, then it is not natural and the attributes missing relationships are not considered for aggregation.

o        For any other attribute, do not consider the attribute for aggregation.

To see how these components work together to define the aggregation candidates, the Screen Shot below for the Aggregation Design Wizard displays the aggregation candidates for 6 different design scenarios of the Territory dimension.

Each design scenario represents a different configuration based on the following three variables:

(1)   Attribute hierarchies or natural hierarchies – Scenarios 1 – 4 only use attribute hierarchies. Scenarios 5 and 6 include natural hierarchies.

(2)   Default or modified attribute relationships. Either the default Attribute relationships have been left as-is (No AR) or the attribute relationships have been modified (With AR)

(3)   Default or unrestricted Aggregation Usage property. Default uses the Default rule. Unrestricted means consider this attribute as an aggregation candidate; however, it must pass the standard aggregation cost/benefit tests.

What’s the bottom line? In the scenarios outlined above, Scenario 6 and Scenario 2 provide the best solutions from an aggregation candidate perspective. In fact, all things being equal, they should produce the same aggregation design, but I have not fully tested this to ensure that this in the case J.  

Personally I prefer Scenario 6. From an end user browsing perspective, user hierarchies provide users with pre-built navigation paths. Also from an MDX coding perspective, natural hierarchies provide easy access to navigation functions (ancestor, descendants, etc. to move around the hierarchy). You can always hide the attribute hierarchies if having both attribute hierarchies and user hierarchies is confusing to end users. Also important to note is that the SSAS OLAP client tools are taking slightly different approaches for exposing the attribute hierarchies to end users. The tools seem to be more predictable in the way that they expose natural hierarchies.

Keep in mind that all we have done in this example is to influence the aggregation candidates. You still need to specify the other aggregation options including specifying record and members counts, deciding the percent aggregation, and storage mode.

Even with these user-defined inputs, the Analysis Services aggregation design algorithm still has to evaluate the costs and benefits of each aggregation to determine whether or not it is ultimately built. … unless, of course, you build your custom aggregations using XMLA but that is a topic for another day.

Be sure to visit all the options under "Configuration" in the Admin Menu Bar above. There are 16 themes to choose from, and you can also create your own.