…brew your own Business Intelligence

Load Testing SSAS with PowerShell

Load testing an SSAS database is one of those tasks that everyone should be doing but very VERY few people actually do… and I can only suspect the reason is because it’s hard.

Here are the general steps involved:

  1. Capture the query workload using an xEvents (basic exampleautorestart option)
  2. Shred the xEvents trace file(s) and load queries into the SQL table (example)
  3. Start (perfmon/xevent) traces to capture performance metrics (perfmon counters, xevents, collection) …or use an off-the-shelf monitoring solution.
  4. Generate query workload (from step 1)
  5. Analyze performance traces (from step 3) to find bottlenecks
  6. Make adjustment (only 1x per iteration) and repeat steps 3-6 until you run out of time (this should be a timeboxed activity) or find a good enough performance configuration

At first glance, everything looks pretty straight forward. However, I can assure you it will take more time (and patience) than you think.

One of the more time consuming pieces in this process is step 4 – setting up a solution that can generate a concurrent query workload. Bob Duffy (b | t) has written blogs and delivered presentations on the topic of Analysis Services load testing. However, when it comes to tooling (unless something changed recently) I believe he’s still using a custom .NET Visual Studio Test solution for the test harness. And unless you know .NET, you’re going to have a pretty difficult time getting setup… which is why I was so happy when, earlier this year, Chris Schmidt (b | t) wrote a post over on MSDN demonstrating a method for load testing an Analysis Services database using PowerShell.

This weekend I finally had some time to expand upon Chris’ code sample and add several new features… e.g. parameters (target server/database, level of concurrency, queries per batch, etc) and the ability to retrieve MDX (or DAX) queries from a SQL table. In my experience, it’s quite a bit easier to generate a table of MDX/DAX queries rather than a bunch of txt files with a query in each file.

Code

The following SQL script creates a database with a table and inserts a few sample MDX queries into the table (based on the AdventureWorks DW cube). Be sure to enable SQLCMD mode in SSMS before running the script.

/* Set Script Variables - Change these for your environment!!! */
:setvar DatabaseName "ASLoadTest"
:setvar file_path_data "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ASLoadTest_Data.mdf"
:setvar file_path_log "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ASLoadTest_Log.mdf"

PRINT N'Creating $(DatabaseName)...'
GO
USE [master];
GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
CREATE DATABASE $(DatabaseName)
    ON    PRIMARY ( NAME = N'$(DatabaseName)_Data', FILENAME = N'$(file_path_data)' , SIZE = 10MB , FILEGROWTH = 10MB)
    LOG ON  ( NAME = N'$(DatabaseName)_Log', FILENAME = N'$(file_path_log)' , SIZE = 10MB , FILEGROWTH = 10MB )
;
GO


USE $(DatabaseName);
GO

PRINT N'Creating dbo.MDXQuery...'
GO
IF OBJECT_ID('dbo.MDXQuery','U') IS NOT NULL
    DROP TABLE dbo.MDXQuery;
GO

CREATE TABLE [dbo].[MDXQuery]
(
     [Id]                INT IDENTITY(1,1)    NOT NULL 
    ,[QueryString]        NVARCHAR(max)        NOT NULL

    ,CONSTRAINT PK_dbo_MDXQuery PRIMARY KEY CLUSTERED ([Id])
)
;



PRINT N'Inserting Sample Queries into dbo.MDXQuery...'
GO
SET NOCOUNT ON;
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2014-11-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2014-12-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2014-12-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Customer].[Customer Geography].[Country].&[France],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[France],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Measures].[Internet Sales Amount])) WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2005-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2008-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2009-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2009-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2011-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2010-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2011-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2012-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2013-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2012-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2012-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2012-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[United States],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[United States],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], TopCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM [Adventure Works] WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT  FROM [Adventure Works] WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT {AddCalculatedMembers({[Customer].[Customer].[Customer].Members})} DIMENSION PROPERTIES MEMBER_TYPE ON COLUMNS FROM [Adventure Works] CELL PROPERTIES CELL_ORDINAL');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2005-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2014-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2005-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2015-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2008-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2009-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[Country].&[Germany],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
INSERT [dbo].[MDXQuery] ([QueryString]) VALUES (N'SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All Customers]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Customer].[Customer].[Customer].[Address],[Customer].[Customer].[Customer].[Birth Date],[Customer].[Customer].[Customer].[Commute Distance],[Customer].[Customer].[Customer].[Date of First Purchase],[Customer].[Customer].[Customer].[Education],[Customer].[Customer].[Customer].[Email Address],[Customer].[Customer].[Customer].[Gender],[Customer].[Customer].[Customer].[Home Owner],[Customer].[Customer].[Customer].[Marital Status],[Customer].[Customer].[Customer].[Number of Cars Owned],[Customer].[Customer].[Customer].[Number of Children At Home],[Customer].[Customer].[Customer].[Occupation],[Customer].[Customer].[Customer].[Phone],[Customer].[Customer].[Customer].[Postal Code],[Customer].[Customer].[Customer].[Total Children],[Customer].[Customer].[Customer].[Yearly Income] ON COLUMNS  FROM (SELECT Generate(Hierarchize({[Customer].[Customer].[All Customers]}) AS [XL_Filter_Set_0], BottomCount(Filter(Except(DrilldownLevel([XL_Filter_Set_0].Current AS [XL_Filter_HelperSet_0], , 0,INCLUDE_CALC_MEMBERS), [XL_Filter_HelperSet_0]), Not IsEmpty([Measures].[Internet Sales Amount])), 10, [Measures].[Internet Sales Amount])) ON COLUMNS  FROM (SELECT Filter([Date].[Date].Levels(1).AllMembers, ([Date].[Date].CurrentMember.MemberValue>=CDate("2009-01-01") AND [Date].[Date].CurrentMember.MemberValue<CDate("2011-01-01"))) ON COLUMNS  FROM [Adventure Works]) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount])) WHERE ([Customer].[Customer Geography].[All Customers],[Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS');
GO

