BI Blogs

TDWI (particularly Wayne Eckerson) has always had a hangup about using Excel for BI, typically calling BI done in Excel “spreadmarts”. While I don’t fully agree that using Excel is as bad as people say it is, there are definitely some problems if it’s not used in the proper context. So this link is for Wayne: how Excel becomes a database.

As many of you already know, installation on Microsoft Excel on the SSAS 2005 server
is needed in order to use Excel functions in MDX. That’s very helpful because MDX
is lack of many important functions such as Round (!). Many organizations don’t like
it at all, but here’s something that may help. In the SSAS 2005 server you don’t need
to install the whole program, only the .Net programmability support. In the installation,
choose to manually pick up which components you wish to install and then
choose the .Net programmability Support as seen in the picture:

Notice that this issue will
not be fixed in SSAS 2008, so this tip will be relevant for a long time.

Just got home. Most of my day (and my co-worker’s also) went on with a big installation
of the second block of our BI project. In the morning we really thought that maybe
this time, yeah - just this time things will go better. After more than 12 hours I
laughing at myself: How could I be so naive? Many things that could go bad just did
but after it all ended (with a happy ending, otherwise I wouldn’t be here, writing
in my home sweet home) I can say that the big blame is on Informatica PowerCenter.
We’re using version 8 of the software. It’s not new software that started its way
yesterday: It’s a very old and familiar software. So how can it be that when we copy
mapplets (ETL processes, for those of you who don’t know Informatica) from one repository
to another, some lines are just deleted from the mappings? After that you check your
dimensions in MS-OLAP and you don’t understand what happened there. A whole level
in a big dimension that has only one member - 0 ?? Zero member is null. Yeah, we were
right - the line in the mapping just been deleted by our precious Informatica so the
column is all null.

Well, I happy we’re through with this. Good night.

P.S.
Tomorrow I’m taking a day off…

P.S 2

Although many things went wrong in the installation, I really think we had a good
block this time. This block contains many beautiful things in MS-OLAP, MDX and Informatica.
You’ll see it here in the next few days, after I’ll calm down.

In the last few days I’ve seen more and more articles and blog posts about the market shift to software-as-a-service BI. Here are a few that are worth reading:

• On-Demand Business Intelligence - A blogger I had not read before cites some useful Gartner SaaS research and points to the rise of “vertical-focused solutions.”
• It’s Been a Most Interesting Year - Claudia Imhoff hightlights “SaaS coming to BI” as a 2007 highlight and points to “the pressures of reducing the costs of BI enviroments” as a driver.
• Business Intelligence 2K8 - summarizes many of the predictions that have been put forward for 2008 following the LucidEra list from earlier this month.
• Getting Smart is Getting Cheaper - eWeek article cites JupiterResearch prediction that “35 percent of small and mid-sized businesses would deploy a Web analytics-based solution within the next 12 months.”

Clearly on-demand BI momentum is growing as more and more organizations are getting value from business analytics as a service. (BAaaS is better than just AaaS, right?).

A few years ago Fortune Magazine ran a rather ironic headline that is still somewhat hard to believe today: This Time Tom Siebel Guessed Wrong. In the article Tom Siebel even stated: “We don’t really have a thorough analysis of the pipeline here with us today.”

This article illustrated that when the market is turbulent (okay, it was downright dreadful in 2002), even a CRM company that evangelized the importance of business visibility and the need for a “360 degree view of the customer” couldn’t avoid surprises.

So how do you avoid year-end surprises? Here are a few suggestions that are worth considering as you close out 2007 and prepare for 2008:

• Trending. ”Those who cannot learn from history are doomed to repeat it.” It’s a quote that’s been attributed to both Winston Churchill and George Santayana. If you’re unable to track and monitor sales performance over time you’ll always fall victim to quarter-end surprises. They say that in sales ”the trend is your friend” so don’t go another quarter without the ability to get answers to this type of question:

• What is my pipeline this week and what is the change from last week, in total and by rep?
• What is the trend of some of my key pipeline statistics over several quarters, by pipeline stage?
• What is the trend of product sales by my lowest-performing sales reps?

While I certainly hope that all of your surprises are happy ones in the last few days of 2007, my guess is that the ability to monitor sales trends, take snapshots of your pipeline, and analyze the complete forecast-to-billing cycle will help you avoid surprises in 2008. 

Happy New Year!

