Darren Gosbell [MVP] - Random Procrastination

SSAS 2008 R2 – Improvements for slow Metadata

Darren Gosbell — Mon, 08 Feb 2010 12:15:43 GMT

There was a thread last year on the SSAS MSDN forum SSAS 2008 -- Why is metadata so slow??? in relation to slow metadata queries on a cube with 250+ measure groups. The good news is that despite the fact that the SSAS team has largely been focussed on PowerPivot for the SQL Server 2008 R2 release, they have also tried to address a few of the customer pain points in SSAS. Below is part of a recent response to this thread from Akshai Mirchandani from the SSAS development team:

"As an update to this discussion, we did implement an improvement to the way this EstimatedSize property works in the upcoming SQL Server 2008 R2 release -- the latest CTP includes this change. It would be great to hear from any of you who can try this out to see what the impact is on the performance in real-world situations.

This change does require you to update all your partitions and dimensions in some way -- the server will persist the estimated size of those objects at that point and not require scanning their file sizes every time you discover metadata. ProcessUpdate on the dimensions and ProcessClearIndexes+ProcessIndexes on the partitions should do the trick. Without updating the dimension/partition objects things will still work, but without the performance gain...
Additional note: due to these types of changes, you may not be able to take a 2008 R2 database downlevel to a 2008 server.

As most of you have probably heard, AS 2008 R2 is primarily targeted at the self-service user with the new PowerPivot solution -- but we've tried to tackle a few of the pain points for our existing customers along the way. Some examples:

This discovery of metadata issue

Processing of a single (typically small) partition in the context of a very large number of metadata objects (e.g. 1000s of partitions) should now be significantly quicker. The algorithm that analyzes dependencies was optimized for this scenario.

Also, R2 now supports calculated members in subselects -- and the new Excel 2010 pivot tables will let you make use of that feature."

If are having issues with slow metadata for a large database/cube then it would probably be worth giving the November CTP of SQL 2008 R2 a try. If you have any feedback for this issue feel free to post a comment either here or on the original forum thread.

Re-inventing the Wheel

Darren Gosbell — Mon, 23 Nov 2009 11:40:51 GMT

Interestingly I had two instances last week when I thought different parts of Microsoft were re-inventing the wheel.

The first was while listening to Episode 496 of Dot Net Rocks, where they were talking about project Trident from Microsoft Research. My first thought as they were describing the "visual workflows" was that MS Research had re-invented SSIS. Then as they described the versioned catalogue of workflows it sounded more like the way DTS used to store it's packages. But when they started to talk about how these "workflows" could be run in parallel on HPC clusters I realised that this was a completely different animal. Maybe this could become to SSIS what Project Madison is to SQL Server.

The second instance came via this blog post from Angus Logan about the Pivot project being worked on by the Live Labs group. I was thinking that this would be another tool along the lines of PowerPivot and Excel 2010 and thought - "Great, another data analysis project being worked on by a different team at MS. Why can't someone get the different teams working together." But when you watch the video it really does look like a different and amazing technology. While PowerPivot works with numeric data the Pivot project appears to work with images and textual data. It will be interesting to see if this technology ever converges with the rest of the MS BI stack.

Analysis Services 2008 Performance Counters showing zero

Darren Gosbell — Sun, 22 Nov 2009 12:22:06 GMT

I had an issue recently with my SSAS 2008 performance counters - they were all showing up as 0. I could see them listed in Performance Monitor, but none of them worked. The counters for my SSAS 2005 instance worked fine, but the 2008 ones did not.

I had observed this behaviour on my old Vista x86 machine and now it was happening on a fresh install of Windows 7 x64.

I tried numerous steps to troubleshoot this issue including going through this detailed blog post from the CSS SQL Server Engineers blog, but I still could not fix this issue.

It was actually a forum post about issues someone was having with IIS counters that put me onto the right track. Apparently you can have issues with performance counters due to the permissions of the account that the service is running under.

I was running my SSAS 2008 service as Network Service, changing it to run as LocalSystem fixed my issue and now my SSAS 2008 performance counters are working.

I'm sure that this could also be fixed by manipulating the permissions of the files under
"<%Program Files%>\Microsoft SQL Server\MSAS10<.Instance Name>\OLAP\bin\Counters"
but for the time being this solution is sufficient for my needs. :)

Technorati Tags: Analysis Services,Performance Counters

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.