Next we have the PowerShell code (warning: I’m still a PoSh n00b, so feel free to let me know what I could be doing better…for example does it make sense to parameterize workflows?)…

Function Invoke-ASLoadTestThread {
    param(
        [Parameter(Position=0,mandatory=$true)][string] $ssas_instance,
        [Parameter(Position=1,mandatory=$true)][string] $ssas_database,
        [Parameter(Position=2,mandatory=$true)][int] $concurrency_level,
        [Parameter(Position=3,mandatory=$true)][string] $sql_instance,
        [Parameter(Position=4,mandatory=$true)][string] $sql_database,
        [Parameter(Position=5,mandatory=$true)][int] $query_batch_size,
        [Parameter(Position=6,mandatory=$true)][int] $thread
    )

    # Retrieve queries for current thread
    $sql_query = "SELECT q.QueryString FROM dbo.MDXQuery q WHERE q.Id % ${concurrency_level} = ${thread};"
    $lst_query = @( Invoke-SQLCmd -ServerInstance $sql_instance -Database $sql_database -Query $sql_query) | Select-Object -ExpandProperty QueryString
        
    # loops through list of queries and executes them in batches
    $loop_query = 1
    $loop_batch = 1
    foreach ($ssas_query in $lst_query) {

        if ( $loop_query -eq 1 ) {
            $ssas_query_batch = $ssas_query
                
            Write-Debug("Thread {0} | Batch {2} | Query {1}" -f $thread.ToString(), $loop_query.ToString(), $loop_batch.ToString())

        } else {
            $ssas_query_batch = $ssas_query_batch + "`n; `nGO `n" + $ssas_query
                
            Write-Debug("Thread {0} | Batch {2} | Query {1}" -f $thread.ToString(), $loop_query.ToString(), $loop_batch.ToString())                
                
            # execute batch
            if ( $loop_query -eq 3 ) {
                $ssas_query_batch = $ssas_query_batch + "`n; `nGO `n"

                Invoke-ASCmd `
                    -Server $ssas_instance `
                    -Database $ssas_database `
                    -Query $ssas_query_batch `
                    | Out-Null
                    
                # reset query counter
                $loop_query = 0
                    
                # increment batch counter
                $loop_batch++                    
            }
                
        }

        # increment query counter
        $loop_query++
    }

}