I had a notification recently that one of the issues that I have reported to the SQL Server connect site (http://connect.microsoft.com/sql ) had been fixed.

Deploying a Renamed Database from BIDS
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281595

This issue was closed a little while ago as being a duplicate of a bug in the internal bug database, which I was a bit disappointed in as this meant that I would no longer get automatic feedback on this issue. So I was pleasantly surprised when I got feedback that this issue has apparently been fixed in SQL 2008. It appears that someone has taken the time to return to the connect issue and update it’s status. There are literally thousands of bugs and suggestions logged on the connect site and it is nice to see Microsoft taking the time to update issues like this.

Just in time for another early Christmas present the Analysis Services Stored Procedure team have just released version 1.2. It incorporates and couple of new functions and a few alterations. Below are the main additions, you can follow the links to the wiki pages for more information about each of the following.

StrToSet - is a new class that incorporates a couple of new functions. The functions in this class are designed to allow the StrToSet function to receive shorter strings by passing in just a list of keys, not the entire member unique name. This works particularly well in the context of Reporting Services reports.

PartitionHealthCheck - is another new class. The functions in this class are designed to allow cube administrators to check whether the dataid ranges of the partitions in a measure group overlap.

DMV - this function has been added to the xmlaDiscover class and builds upon the functionality already available in this class to provide the capability of executing SSAS 2008 style DMV queries against SSAS 2005 and even includes some functionality that is not supported in the SSAS 2008 DMVs (as of CTP5) like the ability to use LIKE in the WHERE clause and the ability to work with rowsets that have mandatory restrictions.

You can get it from here: https://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=ASStoredProcedures&ReleaseId=7228

The BIDSHelper team have just released an early Christmas present.

I have just uploaded the install for the version 1.2.0.1 release of BIDSHelper to www.codeplex.com/bidshelper. As you can probably guess from the numbering, this is not a major release, it contains a couple of bug fixes and a little bit of performance tuning.

In particular:

• An issue with multiple BIDSHelper buttons appearing in the calculations tab of the cube designer has been fixed.
• An issue where the configuration/expression highlighter component failed to work has been addressed.
• A slight performance optimization has been added to the expression/configuration highlighter.

Merry Christmas.

I was reading Vidas Matelis’ recent post on the metadata rowsets in SSAS 2008 and this got me to thinking about a discussion that Chris Webb and I had recently about the new “DMV” functionality is SSAS 2008. And that it basically that they are simply gives you an easier way to access information that is available through XMLA discover commands. And that you could already get to this information relatively easily through the Discover() function in the Analysis Services Stored Procedure (ASSP) project.

This has prompted me to do a bit of exploration around DMV’s and schema rowsets and this is the first of a few posts in this area.

This first post in the series is about a way to get at this information through powerSSAS (www.codeplex.com/powerSSAS) and that is by using the send-XmlaDiscover cmdlet.

The easiest way to explain how this cmdlet works is through an example, so let’s have a look at one.

Send-XmlaDiscover localhost MDSCHEMA_CUBES | WHERE {$_.CubeSource -eq 1} | Format-Table CubeName, BaseCubeName

Now if you don’t have much experience with PowerShell the syntax may seem a little unusual. I’m pretty sure I have said this before, but there is a bit of a learning curve when you first start using PowerShell but it is worth sticking with it as it is really powerful.

So let’s break down my example and have a look at what is going on.

1. Send-XmlaDiscover localhost MDSCHEMA_CUBES

You will notice that I start off with the Send-XmlaDiscover cmdlet, this is part of powerSSAS and you will have to have added the snapin to have it available. I am passing two parameters to this cmdlet, the name of the SSAS server and the rowset to return. And what comes back from that cmdlet is a set of objects. This is an important distinction, it is not returning rows of text, but a collection of objects. If you want to see what those objects “look” like, then you can “pipe” the output  to the get-member cmdlet.

eg. Send-XmlaDiscover localhost MDSCHEMA_CUBES | get-member

The get-member cmdlet is great for discovering the “shape” of the objects that are being returned. This can be quite handy with the send-XmlaDiscover cmdlet as it returns differently “shaped” objects depending on the rowset parameter. What I mean by this is that the resulting objects will have a different set of properties depending on the rowset that is queried.

2. | WHERE {$_.CubeSource -eq 1}

To filter the collection of objects returned from send-XmlaDiscover I am using the where-object cmdlet (aliased as “where”). The vertical bar is the pipe character which sends the objects output by send-XmlaDiscover as input to the where-object cmdlet. The parameter that is being passing to the where-object cmdlet is what is known as a script block (it uses curly braces like sets in MDX) and when this script block evaluates to true the input object is passed through to the output.

Inside the script block the underscore variable “$_” represents the current object and I am testing the CubeSource property to see if it is equal to 1. You will notice that the property is called CubeSource and not CUBE_SOURCE, I have built this feature into the cmdlet, in that it removes the underscores and “proper cases” the columns from the xmla rowset. You will also notice that instead of using the “=” symbol to test for equality that powershell uses the -eq operator. In fact powerShell uses a number of two letter operators.

-eq	equal to
-ne	not equal
-gt	greater than
-ge	greater than or equal to
-ltÂ	less than
-le	less than or equal to
-match	matches using regular expressions.

 

3. | FORMAT-TABLE CubeName, BaseCubeName

In the last section of this script I am piping to the format-table cmdlet (this can also be aliased as “ft”) and only returning the CubeName and BaseCubeName properties. Format-Table is a cmdlet that I use quite a bit, it outputs a collection of objects putting each property in it’s own column.

So with Send-XmlaDiscover combined with the built-in cmdlets of where-object (where), sort-object (sort) and format-table (ft) you can do pretty much everything that the new DMV’s in SQL 2008 can do plus a bit more.

Here are a few more examples for you to consider (each of the three examples should be entered on a single line):

Send-XmlaDiscover localhost DISCOVER_SCHEMA_ROWSETS

Send-XmlaDiscover localhost MDSCHEMA_MEASUREGROUPS | where {$_.MeasureGroupCaption -Match “Sales”} | sort MeasureGroupName | ft CubeName, IsWriteEnabled

Send-XmlaDiscover localhost DISCOVER_PROPERTIES | where {$_.PropertyAccessType -match “Write”} | ft

One of my statements in my last post about the new Aggregations tab was not entirely accurate…

I stated that to edit aggregations you would still need BIDSHelper, but as I have recently seen demonstrated and as a sharp eyed Greg Galloway has noticed - there is an Advanced View button which lets you do just that from within BIDS.

One of the interesting features it has is a status row that exposes the AMO warnings. There was a bit of a lag when I was testing it which meant that it was hard to tell exactly what had caused the error without hovering over the warning icon to get the list of errors. But to be fair I was clicking around fairly fast and you would probably be a bit more deliberate when adding/editing aggregations manually.

Anyone who has been reading this blog for a while will know that I was quite taken with PowerShell when it was released and was even playing with it back when it was only known by it’s code name of “Monad”.

Well, back when it was still known as Monad, I started experimenting with writing a provider for Analysis Services. It was one of those side projects that I was toying with in my spare time and I kept telling myself that I just need to add one or two more features and then it would be ready for release. Well there are still more features I would like to add, but I figure I am at a point now where I have a good understanding for how it all hangs together and how I can add additional functionality going forward.

So today I am happy to announce the first public release of what I am calling powerSSAS. I have released it as an open source project on codeplex and you can visit the home page here www.codeplex.com/powerSSAS or download the installer from the Releases page.

So, you might be wondering - Why would I want to use PowerSSAS? - well, I’m glad you asked, let’s have a look shall we?

PowerShell includes functionality that allows it to present any hierarchical data store as a “drive” so that it can be navigated in much the same way as you would navigate the file system. PowerShell itself includes a number of these providers including ones for the registry and environment variables as well as some for PowerShell objects like functions and aliases. The core functionality of powerSSAS is the ability to present information from the AMO library as if it was a drive.

The screenshot below is taken from PowerShell Analyzer which is a very cool tool for developing PowerShell scripts (I am fortunate enough to have a complimentary copy of it). One of the features of PowerShell Analyzer is it’s provider explorer, which gives you a Windows Explorer style view of your PowerShell providers.

Below is another screenshot from the Provider Explorer in PowerShell Analyzer, this time I have called the drive “amo” - you could connect to any number of servers and create a drive for each one.

The ServerProperties is interesting as it lets you browse all the properties. If you look at the example below you can see that I have selected the LogDir setting and the properties window in PowerShell Analyzer displays all the properties that relate to the LogDir setting.

In the screenshot above you can see that I have a particularly imaginatively named role called “Role” which has the highly secure “Everyone” group as a member - this is an example of how never to set up a role

But the goodness does not stop there. Even though PowerShell Analyzer rocks, you don’t need it to work with powerSSAS.

 Below is a screen shot of a session with the default PowerShell shell. (I don’t normally have a purple prompt, but it helps to differentiate the input/output from the prompt)

So let’s step through what was going on with each of those commands:

PS > add-pssnapin powerSSAS

This adds the powerSSAS assembly to this shell session, making the provider and all the cmdlets available in this session (there are ways this can be automated, but that is a topic for another post)

PS > new-psdrive demo powerSSAS localhost

Here I am telling powerSSAS that I want a new drive called “demo”, that I want to create it using the “powerSSAS” provider and that it should connect to the “localhost” server (this can be any valid SSAS server instance that you have access to)

PS > demo:

This line navigates to the “demo” drive, this is not a standard provider feature, if you look at the functions on the system you will see that I have made the provider generate a function with the name of the drive followed by a colon which sets the location to the powerSSAS drive. This is a trick that the PowerShell team themselves use to navigate to file system drives like C, D, E, etc by typing C:, D:, E: etc.
This puts us in the context of the AMO Server object.

PS > cd databases

This line navigates us into the Databases container of the server

PS > dir

This line displays the context of the current container, at this point you could also have also typed ”gci” or “ls” these along with “dir” are all aliases to the underlying Get-ChildItem cmdlet.

PS > cd “Adventure Works DW”

This line navigates to the “Adventure Works DW” database object

PS > cd cubes

This navigates to the cube collection of the database

PS > dir | ft ID, Name, State, LastProcessed

This line gets all the child items for the cube collection, pipes them to “ft” (which is an alias for format-table) and displays the ID, Name, State and LastProcessed properties.

You’ll notice that this command is different line 5. this is partly show a different method of listing an object’s properties and partly because I have setup a default format for databases, but I have not done one yet for cubes, so it currently dumps out all the properties for a cube which is not so easy to read.

Hopefully even from this simple example you can start to see some of the potential.

But that’s just the beginning, consider the following:

PS > cd sessions

We start by navigating to the sessions collection of the server. But wait a minute… the server object does not have a sessions collection… well, it does in powerSSAS. This is an extension that I have built into the provider and I plan to add more of them, connections, traces and jobs are a few obvious ones.

PS > dir

This lists the contents of the “sessions” container. Basically running a DISCOVER_SESSIONS command behind the scenes.

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 }

 This is where things start to get interesting. Here I am passing the objects returned from the “dir” into the “where-object” cmdlet (aliased as “where”) and then I am using a script block to examine each object looking for those with a LastCommandCpuTimeMs of greater than (that’s the -gt) 0 milliseconds.

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 } % { $_.kill() }

