Darren Gosbell [MVP] - Random Procrastination

Gemini/PowerPivot MOSSIG Presentation

Darren Gosbell — Wed, 04 Nov 2009 07:58:41 GMT

I did a presentation earlier this month on PowerPivot (a.k.a Project Gemini) at the MOSSIG meeting and as promised here is the slide deck. If you've seen other "Gemini" presentations there will be nothing new in this presentation, I've really only posted it here so that those who attended the meeting can refer to it.

You can download the "short" (1Mb) version with just the content slides:

Or you can get the full (8.3Mb) slide deck including the "fairy tale" images from the link below:

Good-bye OLAP Report – Hello The BI Verdict

Darren Gosbell — Tue, 03 Nov 2009 07:48:29 GMT

I just got an email telling me about this press announcement in relation to the OLAP report becoming the BI Verdict (www.bi-verdict.com). I wonder if this is reflection of the fact that "OLAP" is a technology and "BI" is about more than a single technology…

Which Edition is my Analysis Services Server?

Darren Gosbell — Wed, 14 Oct 2009 10:20:22 GMT

There was a question the other day on the MSDN SSAS forum asking how to find out the Edition of SSAS which is running on a given server. The answer to this question could be either Standard, Enterprise or Developer. The Object Explorer in Management Studio displays the current version next to the server name, but not the Edition.

Now at a basic level I know that this is stored as a property of the server, so you could run a DISCOVER_XML_METADATA command like the following.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions>
    <RestrictionList>
      <ObjectExpansion>ObjectProperties</ObjectExpansion>
    </RestrictionList>
  </Restrictions>
  <Properties>
    <PropertyList>
    </PropertyList>
  </Properties>
</Discover>

Which returns not just the Edition, but a lot of other interesting information.

Of course not everyone likes reading XML, so another approach would be to run a short powershell script, either using AMO directly…

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
  $svr = new-object([Microsoft.AnalysisServices.Server])
  $svr.Connect("localhost")
  $svr.Edition

Or using the powerSSAS snapin (to do the same thing with a lot less typing).

  add-pssnapin powerSSAS
  $svr = get-ASServer localhost
  $svr.Edition

Or if you really want to be really boring you could right click on your server from the Object Explorer in Management Studio, choose Reports – Standard Reports – General, but that would be way too simple. :)

SQL Downunder 2009 Presentation: SSAS Metadata – Knowledge is Power

Darren Gosbell — Sun, 11 Oct 2009 04:18:07 GMT

I just finished my talk at the 2009 SQL Downunder Code Camp on Analysis Services data. As promised, attached below are the scripts and Powerpoint slides that I used in the presentation. There is not that much information in the slides, they were really just introduction and conclusion - all of the content is in the scripts. And the scripts probably only make sense if you at the session.

In addition to the scripts, below are the links to all of the free tools that I was using:

•MDX Studio http://ssas-info.com/forum/mdxstudio

•ASSP http://asstoredprocedures.codeplex.com

•PowerSSAS http://powerSSAS.codeplex.com

And the topic of BIDS Helper has come up a number of times over the weekend, so I’ve included a link to that too.

Book Review - Analysis Services 2008 Unleashed

Darren Gosbell — Sun, 04 Oct 2009 11:36:09 GMT

Full Disclosure: I was lucky enough to be sent a free review copy by the publisher

But... if I had not been sent a review copy I would have gone out and bought a copy of this book anyway. Why is that? Because I had purchased the previous edition and I knew that it would be the most in depth book on SSAS 2008 available. It has been written by members of the product team and contains a wealth of information that just could not come from any other source.

I'm sure most of you have heard that Attribute Relationships vitally important in an Analysis Services database, but have you ever wondered why? Well this book explains exactly how attributes and hierarchies are stored and you can see for yourself the additional structures that are created as a result of attribute relationships.

If you are new to Analysis Services this may not be the best book to start with as it goes pretty deep. It does start off with some introductory chapters on SSAS, but if you have never used the product before then it is probably better to start with one of the books that is explicitly aimed at getting started. But if you have done a project or two with SSAS and you are looking for some real inside knowledge, then this is the book for you.

In particular sections 5 and 6 that cover information that you will not find anywhere else. These sections deal with everything from the physical data and how data is brought in to SSAS to memory management, query processing and the thread architecture.

If you're lucky you may find it in your local book shop. if not, you can always get it from Amazon. :)

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.