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.

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.

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.

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.

 

 

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

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.

I've been developing reporting solutions with Micosoft Analysis Services (originally called MS OLAP Services) since it first shipped with SQL Server 7.0.  For the last two months I've been working on a contract at Microsoft using SQL Server Analysis Services 2005 and must admit I'm highly impressed with the product.

There are many improvements to the newest version of Analysis Services.  From an application interface point of view, I'd have to say that the integration with the Visual Studio 2005 environment was a great move.  A standardized interface for all of the SQL Server components (Database Engine, Analysis Services, Integration Services, Notification Services, and Reporting Services) is pretty nice.  I like being able to run queries against multiple databases as well as cubes within the same window. 

Business Intelligence (BI) is a term used to describe software technologies and the management of data to help decision makers make better decisions.  When people ask me what BI is, I tell them its the process of turning raw data into usable information. Through the use of statistical analysis, forecasting, data-mining, and simple reporting, this usable information translates into knowledge for end users.  BI applications are sometimes referred to as Decision Support or OLAP (On-line Analytical Processing) systems.

I've been designing and developing BI software applications through my own company, dymetrics, inc., for the last six years.  My experience includes the development of data warehouses/marts, multi-dimensional databases, and user interfaces for displaying reports and querying data.

I use both Oracle and SQL Server for data warehouse/mart development, Microsoft SQL Server Analysis Services for OLAP development, and ASP.Net for front-end development.  If you are in need of a consultant to help you with your Business Intelligence intiative, feel free to email me using the link in the navigation bar to the left.