Workflow Start-ASLoadTest-Workflow {
    <#
    .SYNOPSIS
        Generates a concurrent query workload against a SSAS database.

        code by Chris Schmidt @ MSFT
        https://blogs.msdn.microsoft.com/chriss/2016/06/load-testing-ssas-with-powershell/

        based on workflow code from Jamie Thomson’s post below:
        http://sqlblog.com/blogs/jamie_thomson/archive/2014/12/09/parallel-foreach-loops-one-reason-to-use-powershell-workflow-instead-of-ssis.aspx

    .DESCRIPTION
        Powershell-based harness for generating a parallel query workload against the 
        Analysis Services database specified by the $ssas_instance/$ssas_database
        parameters.

        AS queries must be stored in a table called dbo.MDXQuery with the following schema:

            CREATE TABLE [dbo].[MDXQuery] (
                 [Id]            INT IDENTITY(1,1)    NOT NULL 
                ,[QueryString]    NVARCHAR(max)        NOT NULL
            )

    .PARAMETER ssas_instance
        specifies the target ssas instance
        
    .PARAMETER ssas_database
        specifies the target ssas database
        
    .PARAMETER concurrency_level
        specifies the number of concurrent threads across which 
        the workload should be spread
        
    .PARAMETER sql_instance
        specifies the target sql instance from which to retrieve
        the SSAS queries
        
    .PARAMETER sql_database
        specifies the target sql database from which to retrieve
        the SSAS queries
        
    .PARAMETER query_batch_size
        specifies the number of queries to execute on the same connection

    .INPUTS
        <n/a>

    .OUTPUTS
        <n/a>

    .EXAMPLE
        Start-ASLoadTest-Workflow -ssas_instance "localhost" -ssas_database "Adventure Works DW 2016" -concurrency_level 4 -sql_instance "localhost" -sql_database ASLoadTest -query_batch_size 3

    .LINK
        <n/a>

    .NOTES
        <n/a>

    #>
    param(
        [Parameter(Position=0,mandatory=$true)][string] $ssas_instance,
        [Parameter(Position=1,mandatory=$true)][string] $ssas_database,
        [Parameter(Position=2,mandatory=$true)][int] $concurrency_level,
        [Parameter(Position=3,mandatory=$true)][string] $sql_instance,
        [Parameter(Position=4,mandatory=$true)][string] $sql_database,
        [Parameter(Position=5,mandatory=$true)][int] $query_batch_size
    )

    $thread_array = 0..($concurrency_level - 1)

    foreach -parallel ($thread in $thread_array) {
        Invoke-ASLoadTestThread `
            -ssas_instance $ssas_instance `
            -ssas_database $ssas_database `
            -concurrency_level $concurrency_level `
            -sql_instance $sql_instance `
            -sql_database $sql_database `
            -query_batch_size $query_batch_size `
            -thread $thread
    }

}

After loading those into the PS session, you can run the following snippet to generate the query workload… (don’t forget to change the parameter values to match your setup)…

# parmater values (change for your environment!!!)
$ssas_instance = "localhost\ssas_md"
$ssas_database = "AdventureWorksDW2016"
$concurrency_level = 3
$sql_instance = "localhost"
$sql_database = "ASLoadTest"
$query_batch_size = 4

# clear cache
Invoke-ASCmd -Server $ssas_instance -Database $ssas_database -Query "CALL ASSP.ClearCache();" | Out-Null

# start workload
$start_dt = get-date
Write-Output("================================================================================") 
Write-Output("Running Start-ASLoadTest-Workflow")
Write-Output("================================================================================")

Start-ASLoadTest-Workflow `
    -ssas_instance $ssas_instance `
    -ssas_database $ssas_database `
    -concurrency_level $concurrency_level `
    -sql_instance $sql_instance `
    -sql_database $sql_database `
    -query_batch_size $query_batch_size

$end_dt = get-date
Write-Output("")
Write-Output(" Test Parameters:")
Write-Output("    > ssas_instance: {0}" -f $ssas_instance)
Write-Output("    > ssas_database: {0}" -f $ssas_database)
Write-Output("    > concurrency_level: {0}" -f $concurrency_level)
Write-Output("    > sql_instance: {0}" -f $sql_instance)
Write-Output("    > sql_database: {0}" -f $sql_database)
Write-Output("    > query_batch_size: {0}" -f $query_batch_size)
Write-Output("")
Write-Output(" Test Results:")
Write-Output("    > Start Time: {0}" -f ($start_dt).ToString())
Write-Output("    > End Time: {0}" -f ($end_dt).ToString())
Write-Output("    > Duration (sec): {0}" -f (New-TimeSpan –Start $start_dt –End $end_dt).Seconds.ToString())
Write-Output("================================================================================")

Here’s the output from one of my lab systems…

image

If you add the –Debug argument to the Start-ASLoadTest-Workflow call, like this…

Start-ASLoadTest-Workflow `
    -ssas_instance $ssas_instance `
    -ssas_database $ssas_database `
    -concurrency_level $concurrency_level `
    -sql_instance $sql_instance `
    -sql_database $sql_database `
    -query_batch_size $query_batch_size `
    -Debug 

…you get detailed output of the query batches being executed on each thread…

image

Note: depending on your environment (mainly the version of PowerShell) you might be limited in the level of concurrency. See here, here, here. Long story short, if you’re on Windows Server 2012 w/ PowerShell v4, you should be fine 🙂

Wrapping up

Analysis Services load testing is hard. Hopefully this sample PowerShell code makes it a bit more bearable.

 

Additional References:

7 thoughts on “Load Testing SSAS with PowerShell

  1. Ammar says:

    Hi Bill, your script works great! 🙂
    I have one question though, when i am running it with concurrency level e.g. 100, i see that it execute/connect more than 5 sessions at a time. Just wondering how can i make sure that concurrent threads can be 100 for load test.

    1. Bill says:

      Hi Ammar – what values are being passed in for $concurrency_level and $query_batch_size ? and how many queries total are there in the source db-table?

      1. Ammar says:

        I only have one long query there. So keeping $query_batch_size 1 and $concurrency_level 1000 (to test system under stress if 1000 queries are concurrently run on the server).

        1. Bill says:

          can you send me a screenshot of the debug output?

          email: anton (at) opifexsolutions.com

  2. Ammar says:

    correction: *it does not execute/connect more than 5 sessions at a time.

  3. biatmn says:

    Invoke-ASCmd truncates queries longer than 8000 characters. Unlike invoke-sqlcmd it does not have MaxCharLength property to accommodate the lengthy queries

Leave a Reply