Today is Strange Date for SqlServer 31/12/2012. This is the only day
SELECT datepart(WEEK,’31/12/2012′) Shows 54! (If your first day is Monday)
It will not show 54 for along time.
When it will show 54 again?
1.This fist day of year must be Sunday.
2.February must show 29. Year has to be leap year.
Happy new year.
Article from Marco Russo
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 🙂
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.