Darren Gosbell [MVP] - Random Procrastination

SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Darren Gosbell — Wed, 01 Jul 2009 21:42:40 GMT

A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular database. You don't have to use a stored procedure, you can use any sort of SQL command that returns a set of rows into a rowset action.

Below is how I setup the action:

When you right click on a cell in a pivot table you will see the following:

And clicking on the "Rowset Action" creates another sheet that looks like this:

Since I posted about the ExecuteSQL function I have made a slight change to the code I checked into the ASStoredProcedure project on codeplex. I have now moved it into a separate "SQLQuery" project so that it compiles to it's own .dll file and does not have to be deployed with the same impersonation mode as the main ASSP assembly.

Technorati Tags: ASSP, Analysis Services, Stored Procedures, Codeplex

MVP for another year

Darren Gosbell — Wed, 01 Jul 2009 21:34:14 GMT

It was very exciting to see an email from the MVP Award program in my inbox this morning saying that I had been re-awarded as an MVP for another year. This will be my 4th year as an MVP and I am very honored to have been re-awarded.

SSAS: Executing Arbitrary SQL queries

Darren Gosbell — Thu, 18 Jun 2009 13:20:35 GMT

I had a question a little while ago via my blog about possibly using a rowset action to execute a SQL query against a specified table. Although a rowset action will allow you to enter a SQL query, such a query is still executed against the current cube and only the subset of SQL supported by SSAS can be used. Basically the rowset action just returns a flattened result set.

However what would be possible would be to write a .Net stored procedure and use that to execute your SQL query. The code itself is really simple, the whole procedure only takes a few lines:

using System;
using System.Data;
using System.Data.OleDb;

namespace ASSP
{
    public class SQLQuery
    {
        public static DataTable ExecuteSQL(string connectionString, string sql)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            
            DataTable dt = new DataTable("Results");
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
            da.Fill(dt);
            return dt;
        }
    }
}

And with this small amount of code you can execute any query that you like using the CALL statement

eg.

call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"Select * from DimCurrency");

Which is really cool if you want to do your own drillthrough or return an arbitrary recordset (assuming that your client application supports rowset actions). By you can actually execute more than just any query, you can actually execute any statement.

So you can paste the following set of statements into SSMS and run them.

call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"Create TABLE myTable (id int)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"INSERT INTO myTable VALUES(1)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"SELECT * FROM myTable");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"DROP TABLE myTable");

Even though I can only see this technique being used for SELECT statements, you can basically do anything you like in the database provided you have the appropriate rights. Pretty powerful, initially I thought that this might be too powerful to put into the ASStoredProcedures Project on codeplex, but the more I think about it the more I think I was being paranoid, so I have checked this code into the ASStoredProcedure project. With the default deployment options a user can only perform operations that they already have rights to do anyway. It's really only if the assembly is deployed to run under the service account and if the service account has more rights than your end users that there is any risk of someone running a statement with elevated privileges.

Ultimately I'm not sure how directly useful this simple procedure will be, but I think it will provide a starting point from which more sophisticated routines can be developed using some of the other techniques demonstrated in other functions in ASSP. I'd be interested to hear comments from anyone who ends up adapting this routine.

Update 19 Jun: I removed the "ReadOnly=1" from the end of the connection string - as it was not actually doing anything

Technorati Tags: Analysis Services, Stored Procedures, ASSP

SSAS: Powershell to replace a group member in a role

Darren Gosbell — Wed, 10 Jun 2009 22:41:23 GMT

There was a question in the SSAS forum recently on how to replace one group name with another within the membership of a number of SSAS roles in a number of databases. While you could possibly do this with XMLA it would be tricky as you have to re-submit the whole membership list, you can't just add/remove single members. The easiest way to do this is to write something using the AMO library and in my opinion the easiest way to write a script for AMO is using Powershell.

Below is my short script which loops through all roles in all databases on the server and swaps out one group or user with another. I tried to make the script verbose and readable and I added some strings which are echoed out to the console so that you can see the roles and members that the script is iterating over.

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost\sql08")

foreach ($db in $svr.Databases)
{
# Print the Database Name
"Database: " + $db.Name
foreach ($role in $db.Roles)
{
    $foundMember = $null
    # Print the Role Name
    "   Role: " + $role.Name    #Print the
    foreach ($member in $role.Members)
    {
     # Print the member name(s)
      "      " + $member.Name
      if ($member.Name -eq "domain_name\old_group_name")
      {
        $foundMember = $member
      }
    }
    If ($foundMember -ne $null)
    {
      "    Member Found!"
      $role.Members.Remove($foundMember)
      $newRole = New-Object Microsoft.AnalysisServices.RoleMember("domain_name\new_group_name")
      $role.Members.Add($newRole)
      $role.Update()
    }
}
}
$svr.Disconnect()

