New Style and Old Style Calculated Member commands [Reed]

Chris Webb posted an interesting observation about a new style for calculated members in scripts, where instead of

CREATE MEMBER CURRENTCUBE.[MEASURES].[Old Style] AS 1, VISIBLE = 1

you can just say

CREATE [New Style] = 1;

He points out a "bug" with the old style syntax. I added some comments relating to some experiments he inspired me to do. My conclustion is that it's too predictable a behavior to be a real bug, but if MS wants to call it a bug, that's OK; and in any event, it might be worth being aware of, especially if you mix script assignments with calculated members. Here's the link to his July 2006 blog page:

http://cwebbbi.spaces.live.com/?partqs=amonth%3d7%26ayear%3d2006&beid=cns!7B84B0F2C239489A!823&d=1&wa=wsignin1.0

-- Reed Jacobson

Dynamic Dimension Security in Analysis Services 2005 [Reed]

Part I – The Measure Group Setup

 

The idea of dynamic dimension security is that with a single security role, each user gets a different list of members, based on an MDX expression that returns the permitted set. For example, User A may be allowed read access to different products than User B.

 

The MDX Username function returns the current user’s login information. If there is a many-to-many relationship between the User and the permitted products, you need a security map table, which is actually a “fact”, or bridging, table that contains one foreign key for the user and another for the product.

 

The SQL Server 2005 Adventure Works sample cube does not include a specific security map table, but it is possible to use the Internet Sales measure group as a surrogate: If a user (aka customer) purchased a product over the internet ([Internet Sales Amount]), then that user has the right to see the total sales for that product ([Sales Amount]). The structure is the same as if we used a regular security map table.

 

With a real security map table, you would create a measure group, and use a simple count of the rows as the measure, naming it something cool like [Security OK]. In this example, [Internet Sales Amount] is the surrogate for the [Security OK] measure.

 

Part II – The Username comparison

 

The format returned by the Username function is DOMAIN\User. In an ideal world, your dimension table would have an attribute in the exact same format. In the real world, you often have something a little different. For example, the name in the dimension may not include the domain. The closest thing to a user name in the Adventure Works Customer dimension is the Email Address attribute. Its format is User@adventureworks.com. This is not a bad approximation for a real-world user name attribute.

 

In the MDX security expression you compare the result of the Username function with the value of the [Email Address] attribute. Here’s an expression that will convert the Username to the right format for the Adventure Works [Email Address]:

 

