tim laqua dot com Thoughts and Code from Tim Laqua

23Jan/132

Hiding SSAS Attributes With Powershell

The challenge here was that we have a cube with some great data as well as some low level detail (customer detail, think PII) that we want to expose to a larger audience. The problem is that the low level detail is not something that the new audience needs (or in some cases, is allowed) to have access to. The obvious answer here is Dimension security - why not use that? Two reasons. First, Dimension security is slow. Second, even if it wasn't slow, to hide all the data in many (30+) attributes is tedious to setup, and when new attributes were added we would have to make sure and disable access to those as well. To be clear, we're not just hiding attributes here, we're creating an entire copy of the existing SSAS database and hiding attributes in the copy.

# Import Required Libraries
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
 
# Connect to server
$server = New-Object Microsoft.AnalysisServices.Server
$server.Connect('localhost')
 
# Make a metadata copy of the existing cube
$newdb = $server.Databases.GetByName('POS Data').Clone()
 
# Change ID and Name of SSAS DB
$newdb.Name = 'POS Data - Limited'
$newdb.ID = 'POS Data - Limited'
 
# Drop Existing SSAS DB if it exists
$server.Databases['POS Data - Limited'].Drop()
 
# Add the new copy to the server
$server.Databases.Add($newdb)
 
# Sync our copy of the new database with the server's copy
$newdb.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
 
# Grab the cube we want to work with from the new database
$cube = $newdb.Cubes.GetByName('POS Data')
 
# Hide the Location Dimension
$cube.Dimensions.GetByName('Location').Visible = $false
 
# Hide all attributes and hierarchies in the Customer dimension
$cube.Dimensions.GetByName('Customer').Attributes |  %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}
$cube.Dimensions.GetByName('Customer').Hierarchies | %{$_.Visible=$false; $_.Enabled=$false;}
 
# Enable the key attribute in the customer dimension - it won't work if the key isn't enabled
$cube.Dimensions.GetByName('Customer').Attributes['Dim Customer'].AttributeHierarchyEnabled=$true
 
# Enable the Market attribute in the customer dimension
$cube.Dimensions.GetByName('Customer').Attributes['Market'] | %{$_.AttributeHierarchyEnabled=$true; $_.AttributeHierarchyVisible=$true;}
 
# Hide the Location Database attribute in the Product dimension
$cube.Dimensions.GetByName('Product').Attributes['Location Database'] | %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}
 
# Add a new member to the role granting Read Only permissions in the cube
$newMember = new-object Microsoft.AnalysisServices.RoleMember("domain\tlaqua")
$newdb.Roles['Role 1'].Members.Add($newMember)
 
# Push our updates to the server
$newdb.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
 
# Process the new database
$newdb.Process([Microsoft.AnalysisServices.ProcessType]::ProcessFull)
 
# Disconnect from the server
$server.Disconnect()

This approach has two notable downfalls. First, you have to think up a different database name because the original db with the low level detail still exists on the server. Second, you have to ProcessFull the clone that you made. It doesn't close the data, it clones the metadata. All in all, works great for us, this particular cube is only processed once a week and the 20 minutes we lose processing data in to the clone is more than acceptable (looks really clean to the users as well).

3Feb/122

SSAS Cache Isn’t Making Cents

I stole the pun from my Business Analyst, Mr. John Seiler 😉 Now on to my issue - when SSAS caches the value for my [Actual] measure, it seems to do so based on the results of the first query that requests that coordinate. In this particular cube, there's bunches of tiny fractions and depending on how you slice it, it aggregates a little different. This is a fun problem in itself, but the part that drives me (and the Finance department) crazy is that if you go and slice on something OTHER than that first query that created the cache, the values they see don't always add up to the "Grand Total" in Excel - aka. "All"

These are the queries used for this test:

Query A

SELECT 
	{Actual} ON 0
FROM [GL] 
CELL PROPERTIES VALUE

Query B

SELECT 
	{Actual} ON 0,
	NON EMPTY Hierarchize({DrilldownLevel({[Account].[Accounts].[All]},,,INCLUDE_CALC_MEMBERS)}) ON 1
FROM [GL] 
CELL PROPERTIES VALUE

Query C