Now that I have found the sessions that had a LastCommandCpuTimeMs of more than 0 milliseconds, I want cancel those “expensive” sessions (hey, this is just an example - my laptop is not under a lot of heavy load <g>). So foreach of these objects (% is short for foreach-object) I am calling the Kill() method. (The session class and the associated kill method are powerSSAS types, not part of native AMO)

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 }

This command shows that those previous two sessions have in fact been canceled.

 

Now if you don’t want to create a drive and navigate like this I have built this functionality as cmdlets, in fact the provider is just calling into the cmdlets, so you could also have simply written the following:

PS > get-ASSession localhost

PS > clear-ASSession -serverName localhost -ID AFD040D3-34CD-4DEA-AADC-1421E593F70A

I want to improve the ways you can call the clear-ASSession cmdlet, but this is how it is working at present

Finally, scripts like the one I blogged about last year where you could process all dimensions that begin with the letter “P”, or any that have not been processed in the last 30 days are entirely possible.

So, give it a go and see if you like it. If you have any ideas, questions or requests you can contact me through this blog or you can post something in the powerSSAS discussion list or issue tracker.

There is a bit of a learning curve to figuring out how PowerShell works, but once it clicks you will be hooked too. I plan to continue work on enhancing powerSSAS and plan to share more about what you can do with it and some things I learned while building it.