A fix for the PerformancePoint 2007 Scorecard font issue

Darren Gosbell — Wed, 10 Jun 2009 05:45:43 GMT

The Dashboard Designer in PerformancePoint Server 2007 gives you the ability to set a number of the properties for the font of a given column in a scorecard including the font-family and the font-size. You can see these settings change in the designer and you can preview the scorecard and see how your scorecard will look when it is deployed.

I changed the font in the screenshot above to Wingdings so that you could easily see the difference. In practice this is probably not a typical change, but you might want to make the font a bit bigger or possibly use a font to get a sparkline in your scorecard.

However, when you actually deploy your scorecard to Sharepoint you will be greeted with the following:

The fonts are all reset to 8pt Arial, regardless of the actual settings you specified in the scorecard designer for the font family and size.

This took a fair bit of digging an may not make too much sense if you have not done a bit of web development, but I have isolated the issue in the screen shot below.

This is part of the markup for a scorecard webpart, showing just the parts that highlight this issue. Right up at the top you can see a DIV tag with the ms-WPBody style, this is the root of our issue. I have pointed out the TD tag a bit further down which is where the font settings from the scorecard are applied and then further down you can see a second TD and inside that is a DIV which contains the actual data for the scorecard cell.

The ms-WPBody style has an entry in the core.css which is part of Sharepoint and it applies a default font-family of Arial and a size of 8pt to any contained TD elements, the inline style that is specified in the TD at the column level gets overridden because of the TD wrapping the cell contents.

One fix for this issue involves deleting the section in core.css that contains the reference to ".ms-WPBody TD" but that is rather extreme. It could affect the look of other elements on your Sharepoint site and service packs and upgrades could undo this.

My current fix involves installing the jQuery SmartTool from codeplex then adding a ~~custom~~ content editor webpart with the following script to the page containing the scorecard.

<script language="javascript" type="text/javascript">
  _spBodyOnLoadFunctionNames.push("fixPpsFonts");
  function fixPpsFonts()
  { $(".ms-WPBody").removeClass("ms-WPBody"); }
</script>

I set the visibility property of custom content webpart to false and then exported the webpart and added it to the gallery so that I could easily add it to other pages.

It's not an ideal solution at the moment as I have to make sure to manually add the webpart if I ever re-deploy the dashboard, but I think it's better than the alternatives.

Ideally I would like to be a bit more intelligent and only strip off the ms-WPBody class if the webpart contains a scorecard, but because the scorecards are rendered asynchronously using - the scorecard does not actually exist at the time the script runs, so I'm not sure that it's even possible to implementing this without changing the PerformancePoint Server code.

Update: 27 June 2009 - You need to enable the SmartTools.jQuery feature before you can use it. I have added some screen shots below to show how this is done.

You need to be either a Site Collection administrator or higher to perform the following steps. You start by going into the Site Actions menu and then to the "Modify All Site Settings" option.

Then you go into the "Site collection features" option.

And you enable the SmartTools.jQuery feature

Then you can go back to the page with your PPS Dashboard and add the snippet of javascript. Use the Source Editor button to paste in the script and set the Hidden property in the Layout section so that the webpart does not display anything to the end users.

BIDS Helper release 1.4.1

Darren Gosbell — Wed, 13 May 2009 20:40:03 GMT

I am happy to announce that we recently put out a new release of BIDS Helper. I have copied the release notes out below, but one important "feature" that is not listed is that we now have a build script which is a modified version of the psake Powershell build script written by James Kovacs.

It had gotten to the point where it was a bit of an effort to do a build. You had to start by making sure you had the latest version of the source code, then version number had to be updated in a number of spots, then you had to do the compile, then run the installer script, then create the zip file for the xcopy deploy and once that was all done for the SQL 2005 version the whole process had to be repeated for the SQL 2008 version. So now we build an entire release in a single operation that only takes a minute or two.

There is also a new Version Notification feature that will periodically check if there is a newer release on codeplex and let you know if there is a new version available for download. So once you are running this release, BIDS Helper itself will let you know when a new release is available.

As always, if you have any ideas for features that you would like to see you can add a feature suggestion to the issue list.

This release incorporates the following major features:

General
- Version Notification
Analysis Services
- Sync Descriptions
Reporting Services
- Smart Diff

This release also incorporates the following bug fixes and enhancements:

