SQL Server Analysis Services (SSAS) multidimensional cube: How to create a role playing dimension?

Standard

Summary:

I was recently asked by a SSAS developer about how to set up a role-playing dimension so I did a brief demo to show how to set it up. In this post, I am document the steps to help you get started:

What is role-playing dimension?

What if you want to see sales data by Order Date as well as Shipped Date? You can one date dimension that is used more than once for seeing sales data by order date and shipped date. In this case, date dimension becomes a Role Playing Dimension.

Steps:

1. Open SQL Server Data Tools and SSAS project that you are working on.

2. Go to Data Source Views from the solution explorer.

3. Make sure that the relationships are specified between dimension (e.g. date dimension) and Fact Table (e.g. Fact Sales). Example: If you’ve OrderDateSK and ShippedDateSK in your fact table, then establish a relationship between the both columns with the Primary Key of the Date dimension.

SSAS Data Source View Role playing dimension

Note: If you do not have the relationships specified in the data source views, there’s an alternative to go to dimension usage for a cube and setup role playing dimensions. since this is targeted to get you started, I am not covering the steps for that.

4. Now go to the Dimensions section in the solution explorer and add a Dimension that uses the existing data source view from step #3.

5. switch to Cubes.

Now, if you don’t have a cube created yet then you can use the cube wizard if you want and as long as you have the relationships specified at DSV’s, it should create a role-playing dimension.

Alternatively, you can also create a new dimension. In the Design pane, lower left section, you’ll see dimensions.

Add Dimensions Cube SSAS

select your dimension that you created in step #4 that’s a role-playing dimension from the list and click on OK. And it creates a role-playing dimension for you! So all you had to do was to make sure the relationships are created between the fact and (role-playing) dimension table and SSAS does the rest of the work for you

SSAS Role Playing Dimension

 

Note: for the demo I didin’t rename the dimension attributes but you do all that good stuff too when you are building the cube for users!

Conclusion:

In this post you got started on how to add a role-playing dimension in a SSAS cube.

How to sort measure values in an excel 2010 based ad-hoc report using SSAS cubes?

Standard

I helped a user today to sort measure values in his excel based ad-hoc report using SSAS cubes & so I’m posting the tips here for note-keeping. There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

sorting Excel ad hoc report ssas cube#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

sorting data excel analysis services cube

Conclusion:

In this post, I shared two ways you can sort the measure values in an excel based ad-hoc report using SSAS cubes. Do you allow excel based ad-hoc reporting over SSAS cubes in your organization?

 

How to add calculated measures to Pivot Tables using OLAP Pivot Table Extensions add-in?

Standard

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT

Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

excel version 32 bit or 64 bit

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Excel Pivot Table Extensions

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Excel Simple Pivot Table OLAPNow let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

OLAP PIVOT TABLE EXCEL EXTENTSIONS

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

Simple Formula OLAP excel calculated memberYou can now see that the calculated measure Difference got added to the Pivot Table!

Excel olap pivot table with calculated memberNote

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

That’s about it for this post! Your feedback is most welcome!

 

How to add additional Columns to an Existing Table in a Power Pivot Model?

Standard

Problem:

There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?

Solution:

Here are the steps:

1. Open Power Pivot Model.

2. Go to Design Tab.

3. Click on Table Properties:

Power Pivot Table Properties

4. From here, You can add additional columns:

Add Columns Table Power Pivot5. click on save and that should add the column to your model:

Power Pivot Edit Connections TableFAQ:

#1: I can’t see what you see inside Design Tab. What do I do?

Make sure you have the latest Power Pivot version. You can read this: http://parasdoshi.com/2013/06/04/excel-2010-how-to-check-the-installed-version-of-powerpivot-and-whats-the-latest-version/

#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:

you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!

Related Posts:
How to add a column in an imported Table while developing SQL Server 2012 Analysis services Tabular Model

How to Parametrize a SSRS report where data source is Analysis services cube?

Standard

In this blog post, we’ll see steps to Parametrize a SQL Server Reporting Services report where data source is Analysis services (SSAS) cube:

Step 1: Let’s say you have reached to a point with your SSRS report where you’ve configured your data source, data sets and data fields that you want to use for your report. For the purpose of this blog post, I’ll be starting with a SSRS report that shows Sales VS country names:

SQL Server reporting services 1

Step 2: Now, let’s say the requirement are such that you want to parametrize the report by a data field in the Analysis services cube: continents

Step 3: Switch to Design View. Now navigate to query designer: Select your Data-set > Right Click > DataSet Properties > Query > Query Designer

ssrs query designer SQLStep 4: Drag the field to the filter area. For the purpose of this blog post, I am going to select Continent Name and add it to Filter area.

To add a field to filter area, there are two options:

#1: Select the field > Right click > Add to Filter

#2: Select the field > use your mouse to drag it to filter area

Step 5: Once you’ve added your desired field to the filter area, we’ll have to add it as parameter.

Now chances are that you are not seeing the parameter check box for this field because the dialog box is minimized. You can either maximize the dialog box or scroll to the right side of the filter area.

Once you see it, check it > click ok

parameter query sql server reporting