SELECT 
	{Actual} ON 0,
	NON EMPTY Hierarchize({DrilldownLevel({[Date].[Fiscal].[All]},,,INCLUDE_CALC_MEMBERS)}) ON 1
FROM [GL] 
CELL PROPERTIES VALUE
GO

Results (Cache was cleared with ClearCache on the entire Database before each Series)

Series 1 Series 2 Series 3

So basically the Grand Total of this GL cube is a random number based on whoever sneaks the first query in after the cache is cleared (processing, sync, etc).

And for all of you that think the MDX script breaks everything everywhere - I did comment out my entire MDX script before running these tests.

18Apr/110

Parallel Cube Processor 1.0

Parallel Cube Processor (PCP) is an application designed to process multiple Analysis Services databases at the same time. I originally wrote this application with the assumption that it would be blazing fast and essentially warp space and time. As it turns out, SSAS appears to be going as fast as possible at all times, so giving it more stuff to do doesn't really make the whole shebang faster - just different. Play around with it, watch some performance counters - it's an interesting conundrum. The application can handle parallelism using two methods.

The first method is XMLAParallel - in this mode, the application constructs an XMLA command to process the specified number of databases inside of a Parallel element. Only one XMLA command is issued at a time so the applicatoin will not submit another command until the longest processing job completes in each batch.

Tagged as: , , Continue reading
15Apr/112

Dealing With Long Running SSAS Queries using Powershell

So, your Analysis Services instance keeps eating all the memory on your server despite proper memory configuration? This happens when queries get too big for their britches - SSAS memory limits are basically soft limits. Whatever an active query requires in terms of resources, SSAS will attempt to provide it. Even if it has to page the entire operating system out to disk to do it. You can mitigate this with good cube design, better aggregations, user training, etc - but it's bound to happen. When your cube gets big enough, one of your users will blow the entire server up.

6Nov/101

Analysis Services Writeback – Working with Weight Expressions

While writeback has been around for a while, it didn't really get easy to do until Excel 2010 was released because wrapping a UI around it was cumbersome. Now that we have a simple writeback UI via the What-If analysis dialogs in Excel, it's pry worth poking around at how to get things to allocate the way you want.

Here's our starting point in the Adventure Works cube (note, I commented out all the scope statements in the MDX script that distribute quarterly quotas/targets to months - in AW, quotas only exist in the data warehouse at the calendar quarter level):

Let's start with something simple - Equal Allocation:

6Apr/102

Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter

Ok, it doesn't hang every time you try to edit an OLAP cube filter, but sometimes - it appears to. In reality, I've never seen it permanently hang - just kind of go away for a while. Here's the basic symptom that the business will report to you:

"Excel freezes (or hangs) when I try to change this filter"

Most of us have seen this at one point or another and shrugged it off as a busy time or processing is going on or there are cats clogging up the tubes, etc. Tonight, I finally decided to figure out what's causing it.

12Mar/0921

SSAS Cube Action: Cells Target type, URL Action type Example

Originally, we were charged with figuring out how to display SSAS cube measure descriptions via ToolTip in Excel 2007. If that's your plan, forget it - after some reading up on the interwebs, it appears that Excel doesn't even request the Description property. Additionally, if you want to add a description to Calculated Members, you have to hack it in (yuck).

So we went with a simple, albeit relatively crude (but effective), alternative - implementing a URL action for Cells so users can easily link out to a definition of the measure they're looking at.

Create a new action in your cube (Open up the cube definition, Actions tab) and configure similar to this:

Name: View Member Definition
 
Action Target
    Target Type: Cells
    Target object: All cells
 
Action Content
    Type: URL
    Action expression: "http://i.domain.com/doc/Defs.aspx#" 
                       + [Measures].CurrentMember.Name
 
Additional Properties
    Invocation: Interactive
    Description: View Member Definition
    "View Definition Of " + [Measures].CurrentMember.Name + "..."
    Caption is MDX: True

When you're finished, it should look something like this:
ssascubecellsurlaction

For a possible way to implement the aforementioned Definitions.aspx, check out http://timlaqua.com/2009/03/scrolling-to-and-highlighting-anchor-target-via-javascript/ - which describes an early endpoint we used for this project.

12Mar/090

