When I need to display data from a cube on a web page, I usually store the MDX query in a text file and make this file part of a resource file. This allows me to store the MDX in a readable format. In the image below, I have two files in the App_GlobalResources folder. The first [...]
SSAS
Powershell – Processing Production SSAS Databases
In an earlier post I demonstrated a method I use to update development SSAS databases using powershell. While this is okay for development servers it doesn’t really work with production boxes containing millions of rows of data. Generally when I refresh a cube, the only data that will have changed relates to the most recent month or [...]
SSAS Partitions & Slices
Here is a quick run through of the steps to partiton a measure group in SSAS. Within the cube designer click the partitions tab. Right click on the Fact Sales partition and select properties. In the Properties window find the Source property and click the ellipse button to the right of the value. This will open the [...]
PowerShell – Processing SSAS databases
Here is a simple PowerShell script that I use to refresh all the analysis server databases on my development machine. clear host [void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) $server = new-object Microsoft.AnalysisServices.Server $server.connect(“.”) $server.databases | foreach-object { $_.dimensions | foreach-object { $_.Process(“ProcessFull”) $_|select name,lastprocessed } $_.cubes | foreach-object { $_.Process(“ProcessFull”) $_|select name,lastprocessed } } You don’t have to process the [...]
HideMemberIf
Generally, when I want to create an unbalanced hierarchy I will use a parent-child relationship. The image below shows a basic parent-child hierarchy and the data within the dimension. Sometimes a parent-child relationship is not an option. In SSAS a dimension will only support a single parent-child hierarchy and I have come across a few situations where [...]
ParallelPeriod & PeriodsToDate
The query below returns the reseller sales for Jan 2007 and Jan 2008. SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS, { [Date].[Calendar].[Month].[January 2007], [Date].[Calendar].[Month].[January 2008] } ON ROWS FROM [Adventure Works] To determine the sales amount for the same month last year, use the ParallelPeriod function. ParallelPeriod( [Date].[Calendar].[Calandar Year] — Level, 1, — Numeric [...]
Writeback Partitions
A few months ago I was asked to change an existing data cube and add budget and forecast data. This information was stored on Excel spreadsheets and I was asked to read data from the files on a periodic basis and update the cube with the latest budget/forecast information. I saw a couple of problems with [...]
MDX Member Key
When writing MDX queries that will be consumed programmatically it is useful to have the identity keys of each member. Take the example below, you could program a datatable or cellset and use the product name to select/identify data however this have problems. For one the product name could change or there may be more than one [...]
Current Date, StrToMember & StrToSet
The majority of MDX queries I write return figures for the previous quarter or the last six weeks (or some variation that involves starting from today and going back in time so many units). In order to create these reports I first need to get the current date and then work that into the query. The MDX [...]