Step 6: Once you’re back on Design View. Try “preview” report. you should be able to see the option to select parameter value before the report gets populated with data:

parameter in sql server reporting ssas

I selected Europe and then clicked on view report:

parameter in sql server reporting ssas 2

Step 7: One last thing, Let me also point out how you can change the properties of the parameters.

Go Back to design view > from the report data pane > Expand parameters folders > select the parameter > Parameter Properties

parameter properties report data ssrs ssas

I’ll leave you with exploring what you can do with parameter properties! And with that I conclude this blog post, Your comments are very welcome!

This was a beginner’s level post, I’ll encourage you to follow up by watching three videos here: http://sqlserverbiblog.wordpress.com/2013/12/03/reporting-services-mdx-queries-video-tutorials/

SQL Server Reporting Services: Month Names sorting Issue

Standard

Problem:

I was developing a SQL Server Reporting Services report from data that was coming from Analysis Services cube. And when I created the report having Month Names, it was not sorting it correctly.

It was:

April, August, February, Jan….

But It should have been:

Jan, Feb, Mar…

because I had correct sorting settings in the cube. So what was wrong? Or what can we fix?

Solution:

Turns out, the solution was pretty simple. Here are the steps:

1) Select the chart.

2) Select the Month Name category group

3) Go to Properties

4) Go to Sorting section

And delete the sorting property that sorts the Month Names by A to Z. seems this overrides over sorting properties that’s in the cube.

sorting sql server reporting services month names

5) After deleting the sorting property in SSRS fixs the issue.

Please preview the report to see if the issue has been resolved for you.

Getting Started: Implementing Dynamic Security with row filters in Tabular Models

Standard

In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.

Scenario:

We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:

tabular models countries total margin profit

What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom

Solution:

1) Open Tabular Model in SSDT (SQL Server Data Tools)

2) Toolbar > Model > Roles

tabular models BISM roles

3)  Role Manager > NEW > change Name to Europe and Permissions to Read

4) Under the Row Filters, for the Geography Table, enter the following code:

=[Country Region Name]=”France” ||  [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”

How to edit code for your scenario? change the [country region name] to your column(s) and also change the values

role tabular dax filter ssdt code

5) Click OK

6) Now let’s test it!

7) Toolbar > Model > Analyze in Excel

8) Select the role Europe

dynamic row filter in tabular models9) Click ok.

10) From Pivot Table, Select Margin & Countries:

DAX tabular models dynamic row filters based on location

11) As you can see, since the role Europe was selected for testing purpose in step 8 –  ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!

I hope this tutorial helps you get started on implementing dynamic security in Tabular models.

Resource:

WhitePaper: Securing the Tabular BI Semantic Model

 

How to enable Data Mining in EXCEL powered by SQL Server Analysis Services?

Standard

In this blog post, we’ll see a step by step guide to install Data Mining Excel add-in in excel that would help you carry out data mining in Excel.For the purpose of this blog-post, I am going to install add-in for Excel 2010 and SQL Server Analysis services server 2012.

It’s important to note that this add-in acts as an interface between Excel and “SQL Server Analysis services (SSAS)”. So SSAS is the place where data mining wizardry takes place and EXCEL acts as a client.

Here are the steps:

1) Search for “Microsoft SQL Server 2012 Data Mining add-ins for Microsoft office 2010”. AND Download the add-in.

 

2) Run the Setup > To go forward you have to accept license agreement.

 

3) Select “Data Mining for Excel” as a feature that’s supposed to be installed:

feature selection tab of excel data mining add-in

 

4) Next > Install. 

After it is successfully installed, open Excel.

 

5) Now in EXCEL 2010, can you see the Data Mining Tab?

data mining in excel tool

 

6) There’s more. You’re not done yet.

Did you see a “Getting started” dialog box too. If not, click on “getting started” from the Start Menu:

Getting started data mining

 

7) Now, if you have SSAS – then select “Use an Existing Instance of Microsoft SQL Server 2008 or later analysis server that I administer ”

But if you do NOT have SSAS, then you could consider installing the Evaluation copy.

For this blog-post, we’ll go with the first option:

 

8) Now, can you see the instructions? They read like this:

You have chosen to download the Microsoft SQL Server 2012 Enterprise Evaluation Edition.

The Microsoft SQL Server 2012 Enterprise Evaluation Edition is fully-functional 180-day trial software.

Microsoft SQL Server 2012 is a data management and analysis solution that enables companies of all sizes to gain greater insight from their business information, apply that insight to enhance their decision-making process, and achieve faster business results for a competitive advantage.

You now have 2 more steps to complete to start using the Microsoft SQL Server 2012 Data Mining Add-ins for Office.

Step 1. Download the Microsoft SQL Server 2012 Enterprise Evaluation Edition
The typical evaluation installation of SQL Server 2012 does not include the Analysis Services part of the product by default. When installing Microsoft SQL Server 2012, you must install Analysis Services.

Download and install the Microsoft SQL Server 2012 Enterprise Evaluation Edition.

