Archive for the ‘Sql Server Analysis Services’ Category

Semi-Additive Measures in DAX | SQLBI

January 29, 2014 Leave a comment

Cem Üney (@CemUney_BI) has shared a tweet with you

October 1, 2012 Leave a comment

Option for high performance Data Warehousing — Cem Üney (@CemUney_BI)

Memory Settings in Tabular Instances of Analysis Services

February 7, 2012 Leave a comment

Handling Multiselect Filter problem in Excel with Calculated Measures AVG,MAX etc.

December 18, 2011 4 comments

In my customers most of the users are using Excel2007/2010 for accessing OLAP data.
Maybe someone has already blogged about it but  i want to share my experience.
I will use Adventure Works 2008 Cube in my example.

The article is about MultiSelect Filter problem in Excel.

Lets take  [Internet Sales Amount] as a measure and look at the values in Date.Calender hierarchy.













Now i will write a formula to calculate the AVERAGE Sales Amount.

CREATE MEMBERCURRENTCUBE.[Measures].[Average Internet Sales Amount 1] AS

Avg( Descendants([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month],SELF),[Measures].[Internet Sales Amount])

Now lets test the value i am choosing January, February and March 2008 as Month filter. The value should be $1,427,876.65 as shown above.











ok we saw that [Averagete Internet Sales Amoun 1] is true.

Now lets select January 2008 and February 2008. And look at the Average before filtering it.














The Average is $1,401,362.39

Lets Drag the hierarchy to the filter any select January 2008, February 2008.










 Opps the value is $793,477.88 and it is not true.

We can write a mdx query to see the correct value, but as i sad before the aim to see the right value in Excel.

ok what to do to see the correct value.  We are writing the same formula, but this time i am using SET in my formula.







Lets check the query





We see $1,401,362.39 as [Average Internet Sales Amount 2]. This is the value we expected to see.

Happy New Year 🙂



Avoiding Message : Break Distinct count measures into seperate measure groups

October 27, 2011 Leave a comment

I was stuck on creating DistinctCount measures in my cube. In fact it’s easy but sometimes you cannot see the way clearly.

When you try to convert any measure’s Aggregation type to DistinctCount it does not give you error but it is marked with blue underline and says ‘Break Distinct count measures into seperate measure groups’

When you see this message normally you try to create New Measure Group and think to put this measure to this group. But when you try to add new measure group you see that you cannnot choose the same Fact Table.  You see your Fact table in already used table.

And also when you right click the Column in your Fact Table from Data Source View Pane and choose ‘New Measure From Column’ then new measure is added to the existing Measure Group and you cannot change the Measure Group again.

So what to do is.

Just go to your cube and right click. Choose “Distinct count” from the begining.

When you click ok. The new Measure Group will be created automatically. And you wont see any warning messages.

I hope this post helps anyone who was stuck on creating Distinct Count Measure like me.

Analysis Services 2008 R2 Performance Guide

October 11, 2011 Leave a comment

Analysis Services 2008 R2 Performance Guide from sqlcat has just released.

That is the link

Download pdf

How to Use Excel’s CUBEMEMBER Function

September 7, 2011 Leave a comment