Mid(Username,InStr(1,TestName,"\")+1) + "@adventure-works.com"

 

This expression will work even if users can be on multiple domains. If you know that all users will be on the same domain, you can make the expression evaluate much faster by replacing the (slow) InStr function with a hard-coded length. Assuming a constant domain name of DOMAIN, this formula is the simplified version of the previous one:

 

Mid(Username,7+1)  + "@adventure-works.com"

 

For testing purposes (in case your current domain user name doesn’t happen to be in the Adventure Works Customer dimension), you can create a calculated member as a surrogate for the Username function:

 

Member Measures.TestName as "DOMAIN\anne14"

 

Then, during early testing, you simply replace the Username function with the TestName calculated member. This makes it easy to try out different “users”. Then, during final testing, switch it back to use the Username function.

 

Part III: The MDX Set Function

 

The general concept of the set function is to find all the products where the intersection of the product, the current user, and the [Security OK] measure is not empty. (I’m going to show three methods for doing the set. If you just want the “best” answer, skip to the third one.)

 

** Double-Filter Method (2000 and 2005)

 

Here’s the way I usually did this in 2000. You use an “inner filter”, to find the User whose email alias matches the user logon (there should be only one), and use Item(0).Item(0) to turn it into a member. Then you combine that member with the test measure, and check for whether that tuple is empty in an outer filter against the list of products. This technique does work in 2005, but it’s a bit slow. On my test box it was about 1 minute.

 

Filter( [Product].[Model Name].[Model Name].Members, not IsEmpty(
            ( Filter ( [Customer].[Email Address].[Email Address].Members
            , [Customer].[Email Address].CurrentMember.Name = TestNameX
            ).Item(0).Item(0) , [Measures].[Internet Sales Amount] ) ) )

 

Note: To test the set, you can run a simple query:

 

With

            Member Measures.TestName as …

            Member Measures.TestNameX as …

            Set SecurityTest as …

Select [Measures].[ Sales Amount] on 0,

SecurityTest on 1

From [Adventure Works];

 

** NonEmpty Method (2005 only)

 

One way to simplify this is to use the NonEmpty function that is new in 2005. This basically puts the outer Filter and the IsEmpty test into a single function, but the logic is basically the same: Filter the list of products by testing it against the (single member) set of email members that match the current user. Note that you don’t need the .Item(0).Item(0) for this one, because NonEmpty is comparing two sets, so the result of the Filter function is fine as is. This is much faster—on my box, it’s about 3 seconds. Quite an improvement over 60 seconds.

 

NonEmpty ( [Product].[Model Name].[Model Name].Members
            , ( Filter ( [Customer].[Email Address].[Email Address].Members
            , [Customer].[Email Address].CurrentMember.Name = TestNameX

            ) , [Measures].[Internet Sales Amount] ) )

 

** NonEmptyCrossJoin Method (2000 and 2005)

 

Another way to do it—and this theoretically works in 2000 as well as 2005, but I don’t have a 2000 instance handy to test it on—is to use the NonEmptyCrossJoin function. Mosha says he hates the NECJ function (which is OK for him to say because he wrote it), and to use NonEmpty instead. But the NonEmpty function only takes two sets, and the NECJ function can take an indeterminate number. I have always had a great fondness for NECJ. The real goal is to find all the members of the Product dimension that actually exist in the Security measure group, when intersected with the specified member. This approach took < 1 second on my box, and I still like NECJ. Maybe there’s a better way in 2005 that can avoid NECJ, but I like this one. Plus, as an added bonus, it also exploits StrToMember which is another “no no” that I love to use.

 

NonEmptyCrossjoin(
            [Product].[Model Name].[Model Name].Members
            , StrToMember("[Customer].[Email Address].[Email Address].[" + TestNameX + "]")
            , [Measures].[Internet Sales Amount] , 1 )

 

-- Reed Jacobson

Designing legacy DTS packages from within SS2005 Management Studio [Michael B]

Problem solved. Thanks to all who wrote with suggestions.

Here is the full story. Sorry if it is a bit much, but there were several false starts.

Chapter 1:

I had been running SS2000 and had a bunch of DTS packages on my laptop. I installed SS2005 Developer’s Edition on the machine, which wiped out the SS2000 Enterprise Manager.

When I tried to open any of the legacy DTS packages from within Management Studio, I got an error message box opened telling me that I needed to install SQL Server 2000 DTS Designer Components.  Please note that I was not attempting to migrate the DTS packages to SSIS; I was merely trying to open the packages in the equivalence of the package designer used in the older Enterprise Manager.

I found the appropriate page on Microsoft’s site discussing the problem (http://support.microsoft.com/kb/905417) and the site to download the “fix” (http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en). I downloaded the recommended file (SQLServer2005_DTS.msi), installed it, and rebooted.

It was no help; symptoms were unchanged.

Chapter 2:

One of our colleagues suggested that I uninstall and reinstall SS2005. I was getting close to do this and was in the Add/Remove Programs utility when I noticed SS2005 Backward Compatibility Components item in the currently installed programs list for removal or repair. I ran the repair.

That didn’t fix it, but it changed the symptoms somewhat. Now when I tried to open a DTS package within SS2005, I got 3 separate error message boxes in succession (I believe the third was the same as the single error message I was originally getting (and oddly, sometimes I got just two messages -- the first and third. Also the package tried to open but only displayed some of the icons in the design window, usually just the connecting arrows.)

Chapter 3:

Two colleagues had sent a link to another Microsoft article  (http://support.microsoft.com/?kbid=917406), (and one of those also suggested I install the Backward Compatibility Components which I had already done, as mentioned above) and now, with the 3 error message boxes, my symptoms matched what was discussed on the Microsoft page.

The support page gave two workaround methods. The second one was in effect to reinstall the client components from SS2000. The first method promised to allow the correct opening of DTS packages from within Management Studio, which was my preferred route.

It said to remove the following lines of code from the Sqlwb.exe.manifest file in the \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder.

The way the lines actually display on the Microsoft page, line 3 (red line numbering added by me for reference) is one long line in a small, scrollable text box. In this message, it wraps.

1.
2.
3.               
4.
5.                                                  

When I opened the file for editing I saw something like the following (once again, I’ve added line numbers) with lines 2 and 3 continuing on the right beyond the window width:

1.
2.
3. 4.  

I searched for and found the opening tag and its closing tag mate and deleted those and everything in between as it seemed to instruct on the Microsoft page.

What I didn’t notice was that the line went way off to the right and that there were 3 assemblyIdentity tag groups on that line. Without realizing it, I had deleted all of them. (Of course, I had made a copy of the file before altering it.)

After deleting the lines, SS2005 Management Studio wouldn’t open at all giving a memory location error. When I restored the lines to the file, Management Studio would open again, but I once again, I still couldn’t properly open DTS packages. 

After several additional attempts to edit the file, I finally noticed that there were 3 sets of assemblyIdentity tag groups, only one of which looked exactly like line 3 in the Microsoft text box. I reformatted the lines of the file so that I could more clearly see the logical tag groups. It now appeared in the Wordpad window something like the following (again, minus the line numbering and the bold font on line 7. And again, lines 5, 6, & 7 scroll off to the right, beyond the width of the editing window.) (BWT, I just noticed while writing this email, that lines 5 & 6 are identical, but that’s how they were in the original file.)

1.
2.
3.
4.
5. 6. 7. 8.
9.
10.

What I realized I needed to do was to delete only line 7, keeping lines 3 thru 6,  and lines 8 thru 10. This indeed did the trick. I can now edit DTS legacy packages from within SS2005 Management Studio.

Conclusion:

1. Install or repair SS2005 Backward Compatibility Components using the Windows Add or Remove Programs utility in Control Panel.

2. Download and install SQL Server 2000 DTS Designer Components which is the SQLServer2005_DTS.msi file from http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

3.Carefully edit \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ Sqlwb.exe.manifest  file removing only the tag group that looks like line 7 immediately above.

I hope this is clear and is helpful.

Michael Bergelson
Architect
Hitachi Consulting

BTW, I also noticed that under SS2005 Management Studio, there is no option to view the DTS package run logs on a right click, nor have I found that function anywhere else as yet.

RS Aggregate and Manual MDX

In a recent post, I said RS Report Designer is smarter than I thought. And it is. But it is also really, really weird when you try customizing your MDX. (Not that I ever do that.)

This might be a bug, but it seems to happen only when you customize the MDX. Here's a scenario:

1) Use the wizard to create a table report with Country, State-Region, and City levels. Use Sales Amount (additive) and Average Unit Price (non-additve) as measures, and include subtotals.
 [At this stage, the automatic MDX includes just the City level]

2) Change the Sum function for Average Unit Price (and Sales Amount too, if you feel like it), to Aggregate. [At this stage, the MDX changes to include the Country and State-Region attributes.]

3) Delete the Country group from the report. [The auto-MDX changes to remove the Country attribute, and the report still works fine.]

BUT. If between step 2 and step 3, you go make an intsy-weentsy change to the MDX, then the auto-MDX does not remove the Country attribute. IN THIS CASE, none of the lower-level Aggregate functions work. They all return Null. If you have unused higher-level attributes in your MDX, lower-level Aggregate functions return Null. 

The really wacko part is that if you never touch the MDX, and just let the Report Designer fiddle with it, everything seems to work OK. But if you do use the Aggregate function and you do customize the MDX statement, make sure that you have a grouping in the report for each attribute that has higher-level totals.

Incidentally, the place I ran into this big time was when I was decided to add an All level to a hierarchy in the cube. When I had the All level there, the report showed all blanks. When I removed the All level in the cube, the report worked. It was really strange. Why would a change like that in the cube definition make the report switch between showing correct values and showing nulls? Well, naturally, I was writing my own MDX, and I asked for [Hierarchy].Members, rather than explicitly cross-joining specific attributes. So having the All level in the hierarchy returned an extra level in the result set--a level that wasn't "used" in a grouping in the report, so all the Aggregate functions below that level returned Null. Removing the All level from the hierarchy made it so that all the levels in the result set were actually used in the report.

- Reed Jacobson

Happiness: RS Report Designer smarter than I thought

Ever since the earliest Yukon beta's I've been frustrated with how RS report wizard handles AS as a data source. The problem has to do with non-additive measures--for example, a distinct count. In the cube, you create a measure or calculated member that gets the right answer at every point in the cube. But when you use the RS wizard to create the MDX and the report, it only queries for the lowest level used in the report, and then uses Sum for all the higher levels. This is fine when it is a Sum, but doesn't work at all for Distinct Count.

Brian Welcker kept telling me that they added the Aggregate function and fixed the problem, but every time I tried, I still saw the same leaf-level MDX and Sum function. And I know that you can't get the right answer with only leaf-level MDX. So I would change the MDX to return the multi-level values, and then change the report to use the Aggregate function instead of Sum, and it did work. But it really didn't make a good story.

Well, earlier this week, I discovered the secret. I had always fixed the MDX first, and then changed the Sum function to Aggregate. That was obvious to me, because how would Aggregate get the higher level value if the MDX is wrong? Well, I "accidentally" changed the Sum function to Aggregate first, and when I previewed the report, it showed the right answer. How could it do that? Switching to the data pane, the MDX was still the old detail level. So I used Profiler to capture the MDX, and the MDX was different--it included the additional levels.

The bottom line is that if you use the MDX designer to create the MDX, and then modify the report to use the Aggregate function instead of the Sum function (assuming you have non-additive measures), then the report designer changes the MDX to make it work. When I think about it, this really does make sense. If all you have is Sum formulas, it's more efficient to just add them up in RS. RS can't really know whether a measure is additive or not, so it does default to use the Sum function. But all you have to do as a user is build the report (with the wizard) and then go change the function for any non-additive measures to Aggregate. The report then adjusts the MDX to make it work.

It's a bit creepy for me to have it do all that "automatically" for me. (The windows on my car open with crank handles--or zippers--and don't even talk to me about automatic door locks.) But it actually does make sense and it does work. Maybe it's even documented in the new July BOL ;-).

- Reed Jacobson

Top 10 Plus Other Query

On Teo Lachev's forum at Prologika, there was a question from gudjonv about how to do a RS chart that showed the top 10 members plus an "all other" member (thus, 11 slices of the pie). [See http://prologika.com/CS/forums/1465/ShowThread.aspx]. I didn't feel like registering so I could post a response, so I'll put the answer (converted to Adventure Works) here.

This is the general method I've used to do "Top 10 Plus Other" since OLAP 7.0. I can't think of any compelling functionality in 2005 that would do the job better. (Of course, in 2005, the Aggregate function will work even with a distinct count measure, so that makes this solution more functional, but it doesn't change the solution.)

WITH
  SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
  MEMBER [Product].[Subcategory].[Other] AS
Aggregate([Product].[Subcategory].[Subcategory] - TCat)
 SELECT { [Measures].[Sales Amount] } ON COLUMNS,
 TCat + [Other] ON ROWS
FROM [Adventure Works]
;

- Reed Jacobson

Sadness: Excel 2007, Subcube Subqueries, and Growth Formulas

When I first saw a demo of Excel 2007 in the Beta 1 timeframe, I was told that the Slicers in Excel 2007 would still use the WHERE clause, not the new Subcube Subquery available in SQL2005. I was very disappointed (partly because in the beta timeframe for SQL 2005, the Where clause behaved so badly). But when I got my copy of Excel 2007 and looked at the actual MDX, it turns out that they do use the subcube subquery. At first I was happy, but then I discovered a strange consequence of using the subcube subquery (while, in a bit of irony, by RTM the relative benefit of a subcube subquery has decreased because they fixed the behavior of the Where clause).

Suppose you have a "Sales Growth" measure. In all likelihood, the MDX will include something like ([Measures].[Sales],[Time].[Calendar].CurrentMember.PrevMember). Now suppose you have a query with [Nov 2007] as the slicer. If the slicer goes into the WHERE clause, then the current member of Time.Calendar is [Nov 2007] and the prev member is [Oct 2007] and all is good. But if the slicer goes into a subcube subquery, then the Time.Calendar dimension gets collapsed to [All]-->[2007]-->[Q4]-->[Nov 2007], but the current member is [All], not [Nov 2007]. With the subquery, the value of [All] equals the value of [Nov 2007], so the basic measures look alright, but the prev member for [All] is Nothing, so the growth calculation screws up.

The only way I've been able to get the correct growth to show up is by explicitly putting the Time dimension onto the Row or Column axis, so there's a meaningful CurrentMember. It works, but it looks a bit stupid.

I was hoping that the Existing operator (that I recently discovered) would provide a workaround for the problem (because the formula could then use Existing Time.Calendar.Month.Members. But, sadly, the "current context" for the calculated member does not take into consideration the subcube subquery restriction, so you get the entire set of months, not just the "current" month. So this approach did not work.

If anyone has an idea for how to write a formula that properly calculates growth, even when the filtering is done by using a subcube subquery, I'd love to hear it.

- Reed Jacobson