Step 2. Configure SQL Server 2008 (or later) Analysis Services so that the Microsoft SQL Server 2012 Data Mining Add-ins for Office will work correctly
You can now run the SQL Server 2012 Data Mining Add-ins for Office Configuration Utility that is part of the installation. This tool will do everything for you in order to get your SQL Server 2008 (or later) Analysis Services instance configured correctly for use with the Microsoft SQL Server 2012 Data Mining Add-ins for Office.

Click the link below to run the application that will allow you to configure SQL Server 2012:
C:Program Files (x86)Microsoft SQL Server 2012 DM Add-InsMicrosoft.SqlServer.DataMining.Office.ServerConfiguration.exe

Once you have completed Steps 1 and 2, click the Finish button below.

Let’s follow the above steps:

 

9) Download SQL Server Enterprise Evaluation copy for yourselves.

 

10) After Downloading the copy – run the SQL Server Setup. These are the steps to install a SSAS:

step a – e

a. Installation > New SQL Server Stand-alone installation or add features to existing installation

1 step by step install sql server analysis services 2012

b. Run Setup support rules > Ok.

c. In the “Specify a free edition”, select “Evaluation” > Next

2 step by step install sql server analysis services 2012

d. To go forward, you’ll have to accept the terms.

e. Now, you’ll see the SQL Server setup. These are my screenshots while I installed the evaluation copy on my machine – refer to them while you are installing:

Note: I am installing it on my Demo Machine, This is NOT how it’s done in Enterprises. The goal of our demo machine is to get acquainted with the Data Mining features in Microsoft SQL Server.

Here are the screenshots

3 step by step install sql server analysis services 2012

4 step by step install sql server analysis services 2012

Click on Select All features. And I unchecked “Distributed replay controller” and “Display replay client”.

5 step by step install sql server analysis services 2012

Installation Rules: Leave default choices > Click Next.

Instance Configuration. Leave default choices > Click Next.

Server Configuration. Leave default choices > Click Next.

Database Engine Configuration. Click on “Add Current User” and click Next.

Analysis Services configuration:

6 step by step install sql server analysis services 2012

Reporting Services configuration. Select Install Only.

Click NEXT till you reach Ready to Install. Click on Install.

Wait for it to install.

Now After SQL Server is successfully installed:

 

11) Do you remember the Dialog Box from Step 8?

click on: C:Program Files (x86)Microsoft SQL Server 2012 DM Add-InsMicrosoft.SqlServer.DataMining.Office.ServerConfiguration.exe

for Step 1-4. Leave Default choices(s). And After successful completion of these steps:

 

12) In the Dialog box: Microsoft SQL Server 2012 Data Mining Add-ins for office 2010, click on Finish

 

13) you’ll get the message:

“you have successfully downloaded and installed the Microsoft SQL server 2012 Data Mining Add-ins for office”

 

14) Now along with installing the add-in, we also installed sample data.

you can access it by START > All programs > Microsoft SQL Server 2012 Data Mining > Sample Excel Data.

Open this file.

 

15) One last piece of configuration.

In Excel > Data Mining Tab > click on ‘No connections’

Click on NEW and enter credentials of the SSAS server:

connect to ssas via data mining excel addin

 

16) Now – you have all components installed and you are ready to perform data Mining in Excel!

data mining excel add in sample data

That’s about it for this post.

Manage ALL relationship’s between tables while building PowerPivot Model

Standard

One of the task while building a PowerPivot Model is to create/Edit/Delete Relationships between tables. With SQL Server 2012 release of PowerPivot for Excel, we now have a Diagram view and this makes our task of defining relationships easier! Great addition. Any-who when you view your model in Diagram Model, This is how it looks:

powerpivot sql server 2012 diagram view

To Edit a Relationship, You can click on the Relationship represented by the arrow and it would pop up an “Edit Relationship” Dialog Box.

But what if you wanted to Manage ALL relationships of this Model? You can do this!

GO TO Design Tab > Click On Manage Relationships.

manage relationships powerpivot 2012

Now here you can manage (create/edit/delete) relationships among tables. Very easy!

manage relationships dialog box powerpivot 2012

That’s about it for this post. Your feedback is welcome!

 

Ability to create Hans-Rosling styled BI dashboard with PowerView is great!

Standard

I got a chance to play with power-view which seems like great feature of SQL Server 2012! And I liked it that we can create Hans Rosling styled BI dashboard’s with Powerview. For those who do not know about Hans Rosling presentations, check these collection: All Hans Roslings Ted Videos. 

motion charts hans rosling bubble chart with play axis

And by studying modules in SQL server Training kit 2012, I was able to create a similar Dashboard, It was fun!

Now, Listing resources for you, If you are Interested to learn about Powerview:

Check out this SolidQ Journal article: http://www.solidq.com/sqj/Pages/Business_Intelligence/An-introduction-to-PowerView-in-SQL-Server-2012.aspx

Also, SQL server Training kit 2012 is a great resource.

And if you do not like setup’s and want to quickly get hands-on – Check this out: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx

And also Microsoft Virtual Academy for video tutorials about SQL Server 2012 BI:
1. breakthrough-insights-using-microsoft-sql-server-2012-analysis-services
2. breakthrough-insights-using-microsoft-sql-server-2012-reporting-services

That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus