Mark Mrachek - Business Intelligence Blog

Errors in the metadata manager

webmaster@mrachek.com (Mark Mrachek) — Thu, 18 May 2006 05:31:34 GMT

I ran into this error yesterday when I was using the Aggregation Design Wizard in Analysis Services 2005. I was finally able trace the problem to a conflict between the Aggregation Design Wizard and a custom assembly that I had in my project. I still don’t know why the custom assembly was interfering with the creation of aggregations, but here is how I resolved the issue:

1) Remove the custom assembly from your project in Business Intelligence Development Studio.

2) Deploy your project.

3) Design the aggregations for your partitions with the Aggregation Design Wizard.

4) Add your custom assembly back into your project in Business Intelligence Development Studio.

5) Deploy your project.

Custom Drillthrough in Analysis Services 2005

webmaster@mrachek.com (Mark Mrachek) — Tue, 16 May 2006 04:17:02 GMT

It’s been quite a while since I’ve posted here because I’ve been extremely busy in a release cycle, but I thought I would post an update on the custom drillthrough that I have been working on. Unfortunately, I do not have the completed code yet.

A few weeks ago, I had a conversation with Chris Webb regarding the use of a stored procedure to implement customized drillthrough in Analysis Services 2005. We had discussed a couple of ways to implement a solution that would query the underlying database to return drillthrough records.

One method was to capture the current members that made up a cell and pass those members to a Reporting Services report which would call a stored procedure and return records from the underlying database. The other method was to capture the current members of a cell and use the metadata for those members (via AMO) to construct a SQL statement that could be passed to RS which would use a stored procedure to return the drillthrough records.

I spent some time working on the SQL statement solution and got it working, but ran into a couple of problems that made me regret choosing this path. First, not all client applications handle actions the same way. For instance, if I were to construct a query by dragging and dropping my dimensions in Excel or the cube browser in SQL Server Management Studio, the queries returned immediately. However, when using ProClarity Desktop, my queries were much slower. I used SQL Server Profiler to see what was causing the holdup and found that a connection to Analysis Services would be opened and closed for every cell that was returned to the client. So, if I had 300 cells that made up a result set, 301 connections would be opened on the server (1 connection for the original query + 300 connections for the cells in the result set). This occurred because my stored procedure used AMO to read the metadata for the current members. This was puzzling because with Excel and the cube browser, these connections would not be opened unless the drillthrough action was triggered when I right clicked on a single cell…which is what I intended.

Another major flaw with this method was passing a SQL statement via the URL to Reporting Services. This left a big security hole where the SQL in the query string could be manipulated. Obviously, there are ways around this but the more I thought about this solution, the more I disliked it.

Needless to say, I’m going to pass on creating the SQL statement from within the Analysis Services stored procedure. My new plan involves two stored procedures, one in Analysis Services and one in SQL Server. I figure if I can pass the current members to Reporting Services, then I can call a custom assembly in SQL Server that will query AMO and get the metadata for the current members. After I get this metadata, I can construct a SQL statement in my SQL Server assembly, execute the statement, and then return the records.

I’m still pinched on time, but will post my results as soon as I get them completed.

Custom Drillthrough in Analysis Services 2005

webmaster@mrachek.com (Mark Mrachek) — Tue, 16 May 2006 04:17:00 GMT

I’m still pinched on time, but will post my results as soon as I get them completed.

Drillthrough on Dimensions with ROLAP storage mode

webmaster@mrachek.com (Mark Mrachek) — Wed, 19 Apr 2006 14:51:11 GMT

Recently, I was setting up drillthrough on a degenerate dimension with ROLAP storage. I encountered the error below when executing the drillthrough action.

Using SQL Server profiler, I was able to locate the cause of the issue. Looking at the trace, I noticed that the ‘IntegerData’ trace field was failing at row 310,000 so I looked at server properties to see which ROLAP related properties were set to 300,000.

The culprit turned out to be ‘ROLAPDimensionProcessingEffort’ which was set to 300,000. So, as a test, I set the property to 2,000,000 (a number higher than the number of members in my degenerate dimension) and this resolved the error.

Although changing this property resolved the error, the cost of using the ROLAP storage for this dimension turned out to be quite expensive. My greatest concern was that a huge number of dimension members were being queried and cached when I was essentially targeting only 30 records for drillthrough.

I had posted this problem to an internal message board at Microsoft and was informed that “…client applications may mistakenly try to fetch more data than they really wanted to from ROLAP dimensions…”. This is why the ‘ROLAPDimensionProcessingEffort’ property exists.

Needless to say, I’m not impressed with ROLAP dimensions and this one more reason to avoid using them if you can.

> >>>

Microsoft buys ProClarity

webmaster@mrachek.com (Mark Mrachek) — Sat, 15 Apr 2006 20:25:26 GMT

In case you haven't heard yet, Microsoft recently purchased ProClarity. Here is a link to the press release.

Panorama Dashboard

webmaster@mrachek.com (Mark Mrachek) — Sat, 25 Feb 2006 20:02:35 GMT

I've been working with a new User Interface Platform group in Microsoft Services IT for the last three months. This group is using Panorama as their BI interface within their platform.

Although I have a bias toward building custom web based applications for BI UI's, I must say that I'm impressed with Panorama. The charting is great and the security models are flexible. I would say the biggest drawback I've seen at this point is that Panorama does not handle dynamic security models in Analysis Services very well.

One thing I really like about Panorama is that you can customize slicer security by altering their base asp files which gives you maximum flexibility when dealing with data level security.

I'll try to post more information about Panorama in the future.

MDX NonEmpty() function vs NonEmptyCrossjoin() function in Analysis Services 2005

webmaster@mrachek.com (Mark Mrachek) — Mon, 20 Feb 2006 02:21:11 GMT

If you have ever used the NonEmptyCrossjoin function, you may have been surprised (and frustrated) with the results your queries returned. This function was added to Analysis Services 2000 to help with performance of queries, but it commonly returned incorrect results. Fortunately the NonEmptyCrossjoin function is being deprecated.

The NonEmpty() function is new in Analysis Services 2005 and should be used instead of the NonEmptyCrossjoin function. The NonEmpty() function is powerful because it can be applied to MDX expressions whereas the NON EMPTY clause was only available at the top level in SELECT statements.

This syntax for the NonEmpty() function is NonEmpty({set},[filter set])

I met with a developer and PM from the Analysis Services team last week in Redmond and they provided me with more information on the NonEmpty() function.

If you use the NonEmpty() function without a filterset, then the expression is evaluated using the default measure in your cube. So, if you do not define a filterset and you did not specify a default measure in your cube, then you may get unexpected results with this function. Because, even though you may not have explicity defined a default measure, all cubes have a default measure (Analysis Services will define a default measure even if you did not define one) and the NonEmpty() function will use this default measure as the context in which to evaluate the expression.

Business Intelligence Consultant.

webmaster@mrachek.com (Mark Mrachek) — Tue, 14 Feb 2006 09:05:40 GMT

I have been developing reporting systems for over nine years. For the last six years I’ve been working as an independent contractor and consultant for my own company, dymetrics, inc., which specializes in delivering Business Intelligence solutions.

In November of 2005, I joined a consulting company as a full-time employee to work on a contract at Microsoft.

Although my areas of expertise include data warehousing, OLAP, and ASP.Net development, my primary focus at Microsoft has been working with Analysis Services 2005. I have also been helping the new User Interface Platform group in Microsoft’s Services IT department implement Panorama as their Business Intelligence user interface.

Once my assignment is completed at Microsoft, I will most likely go back to independent contracting since I prefer working independently as opposed to being a full-time employee.

User Defined Functions (UDFs) in Analysis Services 2005

webmaster@mrachek.com (Mark Mrachek) — Sat, 11 Feb 2006 08:51:43 GMT

I’ve found myself working with UDF’s more and more lately and thought it would make a good blog topic. So, if you are interested in creating a UDF to use in your MDX, read on.

I’m using the Adventure Works database which is the sample database Analysis Services 2005. If you have not yet created the Adventure Works database, you can get the source files and installation instructions here.

First, create a class library in Visual Studio.

Here is a very basic sample of a C# class library I created:

using System;
using System.Collections.Generic;
using System.Text;

namespace AWLib
{
public class Utilities
{
public string GetSomeSet(){
int sec = DateTime.Now.TimeOfDay.Milliseconds;
string set;

if (sec < 200) {
set = "[Date].[Fiscal].Children";
}
else if (sec < 400)
{
set = "[Reseller].[Reseller Type].Children";
}
else if (sec < 600)
{
set = "[Employee].[Employees].Children";
}
else if (sec < 800)
{
set = "[Geography].[Geography].Children";
}
else {
set = "[Sales Channel].[Sales Channel].Children";
}
return set;
}

}
}

Next, after you have successfully compiled your class library, you need to add it to the Adventure Works database. You do this by:

1) Opening SQL Server Management Studio

2) Right click on the ‘Assemblies’ folder in the Adventure Works database, then click on ‘New Assembly…’

Note: You can also add your class library to the server assemblies so the UDF’s will be available in all databases on your Analysis Services instance. Also, you can add your assembly to your ‘Business Intelligence Development Studio’ project and deploy your assembly along with your database.

To test the UDF (from above), open a ‘New Query’ window in SQL Server Management Studio, select the Adventure Works database and execute the following MDX.

SELECT Measures.Members ON 0,
STRTOSET(AWLib.GetSomeSet()) ON 1
FROM [Adventure Works]

The AWLib.GetSomeSet() function will return different sets based on the milliseconds from the timestamp on your server.

UDF’s are very powerful and flexible and help to extend the functionality of MDX. Here are just a few examples of how you can use UDF’s 1) implementing dynamic security in Analysis Services, 2) dynamically building sets and tuples, and 3) creating complex mathematical calculations.

Clr Assembly must have main file specified.

webmaster@mrachek.com (Mark Mrachek) — Sat, 04 Feb 2006 19:30:08 GMT

I ran into this error the other day when attempting to design aggregations for a cube using the AS 2005 Aggregation Design Wizard. The full error was: "Clr Assembly must have main file specified. To restart the process, resolve the problem and then click Start."

I was able to able to resolve this error by:
    1) Stopping the services of AS where your database resides
    2) Renaming the data directory for Analysis Services Databases.
        Default location: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data (rename the 'Data' folder to 'Data_OLD')
        Note: MSSQL.2 is the location for the AS files on *my* server. You're AS folder may be named MSSQL.1 or MSSQL.3 or whatever.
                Also note, when you rename this folder, all existing databases that resided in your instance of AS are now unavailable. You will need to re-deploy all of your databases to this server (if applicable).
    3) Create a new folder in the 'C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\' directory called 'Data' (basically replacing the original 'Data' folder you renamed in Step 2 with a new "empty" folder)
    4) Start the AS Services on your server
    5) Deploy your Analysis Services Project to the server
    6) Create your aggregations.

This error has nothing to do with your database objects but everything to do with internal AS files. Why or how this occurs is beyond me, but some AS files must be corrupt which causes this error.

Conditional Statements in the MDX WHERE Clause

webmaster@mrachek.com (Mark Mrachek) — Thu, 26 Jan 2006 06:46:50 GMT

I’ve written thousands of lines of MDX code over the years to support web reporting applications and today I discovered something new. Although it probably isn’t new to MDX, it was new to me because I never needed it until today. I was targeting the ascendants of a member and based on the parent of the member, I needed to alter the slice of the cube.

Typically, when I use conditional statements, I create calculated member and handle the logic in the WITH statement. Today’s challenge was different because I was not able to control what calculated members are passed from Panorama to Analysis Services. Instead, I needed to work through the ‘WHERE’ clause to slice my data.

Anyways, I don’t know if I’ll ever need to use it in the future, but it’s nice to know that this functionality exists. Below is a sample MDX statement that uses a conditional statement in the ‘WHERE’ clause. This sample provides little value except to illustrate that you can indeed use conditional statements in ‘WHERE’ clauses.

SELECT [Measures].[Internet Total Product Cost] ON 0,
[Sales Reason].[Sales Reasons].Members ON 1
FROM [Adventure Works]
WHERE (IIF([Sales Territory].[Sales Territory].[Sales Territory Country].[Australia].Parent.Name = “Pacific”, [Sales Territory].[Sales Territory], [Sales Territory].[Sales Territory].[Sales Territory Country].[Australia]))