An Analysis Services 2008 nugget

Darren Gosbell — Tue, 12 May 2009 13:18:23 GMT

I was experimenting with some of the PerformancePoint APIs today, but I was doing something wrong as I kept getting ERROR! back. I assumed that some of the parameters that I was using was resulting in invalid MDX being generated. I was running against a test database on SSAS 2005 and as I suspected there was a syntax error in the MDX. Unfortunately Profiler against SSAS 2005 showed me the error, but not the offending MDX, which was not much help.

On a whim I decided to move my test database to SSAS 2008 and what do you know - profiler now shows the full MDX in the error message! This allowed me to see that the it was the parameter that was mapping to the slicer that was not working.

I'm sure there are plenty of other little areas of fit and polish like this which don't make it into an official feature list, but make it worth upgrading to SSAS 2008.

SSAS: T-SQL Equivalent for a Many-to-Many relationship

Darren Gosbell — Sun, 03 May 2009 11:47:03 GMT

This question came a while ago now in this SSAS forum thread: What is wrong in my query and I thought it was something that may interest other people.

Basically it boiled down to trying to find a T-SQL equivalent to the following MDX which is querying a dimension with a many-to-many relationship to the measure.

So given the following simple MDX query, what would be the equivalent in SQL?

select 
  measures.[Internet Sales Amount] on 0
  , [Sales Reason].[Sales Reasons].[Reason Type].Members on 1
FROM [Adventure Works]

Well, what I came up with was the following where I ended up effectively joining to the fact table twice. I don't know about you, but I'd rather write the MDX version any day. :)

SELECT 
  m2m.SalesReasonReasonType
  ,Sum(f.SalesAmount) 
FROM FactInternetSales f
INNER JOIN 
(
    SELECT DISTINCT salesOrderNumber, SalesOrderLineNumber, D.SalesReasonReasonType
    FROM [dbo].[DimSalesReason] AS dim
    INNER Join dbo.FactInternetSalesReason isr
        ON dim.SalesReasonKey = isr.SalesReasonKey 
) m2m
    on f.SalesOrderNumber = m2m.SalesOrderNumber 
    And f.SalesOrderLineNumber = m2m.SalesOrderLineNumber 
GROUP BY m2m.SalesReasonReasonType

Technorati Tags: Analysis Services, MDX, Many to Many, SQL

PowerShell: List all the senders from an Outlook folder

Darren Gosbell — Wed, 08 Apr 2009 11:17:54 GMT

A friend of mine was looking for a tool today to extract a list of names and email addresses from a folder in outlook. I know that Outlook has a comprehensive COM based object model which I figured that I should be able to access from Powershell. I quick search turned up articles from both James Manning and Lee Holmes on automating Outlook from Powershell (which I think I have come across before). A bit of poking around using the get-member helped me locate the properties I needed and resulted in the following script.

It access the Personal\Fun folder in my inbox and exports a list of names and email addresses for anyone that has sent me a joke (or at least those which were worth keeping)

$olFolderInbox = 6
$ol = new-object -comobject "Outlook.Application"
$mapi = $ol.getnamespace("mapi")
$inbox = $mapi.GetDefaultFolder($olFolderInbox)
$msgs = $inbox.Folders.Item("Personal").Folders.Item("Fun")
$msgs.items | Select-Object SenderName, SenderEmailAddress -unique | export-Csv c:\emails.csv -noTypeInformation

Taking this a bit further, I wrapped this code into a script so that it could take in the path to an outlook folder and returned a collection of names and addresses.

So the following call will display the output to the console

.\get-OutlookFolderSenders.ps1 "Personal\Fun"

And to export them to a file you can just pipe through to the export-csv cmdlet

.\get-OutlookFolderSenders.ps1 "Personal\Fun" | export-csv "c:\email.csv" -noTypeInformation

Or if you want a html page you can do the following:

.\get-OutlookFolderSenders.ps1 "Personal\Fun" | convertTo-html | "c:\email.htm"

I developed this little snippet of code using Powershell Analyzer which I still favour as my main Powershell IDE even though it is not longer being actively developed. It just fits with the way I like to work.

Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Darren Gosbell — Sun, 29 Mar 2009 10:28:03 GMT

I recently had to do some simple debugging of some script tasks and components in SSIS and through I would share the simple technique that I was using. While this is only just above the level of debugging using MessageBoxes it can still be quite handy. It basically consists of printing information messages to the Progress/Execution Results window in BIDS. The syntax differs between the ScriptTask and the ScriptComponent and I keep forgetting it, so I figured if I posted it here I should be able to find it later without too much trouble.

