Memory Settings in Tabular Instances of Analysis Services
Memory Settings in Tabular Instances of Analysis Services.
Article from Marco Russo
Handling Multiselect Filter problem in Excel with Calculated Measures AVG,MAX etc.
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
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
Analysis Services 2008 R2 Performance Guide from sqlcat has just released.
That is the link
How to Use Excel’s CUBEMEMBER Function
Where’s your mobile business intelligence strategy, Microsoft? – The Frontline – a blog from V3.co.uk
SQL Server PerfMon counters for tracking Windows memory
Lock Pages in Memory … do you really need it?
Memory Configuration issue from Ask the Performance Team
My Company
