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.

Today I started working with Analysis Management Objects (AMO) which is a library of classes that users work with to create and manage Analysis Services objects.  Although DSO 9.0 (Decision Support Objects) is available in Analysis Services 2005, it is being deprecated and developers are encouraged to use AMO instead of DSO in the future.  So far, I've been highly impressed with AMO.  Hopefully I can get a few scripts out in the next week or so.

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.