The November CTP of SQL Server 2008 was released on the connect MSDN Downloads site at the end of last week and when you open up a cube in BIDS you will see a new tab for aggregations. I figured I might give you a bit a walk through what you can expect from this new tab.

What this tab does is to let you see all the aggregation designs that relate to a given measure group. It also lets you manage which partitions are using a particular aggregation design.

As with the other Analysis Services designers, the new AMO warnings are also surfaced in the aggregations tab which lets you see if there are any basic issues with your aggregations.

Such as a particular design no being referenced by any partitions

Or even an AggregationUsage property not being set properly.

Right-clicking on an aggregation design gives a number of options including

“Design New Aggregations”, “Usage Based Optimisation” and “Assign Aggregation designs”.

 

When you start up the “Design Aggregations” Wizard you can now easily select which aggregations a particular design is to apply to. 

One of the coolest additions to the aggregation design wizard is the following screen which shows you the aggregation usage setting for all the attributes and gives you the ability to change them.

The objects count screen appears largely unchanged.

As does the core Aggregation Options screen. 

At the end of the whole process you get to give your design a name. I would suggest that you might want to think about including an indication of the design options that you used. (eg “30 pcnt”, “upto 150Mb”, etc. ) You can see a property for the aggregation level when you view the properties of the aggregation design, which is where I thought information like this would be recorded, but that property did not appear to update (maybe this will change in a future build). But if you have a couple of different aggregation designs, this would let you see at a glance, which was which.