For ScriptTasks in the control flow you use the following

Dts.Events.FireInformation(0, "<SubComponentName>", "<Message>", "", 0, true)

For ScriptComponents in the data flow you use the following

ComponentMetaData.FireInformation(0, "<SubComponentName>", "<Message>", "", 0, true)

Both these calls cause an information message like "[ <SubComponentName> ] <Message>" to be written to the Execution Results window in BIDS.

This can be useful for displaying the values of variables and for printing out trace messages when certain logic has been invoked.

The screen shots below are from a simple test package which just loops through every database on a server and selects a list of all the tables. In the data flow task I have a script destination component that raises information events with the database and table name in the description. below is a copy of the code and a snippet of the Execution Results window that shows the output.

Then at the end of the control flow I have a script task that just prints out a message that the packages had finished.

OT: OfficeLabs Search Commands for Office 2007

Darren Gosbell — Wed, 18 Mar 2009 11:41:07 GMT

I quite like the new ribbon interface in Office 2007, but occasionally I still find myself sometimes hunting for commands that I know exist somewhere.....

If you are the same, then you will be pleased to know that there is a really good solution to this situation in the form of the Search Commands plugin from the OfficeLabs team at Microsoft. It adds a tab to the ribbon that lets you search all the commands in any of the office applications.

If for instance you could not find the Spell Check facility in Excel 2007 you can search for it:

And you not only get a button that you can use to launch the feature, you get a tool tip that tells you the location in ribbon for that given command.

The other interesting thing is that there are some commands that are not on the ribbon at all, but with this tool you can easily find and execute them without having to add these commands to the quick launch or anything like that.

There are a couple of other projects on the OfficeLabs site including the pptPlex one which look quite interesting.

Technorati Tags: OfficeLabs, Office 2007

SSAS: There is no such thing as an Attribute in MDX!

Darren Gosbell — Sun, 15 Mar 2009 12:04:03 GMT

In MDX in SSAS the term "attribute" occasionally gets used interchangeably with "hierarchy" and "level", but it's not technically accurate.

In SSAS, MDX has concepts of Dimensions, Hierarchies and Levels, but not Attributes. Attributes and Attribute Relationships are design time concepts that are understood by the storage engine, but they are not directly exposed in the MDX language. Attributes actually map to levels (or properties) in MDX. I think the confusion comes about because, by default, an attribute generates a hierarchy with a single level in it that both have the same name as the underlying attribute. (I also think that some of this confusion comes about because of the way that SSAS coalesces different objects, but that is a topic for another time.)

So if you create a Month attribute in the Date dimension, when you process it will generate a Month Hierarchy and a Month Level so in the form <Dimension>.<Hierarchy>.<Level> you end up with [Date].[Month].[Month].

I think that understanding the different types of objects and how they interrelate if fundamental to being able to understand MDX.

In MDX terms:

~~a dimension contains a collection of 1 or more hierarchies,~~
a hierarchy contains a collection of 1 or more levels
and a level contains a collection of 1 or more members

...and there is no such thing as an attribute. :)

Update [17 Mar 09] - As Mosha has pointed out in the comments, MDX does not have a concept of dimensions either, just hierarchies. There are some references to dimensions, things like the .Dimension function (which actually returns a hierarchy and I think is just a hang over from AS 2000) and member unique names can be expressed in terms of [<dimension>].[<hierarchy>].[<level>].[<member>]. So I think that the dimension concept does "leak" into MDX a little bit.

How to build your own Super Model - Melbourne SQL User Group

Darren Gosbell — Mon, 16 Feb 2009 12:47:46 GMT

Next week I am giving my talk talk on "How to build your own Super Model" to the Melbourne SQL User Group. This is the same one that I presented to the Adelaide User Group last month.

It is an introductory look at dimensional modeling for Analysis Services. Where we will talk about what it is, how it’s done and look at the features that Analysis Services provides to support some of the different modeling techniques. The focus of this session will be around the various types of dimension usage, looking at regular relationships, fact relationships, many-to-many relationships and reference relationships.

So if you are in Melbourne and are free next Tuesday, March 17th after work you can get the full details for the event and register for it here

BIDS Helper 1.4 released

Darren Gosbell — Sun, 08 Feb 2009 11:48:46 GMT

A little while ago we released v1.4 of BIDS Helper. Quite a few of you seem to have found it anyway, but for those of you who have not, below is a summary of what you can find in this release.

Release 1.4 contains a number of new features:

Analysis Services
- Parent-Child Dimension Naturalizer
- Test Aggregation Performance
Integration Services
- Sortable Package Properties Report
Reporting Services
- Dataset Usage Reports
- Delete Dataset Cache Files

And it contains a number of enhancements and bug fixes:

Analysis Services
- Enhancements to Edit Aggregation Dialog
- improved Printer Friendly Aggs report
Integration Services

The other reason that I wanted to post about this is that codeplex has added the ability to add reviews and ratings for project releases. BIDS Helper does not currently have any of these so if any of you feel motivated to add a rating or review to the current release that would be really cool.

As always, if you have any questions, issues or suggestions for new features you can use the discussions or issues tabs on the project site.

Technorati Tags: BIDS Helper

MDX equivalent of a filtered GROUP BY in SQL

Darren Gosbell — Tue, 27 Jan 2009 19:54:09 GMT

Does that title make sense? I don't know if it does, but I can't think of another description for this problem. If anyone can think of a better title I would love to hear it. It's hard to explain in words so let's jump into some code examples.

Consider the following SQL statement against the AdventureWorksDW relational database. The requirement is to select a list of 4 cities and then want to see the order quantity grouped at the country level.

SELECT 
    g.EnglishCountryRegionName Country
    ,sum(OrderQuantity) as OrderQuantity
FROM dbo.FactResellerSales rs
INNER JOIN dimReseller r
    ON r.ResellerKey = rs.ResellerKey
INNER JOIN dimGeography g
    ON g.GeographyKey = r.GeographyKey
WHERE g.City IN ('Melbourne','Sydney','Seattle','New York')
GROUP BY g.EnglishCountryRegionName

This query returns the following result:

How can we do an equivalent query in MDX?

if you start with something like the following it gets you the correct raw figures, but it breaks the amounts out for each city and we want to see the sub-totals by country.

SELECT 
  [Measures].[Reseller Order Quantity] on 0
  ,{[Geography].[City].[Melbourne]
   ,[Geography].[City].[Sydney]
   ,[Geography].[City].[Seattle]
   ,[Geography].[City].[New York]} on 1 
FROM [Adventure Works]

This is the result that you get:

So then you might figure that putting the country members on the axis and the cities in the where clause should do the trick.

SELECT 
  {Measures.[Reseller Order Quantity]} on 0
  ,[Geography].[Country].[Country].Members on 1 
FROM [Adventure Works] 
WHERE (
    {[Geography].[City].[Melbourne]
    ,[Geography].[City].[Sydney]
    ,[Geography].[City].[Seattle]
    ,[Geography].[City].[New York]})

But then we get the following...

So what is going on here? The query has returned the total amounts for the entire countries, not just the sub totals for the cities in the WHERE clause. Because of the attribute relationship between Countries and Cities, SSAS has put the country members on the rows that are related to the cities in the WHERE clause. In effect what this query is roughly saying to SSAS is "show me the Reseller Order Quantity for the Countries that contain one or more of the following cities. There is a more in depth explanation of this behavior here: Attribute Relationships Explained

So what we want to do is to get a measure that is filtered to figures for the selected cities. One way of doing this would be to created a calculated measure like the following:

WITH MEMBER Measures.FilteredSales as 
    SUM(
        EXISTING {[Geography].[City].[Melbourne]
                ,[Geography].[City].[Sydney]
                ,[Geography].[City].[Seattle]
                ,[Geography].[City].[New York]}
   , [Measures].[Reseller Order Quantity])
SELECT 
  {Measures.[FilteredSales]} on 0
  ,[Geography].[Country].[Country].Members on 1 
FROM [Adventure Works] 
WHERE (
    {[Geography].[City].[Melbourne]
    ,[Geography].[City].[Sydney]
    ,[Geography].[City].[Seattle]
    ,[Geography].[City].[New York]})

This gets the result that we are after, but it was a rather convoluted solution.

However, there is an easier way. We could use a sub-select...

SELECT 
  [Measures].[Reseller Order Quantity] on 0,
  [Geography].[Country].[Country].Members on 1
FROM ( 
   SELECT
      {[Geography].[City].[Melbourne]
      ,[Geography].[City].[Sydney]
      ,[Geography].[City].[Seattle]
      ,[Geography].[City].[New York]} on 0
FROM [Adventure Works] )

Which returns the following:

Which is exactly what we were after. I usually tend to avoid using sub-selects when I can as I don't like how they can affect calculated measures because the sub-select is not visible to functions like .CurrentMember. But there are situations like this, where they are perfectly suited and are much simpler than an alternative solution.

Technorati Tags: MDX, Attribute Relationships, Analysis Services