Scrolling to and Highlighting Anchor Target via JavaScript

I implemented a SSAS cube action to link to a SharePoint page (URL Action) with the name of the measure contained in the cell the user fired the action from. The theory here is to have a page that contains a list of definitions for all measures (both real and calculated) in the cube. As we were flushing out this implementation, it was suggested that the page should scroll to the specified measure and highlight it in some way. The implementation of this using the CSS :target pseudo class is pretty straightforward - however we're a Microsoft shop and we absolutely have to support Internet Explorer 7 and Internet Explorer 8, so that's out.

23Feb/090

Wierd Slice – Revenue for the Most Recent Hour in the Cube Across the Last 14 Days Sliced by Week

Well - there's two parts to this - one is to figure out the most recent hour in the cube that has data. Let's face it, it finishes as soon as it can - but that time may vary now and then. Then, for this particular report, we wanted to see the last seven days on one series and the previous seven days on another series. To do this, we just slice by two calculated measures. But this creates an odd dateset where the This Week column will have no data for the "Date Time" columns that actually belong to the Previous Week.

This isn't really a problem as in SSRS, you can simply define the Category Group for the Series in your chart (ya, I didn't mention that yet - this is all for a chart) as DatePart("w", Fields!DateTime) - which will group everything by the number of the given weekday and then you can just have your "This Week" and "Prevoius Week" series.

WITH 
  MEMBER [Measures].[This Week] AS 
    Aggregate
    ([Time].[Calendar Date].LastChild:[Time].[Calendar Date].LastChild.Lag(6)
     ,[Measures].[Revenue])
 
  MEMBER [Measures].[Previous Week] AS 
     Aggregate([Time].[Calendar Date].LastChild.Lag(7)
     :
     [Time].[Calendar Date].LastChild.Lag(13)
     ,[Measures].[Revenue])
SELECT 
  NON EMPTY 
    {[Measures].[Previous Week], [Measures].[This Week]} ON 0
 ,NON EMPTY 
    { 
        [Time].[Date Time].[Date Time].MEMBERS 
        * 
        [Time].[24 Hour].[24 Hour].ALLMEMBERS
    } ON 1
FROM 
(
  SELECT 
    Tail(
         Filter(
               [Time].[24 Hour].[24 Hour].MEMBERS
               ,Sum([Time].[Calendar Date].LastChild,[Measures].[Revenue]) > 0
         ), 1
    ) ON 0
  FROM 
  (SELECT 
      {
            [Time].[Calendar Date].LastChild
            :
            [Time].[Calendar Date].LastChild.Lag(13)
      } ON 0
    FROM [Cube]
  )
)

And to top it all off - after seeing the chart for a while, nobody liked it (after all, it's a relatively abstract view of revenue) and we dumped it. 😉

5Nov/080

How to add a calculated measure (calculated field) to an Excel 2007 PivotTable with a SSAS data source

As it turns out, you can do it programatically as explained by Allan Folting here:
Microsoft Excel: Common Questions Around Excel 2007 OLAP PivotTables:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

And the parts that I have to keep looking up (I use my blog as a notebook for things I don't want to forget ;-):

Sub AddCalculatedMeasure() 
	Dim pvt As PivotTable
	Dim strName As String
	Dim strFormula As String 
 
	Set pvt = Sheet1.PivotTables("PivotTable1")
	strName = "[Measures].[Internet Sales Amount 25 %]"
	strFormula = "[Measures].[Internet Sales Amount]*1.25"
	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember 
End Sub 
 
Sub AddCalculatedMember() 
	Dim pvt As PivotTable
	Dim strName As String
	Dim strFormula As String 
 
	Set pvt = Sheet1.PivotTables("PivotTable1")
	strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]"
	strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25"
	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
	pvt.ViewCalculatedMembers = True 
End Sub 
 
Sub AddNamedSet() 
	Dim pvt As PivotTable
	Dim strName As String
	Dim strFormula As String
	Dim cbf As CubeField 
 
	Set pvt = Sheet1.PivotTables("PivotTable1")
	strName = "[My Mountain Bikes]"
	strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
	Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes") 
End Sub

He also mentions that you can expose these members to Excel Services 2007 by creating the new objects and then removing the VBA code - very useful article.