When you right click an Aggregation Design and choose the option to Assign Aggregations, you get the following dialog box. This gives you a very easy way to apply a given aggregation designs to a group of partitions.

While this feature does not let you manually build your own aggregations like the “Edit Aggregations” feature in BIDSHelper, I would argue that most people should not need to go to that extent.

Update 20 Nov 07: corrected the download location details

The BIDS Helper team are please to announce the release of v1.2.

You can get it here

And just to give you an idea of what BIDS Helper can offer you, here is a montage of some of the features.

The most exciting thing about this release is that it incorporates a number of SSIS features. I won’t go into the individual features too much, they are all documented on the web site. Below are links for all of the major new features.

Analysis Services

• Column Usage Reports
• Delete Unused Aggregations
• Deploy Aggregation Designs
• Dimension Optimization Report
• Measure Group Health Check
• Tri-State Perspectives
• Validate Aggregations

Integration Services

• Create Fixed Width Columns
• dtsConfig File Formatter
• Expression and Configuration Highlighter
• Expression List
• Smart Diff

 

One of the other subtle new feature to the core of BIDS Helper is the ability to enable and disable particular features through the Tools - Options menu. This is not so important for some of the “passive” features such as the Deploy MDX Script feature, that are only triggered when you click on a menu item. But some of the new features are triggered by the activation of certain windows and if any of these features are causing you issues or if you have any options you don’t use and don’t want to see, now you can simply switch them off.

Nick Barclay posted last week about the Australian launch events for Performance Point. These are free events so follow the links and register if you are in Melbourne (Nov 28) or Sydney (Nov 29).

However these events are a bit broader (see the agenda) than just a PerformancePoint launch, if you go to the registration site, you will notice that the event is actually titled as “Microsoft Business Intelligence Summit 2007” - it’s interesting to have to hear about something like this from Nick’s blog via one of the Microsoft US blogs and not from the local Microsoft Office.

Oh mighty Microsoft Marketing Machine - where art thou?  

Kevin posted an interesting comment to my last post about how to setup HTTP authentication on Windows Vista and that was that this sort of thing should be built into SSAS itself. While this is not a bad idea, I don’t know if it would be high on the list of things that I would like to see added to SSAS.

 I don’t have a written list as such, but here are few things off the top of my head that I would like to see in future versions. After I compiled this list I searched the connect site and added links to any related issues that I found. I encourage you to vote for any of the issues that you would like to see addressed. As Jamie Thomson has noted, the product teams do pay attention to the issues logged on the connect site. (So I should probably log my other suggestions on there too)

Here’s the list:

• A better way of dealing with calculations when there are sets in the WHERE clause.

• Connect: Make MDX calc work with sets

• Connect: Subcubes should set current context
• Connect: Sub-selects and the EXISTING operator
• Connect: CurrentTuple property

• Connect: SQL authentication for SSAS

 

What about you? What new features or changes would you like to see made to SSAS?

I got sent this question recently, but when I tried to reply the senders email address bounced, so I am posting the question and answer here.

Q: “I have a problem with aggregate function in cube measure.

My Measure is customer count and this fact have time dimension and location dimension.

this measure transfered monthly.

When aggreagated with time customer count aggregate function is MAX and if aggregated with location dimension aggrefate function is SUM.

What is the solution for this problem because I think SSAS cube measure can only have one aggregate function ? ”

 

A: A calculation like this is a little bit unusual, but I don’t think it is impossible. It just might not be the fastest measure as we are “fighting” against the standard aggregations.

There are probably other ways of doing this, but the first thing that came to my mind would be to do this using two measures, one normal sum measure and one calculated measure.

1) create a standard count aggregate (you can set this to visible = false if you do not want the users to see it)

2) create a calculated measure something like the following

Max(EXISTING [Date].[Date].[Date].members, Measures.[Customer Count])

Where measures.[Customer Count] is the measure from step 1) and [Date].[Date].[Date] is the lowest level date attribute.

This calculation basically says to get the aggregated amounts for all the lowest level date members that exist in relation with the currently selected date member and find the maximum from there.