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.
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.
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.
|