MS Business Intelligence

How to create an Average Aggregation in SQL Server Analysis services?

Standard

Problem:

How do create a measure that does an average over a field from fact table? You can’t find it the “usage” property while trying to create a new measure:

SQL Server Analysis Services Average Aggregation

Solution:

Before i show you the solution, I want you to know that this is a Level 100 solution to get you started – so depending on the complexity of your cube the calculated measure that you are about to create may or may not perform well – if it does not perform well, you might have to dig a little deeper and here’s one blog post to get you started: URL

OK, back to topic! Here are the steps.

SCENARIO: you need average of Sales Amount.

1. Create a SUM OF SALES AMOUNT measure

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: “SUM” > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

2. Create a COUNT OF SALES measure (important: row count vs. non empty count – this is not a developer’s choice, a business user needs to define that)

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: count of rows OR count of non empty values (again this is not developer’s choice, a business user needs to define this) > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

3. Create a Calculated Measure that equals (SUM OF SALES/COUNT OF SALES)

3a. Switch to Calculations section > create a new calculated member:

SSAS Analysis services new calculated measure

3b. Complete Name, Format String & Associated Measure Group. For the Expression, use the following expression. Please use this as a starting point for your measure:

[code language=”SQL”]
IIF([measures].[COUNT OF SALES]=0,0,[measures].[SUM OF SALES AMOUNT]/[measures].[COUNT OF SALES])
[/code]

4. Before you test it, if you don’t need the SUM OF SALES AMOUNT and COUNT OF SALES measures than don’t forget to hide them!

Conclusion:

In this post, you saw how to define a measure with average aggregation is SSAS.

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

Standard

Do not get intimidated by the long title – it’s not as complex as it sounds! So what am i talking about? Recently, I was developing SQL Server 2012 analysis services Tabular Model (referred to as ‘model’ from now on) and during development I realized that I need to import a column for a table. Let me give an example of what I am talking about:

Consider In the data source there’s a table named ‘Date’ with 15 columns, But for the model I needed only few columns – So I imported only 5 (out of 15) columns from this table:

DateKey
EnglishMonthName
MonthNumberOfyear
CalendarQuarter
CalendarYear

Now During development I figured I need one more column which I had not imported before. Let’s call it FullDateAlternateKey

So How do you add the column ‘FullDateAlternateKey’ in your model during development?

Turns out, It is very simple. This is how you do it:

1. Select the Table in your model designer
2. Go to Table properties.
(If you are not able to see the properties window. Go to View > Properties Windows OR Just select the table and press F4)
3. This is how a properties windows for a Table looks like

properties windows table sql server 2012 tabular mode analyis services

4. Now here click on ‘..’ button after you select the ‘Source Data’ field from the Basic Properties
Source Data edit table sql server 2012 tabular mode analyis services
5. You’ll see a ‘Edit Table Properties’ dialog box. Here you check the column that you wish you import to the model. In my case, I checked the box for the column ‘FullDateAlternateKey’:analysis services check the box for column to be imported 6. Click on OK.

Allow it few seconds and you would see the column in your table! Didn’t I tell you – It’s very simple!

That’s about it for this post. So next time, You need to import a column while developing the model, do not worry – it’s just couple of clicks. But, Even though I was able to add a column later, I had to look at the relationships – create one relationship – make sure other things were right. In short – It was more work. So it’s better that you select the columns that you want in the first place. But If the need arises, you can always add columns (and even remove them) via Table properties. I hope it was helpful!