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.

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.

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.

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]))

I have a bias towards developing custom web-based front-ends for reporting applications.  This bias has come from working with clients who have purchased commercial front-ends only to get caught in a dependency trap.  This dependency trap has three components which are on-going software maintenance fees, large consultant fees, and application customization fees. 

Here is a prime example of the dependency trap.  I had a client who purchased an off-the-shelf application that provided about 85% of the functionality they needed.  A good question to ask would be why somebody would purchase an application that only delivers 85% of what you need?  The answer has three parts.  First, it was the best application available at the time.  Second, the VP in charge of the department did not want to build an in-house application.  And third, the salesman for the company told the VP that the application could be easily customized.

Anyway, this off-the-shelf application included 320 hours of consultant time (2 consultants @ 40 hours per week for 4 weeks) for application configuration and training.  After one month, the consultants had burned up the 320 hours on the installation of the application and on training.  However, the performance of the application was pitiful and not operating as promised.  So the consultants stayed for another four months (additional cost to my client) to do their original work correctly.

To make a lengthy story short, every time a change was requested (to customize the application) the minimal waiting period was two months the minimal cost was $8,000 per incident.  Granted, if the changes were significant, I could understand the cost and time….but one request was to have an existing column added to a report.  Since the column already existed, all the company that sold the application had to do was change the query the report was using…something that would require at most, one hour of work.  Instead, they told my client they would need to wait at least two months and pay the $8,000 to have the column added to the report.  How ridiculous.

When my clients ask me what I would use for a front-end, I always recommend developing an in-house web-based front-end because the costs are less and the in-house application will be more flexible and customizable in the future compared to the off-the-shelf products. 

A big concern for senior management is that in-house applications are dependent on the developers who build them.  I totally disagree.  If the in-house application is properly documented then it will not be dependent on individual developers.  So if attrition is a concern, you have documentation for your new developers to follow.

Here is a bit of sample code for adding relationships between objects in Data Source Views in Analysis Services.

Dim strConn As String = "Provider=MSOLAP.3;Data Source=LOCAL;Initial Catalog=AS_DATABASE"
Dim objServer As New Server()
Dim objDatabase As Database
Dim objDSV As DataSourceView

objServer.Connect(strConn)
objDatabase = objServer.Databases.FindByName("AS_DATABASE_NAME")
objDSV = objDatabase.DataSourceViews.FindByName("DATA_SOURCE_VIEW_NAME")

'Delete existing relationships in dsv
If objDSV.Schema.Relations.Count > 0 Then
   objDSV.Schema.Relations.Clear()
   objDSV.Update()
End If

'Create Relationships in DSV
AddRelationship(objDSV, "dbo_PKTable1", "PKColumnName", "dbo_FKTable1", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable2", "PKColumnName", "dbo_FKTable2", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable3", "PKColumnName", "dbo_FKTable3", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable4", "PKColumnName", "dbo_FKTable4", "FKColumnName")

'Update DSV
objDSV.Update()


Sub AddRelationship(ByVal objDSV As DataSourceView, ByVal strPKTable As String, ByVal strPKCol As String, ByVal strFKTable As String, ByVal strFKCol As String)
   Dim objPKCol As Data.DataColumn = objDSV.Schema.Tables(strPKTable).Columns(strPKCol)
   Dim objFKCol As Data.DataColumn = objDSV.Schema.Tables(strFKTable).Columns(strFKCol)
   Dim strFKName As String = "FK_" & strFKTable & "_" & strFKCol

   objDSV.Schema.Relations.Add(strFKName, objPKCol, objFKCol, True)
End Sub

If you choose to use this sample code, please note that the underscore replaces the "." in the user.object naming standard used by SQL Server.  For instance, if your table owner is dbo and your table is called tbltable1, then the complete SQL Server name of the object would be dbo.tblTable1.  Analysis Services replaces the "." with and underscore.  The name that the table in the DSV would be dbo_tblTable1.

 

Named calculations are basically customized columns (or attributes) that you create in data source views in Analysis Services.  The beauty of the named calculation is that you don't have to make changes to your source tables or views.  Instead you create a SQL statement, such as a case statement, to build an attribute locally within your data source view. 

I've used named calculations to concatenate values in two separate columns and I've also used them to create groupings for some dimensions. 

Here is an example: Let's say that you need to create names for products where the id for the product exists in a dimension table, but the product names do not exist.  For instance, your underlying table or view has a ProductID column (with values 1,2,3) and you want to use a product name instead of an id in your dimension.  What we want to do is create a named calculation called ProductName. 

You simply open your data source view in the 'Business Intelligence Development Studio'; right click on the supporting table or view, then click on 'Add Named Calculation'.  A window opens with two textboxes, one is for the name of your named calculation (Product Name), and the other is where you add your SQL Expression.  You can create the named calculation with the following expression:

CASE ProductID
WHEN 1 THEN 'Product A'
WHEN 2 THEN 'Product B'
WHEN 3 THEN 'Product C'
ELSE 'Unknown'
END

You can now add the ProductName named calculation as an attribute to your dimension in the 'Dimension Designer' (on the 'Dimension Structure' tab).  Click on the name of the named calculation (ProductName) in 'Data Source View' pane and drag it to the 'Attributes' pane.

That's it.  You've now created a customized attribute.  Obviously, you could have added the name of the product to the dimension table or view itself, but this example illustrates what is possible with named calculations.

 

I have to say one of the best tools that I've worked with thus far in Analysis Services 2005 is SQL Server Profiler. 

Although Profiler is not new to SQL Server, it is new to Analysis Services and provides developers and DBAs with tons of useful information.  It is very simple to set up a trace and see which users are connected to AS and what MDX queries they are sending to the instance of AS.

Yesterday, a developer and I were trying to resolve an issue we were having involving Panorama NovaView passing incorrect slicer parameters to AS.  We were able to create a trace and show the MDX that was being passed by Panorama to AS and discovered that AS was indeed getting the incorrect MDX due to an error in a Panorama class library.  This saved a ton of time tracking down where the problem was occurring.