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.
In case you haven't heard yet, Microsoft recently purchased ProClarity. Here is a link to the press release.
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.
|