Tag: SSAS 2005

If you want to create a calculated measure in SSAS (SQL Server Analysis Services) that will always get the value for yesterday, this is what I have come up with:

CREATE MEMBER CURRENTCUBE.[MEASURES].[My Yesterday Measure]
 AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),
FORMAT_STRING = “$#,##0.000”,
VISIBLE = 1  ;

What is really cool is you can test this before you even make the measure, using an MDX query:

WITH
   MEMBER Measures.[Yesterday Measure Test1] 
   AS ([Time].[Calendar Date].&[2007-07-09T00:00:00],[Measures].[My Cool Value])
   MEMBER Measures.[Yesterday Measure Test2] 
    AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),FORMAT_STRING = “$#,##0.000”
SELECT {
Measures.[Yesterday Measure Test1]
,Measures.[Yesterday Measure Test2]
} ON COLUMNS
FROM [My Cube]

and now you can take that calculated measure and tweak for any days back (30, etc)

Cool! 🙂

Named Sets in SSAS 2005 (SQL Server Analysis Services 2005) are a good thing. You can set up named sets for things like “Last 3 months” or “Yesterday” which is really nice for end users. The nature of dates is that you can make the MDX dynamic but yet the named set is still “static”.

You can also create sets when running MDX queries, and use them later in queries you run. These are dynamic named sets. The problem is, when you are working on your cube, you cannot define dynamic named sets in the “Calculations” tab. The named sets there are static. If you try to define a named set that say, for instance, tries to get the “Top X Salespeople by Revenue” or something, you can create it, but what happens is when you deploy your cube, the named set gets run for one slice of the cube, usually just the default (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817566&SiteID=1   and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1304964&SiteID=1).

I did read on a site, that in SSAS 2008, you will be able to create dynamic named sets in the cube editor, so that is good.

One other thing with named sets, if you create a named set that is using TopCount, when you bring it into an Excel pivot table, you will see the Top X values you are looking for (sliced incorrectly, as I stated above, but still there). Thing is, they are sorted by the SET Expression, not the Numeric Expression. I haven’t found a way to get them to sort by the numeric expression. Example from above, you have the Top 3 Salespeople by Revenue. You want to see that sorted by revenue. If you run a MDX query using TopCount

SELECT
{[Measures].[Revenue]} ON COLUMNS,
{TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])} ON ROWS
FROM
    [MyCube]

you will see it ordered in the query results correctly

John Smith           $1000
Amanda Jones      $950
Homer Simpson     $500

etc

Now if you take the TopCount out –

TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])

and create a named set in your MDX Script of the cube editor

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT([Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;        

or even like this as the forums above say

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT(Existing [Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;  

When you pull that named set into your pivot table in Excel, they wont be ordered by Revenue, instead, by name

Amanda Jones      $950
Homer Simpson     $500
John Smith           $1000

Funky stuff. My recommendation – if you are going to be needed dynamic named sets like the ones created above, just create a SSRS (SQL Server Reporting Services) report instead, or wait until SSAS 2008 comes out.

Note:

Now, the above is just what I have found. There might some crazy way that I am unsure of in MDX or somewhere to get this to work, but I can’t find anything that would let me do what I want.

In SSAS (SQL Server Analysis Services), if you have a measure group, with some measures as AggregateFunction of Sum and some of AverageOfChildern, you will see a red squiggly line on the measure group and if you highlight it (or build the cube), you will get this error:

Semi-additive measure requires a time dimension.

The problem starts with the AggregateFunction property of your measures, where some are Additive, some are Semi-Additive. You can see which are which by looking at MSDN here. What I found will get rid of the error, but still might not help you is this: if you go to the properties of your Time dimension, and check its type. By default dimensions are created with type “Regular”. If you change your Time dim to type “Time”, then rebuild, you won’t get any errors, and you can deploy.

Now, this might be all good, but in the case I ran into, it still didn’t work for me. AverageOfChildern only applies to the Time dim. Not what you would expect from looking at it. Say you have a measure, that you want the Average (AVG) instead of Sum, you would think that picking AverageOfChildren as a AggregateFunction property would do the trick, well it doesn’t. If you do this, deploy, and browse your cube, you will still see that the values are Sum. WTF right? Well like it sparsely says in the docs, it only applies to the Time dim.

In order to get the Average of a measure, here is the best way to go about it that I have found.

1) Create a Measure from the field for Sum
2) Create a Measure from the field for Count
3) Create a Calculated Member (Measure) for the field, taking Sum/Count from step 1 and 2.
3a) you can check for 0’s in the count by using IIF()
3b) for best performance, check your NON_EMPTY_BEHAVIOR and if it applies to you, it should speed up your results tremendously. Usually you can just choose the fields you are using in your calculation and it should be fine from what I have seen (I am no expert though)
4) make sure to hide your Sum and Count measures if you don’t want your clients to use them by setting the Visible property to false.
5) Build, Deploy, Process, Report

If you are deploying/building a cube in SQL Server Analysis Services (SSAS), and you get tons of errors, and littered throughout you see

OLE DB error: OLE DB or ODBC error: Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.; 28000.

You should take a look at your data sources, and on the impersonation tab, play around with the different options. Usually it is set to “Use the service account” or “default”. I have tried “Use the credentials of the current user” but got errors back when deploying saying that the impersonation method was invalid for processing. I have found that “Use a specific user name and password” works. As I sit and wait for the cube I just finally got to deploy to finish, I figured I would write this up in a post. Your mileage may vary.