SSAS performance tuning (Case Study) Part 2

In the previous post, we have seen that only few smart moves/changes can make cube processing time far less than the current cube processing time. In this post, we will explore the way to identify the performance bottleneck and different ways to resolve it.


In one of my project, cube processing was taking 8 hours where we were running 2 measure groups in parallel.  Each measure group were having daily load partitions. Here are the initial stats:

Cube Name	Measure Group	Partition	Average Processing time
Cube1	MG1	Pn	7-8 hours
Cube1	MG2	Pn	4-5 Hours

Interview Questions for SQL Server Analysis Services (SSAS)

I have received lot of mails from my readers for SSAS interview questions. This gave me idea to list down all the analysis services question here on my blog so everyone can be benefited. This question set is applicable for SQL server analysis service 2005 ( SSAS 2005) and SQL server analysis services 2008 (SSAS 2008). This post divides interview questions into different categories for your convenience:

SSAS Tutorials (Complete list of Analysis Services Tutorials in blog)

Well in this post, i am listing all my analysis services tutorial posts details so if you are starting to learn SSAS or you are SSAS developer then it will be easy to find useful posts. I will keep updating this post with details of new tutorial posts.

Interview Questions for MDX (MDX Interviews)

I have written previous article on interview questions on SSAS. It has covered questions related to SSAS development, architecture and performance aspect except MDX so i am covering MDX interview questions in this post. 

Here is MDX questions list. I am dividing complete list in three categories general concept, MDX queries and Performance:

Master data Services: Excel add-in explored

Master data services is Master data management tool by Microsoft. In latest release SQL Server 2012(RC0), there are few new features added into it. Here is the list:

1)      Excel add in usability improved
2)      Many to Many mapping improved
3)      Collection UI updated, Weights added
4)      Security simplified
5)      Codes automatically
6)      Model deployment performance improved
7)      SharePoint integration introduced

I am going to cover each topic in my posts. Let’s start today by “Excel add in usability improved”.

Before we understand what has improved, let’s look what was there in SQL Server 2008 MDS.

1)      Create Entity: Entity can be created in MDS using Excel add-in. Create data set in excel and then use “Create Entity” from Excel ribbon. If you want to create MDS entity from SQL Server DB table, make connection to DB, import the data into excel and create entity using the same.

2) Add/Update data into existing entities: Excel add-in provide you way to load new data as well as well update the existing data. For it, you need to make connection to MDS and then load the existing data. Now once data is loaded, make changes and use “Publish” functionality in Excel ribbon to load the same into MDS.

3) Combine Data: Excel Add-in can be used to combine data from different source. You can use Data quality component to know duplicate data from other source. For this, load data from MDS entity and new data into separate spreadsheet. Use “Combine Data” functionality present in ribbon to start duplicate analysis using DQ component. Once you specify parameter on DQ wizard, it will give you score(similarity) of new source data rows. Based on it, you can add new record or delete as it may be duplicate.

4) Apply Rules: business rules can be applied on MDS data.

5) Save & Send MDS Queries: MDS data can be filtered/altered/added in excel and then it can be saved in query format. It can be send to another person for validation/verification or making corrections.

Here is list of new changes in Master data services 2012 from MSDN,

The Master Data Services Add-in for Excel has been improved for better usability.

1) In columns that display domain-based attributes, both the name and code are now automatically displayed.
2) The workflow for loading data has changed slightly. In order to load data, you must now open a new worksheet.
3) Changes have also been made to increase the visible workspace. The Master Data Explorer pane now closes when you load data, and information about the active worksheet has been combined into a single row at the top of the sheet.
4) It is now easier to share shortcut query files, which contain information about the server, the model, version, entity, and any applied filters. A new toolbar button allows you to send the shortcut query file to another user via Microsoft Outlook.