(Newbie) Trying to get rid of NonEmptyCrossJoin

Hi everyone,

As I have said before, I am new to MDX. Any help much appreciated.

I am trying to substitute the Exists() function for the NonEmptyCrossJoin() function, but Exists is not producing the results that I would expect. The test query that I am running is:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

NonEmptyCrossJoin({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]},

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]})

This is not ideal code, but performs correctly, only listing members with start and end dates with the appropriate values. I tried to substitute that query for this:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

Exists(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}),

NonEmpty({[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]}))

This query effectively ignores the second set (i.e. the End Date set), listing all values with start dates in the given range. Can anyone tell me what I am doing wrong?

Any help much appreciated.

Edit: Adding a measure group at the end of the Exists function and/or removing the NonEmpty functions do not correct the problem.

Not sure whether you've read this blog entry, which discusses different scenarios in which NECJ was used. The closest equivalent is Exists() with measure group, but it depends on the scenario in which NECJ was being used:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

>>

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

...

NonEmptyCrossJoin(set1, set2, ..., setN, K) is equivalent to Exists(set1*...*setK, set(K+1)*...*setN, "measuregroupname")

where "measuregroupname" is the name of the measure group to which the current measure belongs.

...

>>

So, in your case, it should be like:

Exists({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}

* {[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},, "measuregroupname")

|||

Hi Ed,

First of all, why are you trying to replace NECJ with Exists rather than NonEmpty - are you setting the NullProcessing property anywhere? I think you're better off using the NonEmpty function here.

Secondly, in my experience you should always include a measure somewhere in either of the sets you pass into NonEmpty - especially if you have multiple measure groups in your cube. If you don't then I think what happens is that you remove all the items in your set which are empty for all measures in the cube, which can be confusing when you're only showing one measure and can also slow your query down.

So... does the following do what you want?

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN},[Measures].[Value]) ON ROWS

FROM [AS Test1]

WHERE

(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]} *

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},[Measures].[Value]))

HTH,

Chris

|||Thank you very much Chris and Deepak for your replies. I'll give those approaches a try.

(MDX) EXISTING operator - inconsistent behaviour when using subcubes?

I am getting what I think is inconsistent behaviour when using the EXISTING operator to obtain a set defined by the current query context... See the two queries below (explanation follows).

WITH MEMBER [Measures].[MyDate] AS

Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue

SELECT {[Measures].[MyDate]} ON 0

FROM [Adventure Works]

WHERE [Date].[Date].[October 26, 2002]

... returns [26-10-2002]

WITH MEMBER [Measures].[MyDate] AS

Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue

SELECT {[Measures].[MyDate]} ON 0

FROM

(

SELECT [Date].[Date].[October 26, 2002] ON 0

FROM [Adventure Works]

)

... return [31-08-2004] (the last date in the Adventure Works time dimension).

From my point of view the two queries should return the same date (October 26, 2002), since the context is the same. The only difference is that in the first query the context is specified in the WHERE clause, whereas the second query specifies the context in a subcube. There really should not be any difference, should there?

I agree this looks confusing but I suspect it's intended functionality: calculated members can 'look outside' a subcube defined in the FROM clause (see http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!212.entry for example). This would be useful, for example, if you had a previous period growth calculation that you wanted to return a meaningful value even when you were querying a subcube which had been restricted to just one Date.|||

Chris, I was looking for an answer from you - not quite the answer I hoped for though.

I see your point, but if what you are saying is true, the value of the EXISTING operator is not very high and it will be very questionable if Moshas approach to writing "multiselect friendly MDX calculations" (http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx) is applicable (which I really hope it is!). Does it not also fail to explain why the following query only returns the sum of [Internet Sales Amount] from 2002 and 2003 (which - to me - is expected behavior)?

WITH MEMBER [Measures].[Test 2] AS

SUM(EXISTING [Date].[Date], [Measures].[Internet Sales Amount])

SELECT [Measures].[Test 2] ON 0

FROM

(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])

If what you are saying is true, wouldn't the calculated member return a sum for all years - since the calculated member would "look outside" the subsube, which is restricted to 2002 and 2003?

|||

I think you've made a mistake in your query - the expression [Date].[Date] resolves to the All Member of that hierarchy. I think what you wanted to do was this:

WITH MEMBER [Measures].[Test 2] AS

SUM(EXISTING [Date].[Date].[Date].Members, [Measures].[Internet Sales Amount])

SELECT [Measures].[Test 2] ON 0

FROM

(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])

Which returns a different (and higher) value, the sum of all years.

You're right, though, it does cause problems if you're using subcubes instead of sets in the where clause and following Mosha's advice on multi-select friendly MDX.

Chris

|||You're right Chris - thanks... I will try go get Mosha to comment on this issue... I will follow up in this thread.|||

I know this was an area where there was some discussion about what the most useful behaviour would be - on balance, I think they've done the right thing. As far as the multi-select issue goes, is there any reason why you're not using sets in the WHERE clause instead?

<Cheeky Plug>
This topic is discussed in the second edition of 'MDX Solutions' in one of George's chapters:
http://www.amazon.co.uk/exec/obidos/redirect?link_code=ur2&tag=chriswebbsbib-21&camp=1634&creative=6738&path=ASIN%2F0471748080%2Fqid%3D1141661836%2Fsr%3D8-3%2Fref%3Dsr_8_xs_ap_i3_xgl
</Cheeky Plug>

Chris

|||

Just placed an order for this book yesterday, so I am looking forward to reading the good arguments for the current behavior.

Why am I not using sets in the where clause for multi-select? There are more than one reason actually.

1) If a hierarchy is placed in the WHERE clause, the same hierarchy cannot - as you know - be placed on an axis (which, in many cases, in desireable).

2) The use of the WHERE clause vs. the use of subcubes for restricting the query result varies from client tool to client tool. The cube browser in SSMS, for example, uses both subcubes and sets in the WHERE clause depending on how you setup the query.

One could argue - perhaps - that the client tool should be smart enough to always place any hierarchy not already on an axis in the WHERE clause?! I wonder if this would always solve any problem? Hmm...

|||

EXISTING operator takes into account current coordinate. The whole difference between WHERE clause and subselect, is that WHERE clause sets current coordinate, while subselects merely do top level Exists with Axis (BTW WHERE clause also does it), and apply visual totals.

The confusion between WHERE and subselects is big and seem to grow every day :( It is unfortunate that not everybody realizes the semantic difference between the two and when each should be used. Perhaps we need another operator in MDX which will take into account the current restriction of subselect and/or CREATE subcube. Excel 12 is only going to add to the confusion, since it uses subselects extensively and it uses them for multiselect too, rendering my advice on how to write multiselect friendly calculations less useful...

Mosha

|||

I blogged on this topic.

http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx

Comments are welcome...

(Harder?) cube query & design question

Hi,

I have an MDX query (and worst case a cube design) problem that I haven't been able to solve, any ideas on how to go about this? Here's a simplified description, starting with the

Dimension & Attributes

* We make phone [Call]s.

* In each [Call], and for a number of [Product]s we ask a number of [Question]s.

* Each [Question] results in an [AnswerText]. These also reside in a user hierarchy [Answer Dimension].[Q and A].

* All of the above are attributes in the [Answer Dimension].

Facts

* Measures.[Answer Count], which at the granular level is always one, i.e. each fact records that we recieved a single [AnswerText] to a single [Question] about a single [Product] in a single [Call].

* Measures.[Call Count], which is the number of [Call]s made.

NB: We have thousands of different questions and answers, so surfacing each individual question and answer as a measure is not an option.

Queries & issues

* Counting the answers to a single particular question is easy:

SELECT
Measures.[Answer Count] ON 0
FROM cube
WHERE ([Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue])

* What I can't figure out is how to get Measures.[Answer Count] for multiple simultaneous questions, i.e.:

"For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]"

I've tried (unsuccessfully) the following:

1) Various ways that equate to doing an intersection between the first and the second question. This fails since it returns the empty set - a single fact Measure.[Answer Count] only correspond to a single question.

2) Creating sets at the [Answer Dimension] leaf level where [Answer Count] = 1, and counting the number of tuples in the set. Although this looked promising it still failed me, the dimensionality either didn't allow combining the two questions or didn't slice the facts at all when say using a Filter() to combine the sets, even when using two different attribute to specify the answers.

3) Aggregating [Answer Count] to the set {[Call] * [Product]}, and Filter() where both questions have [Answer Count] >= 1. Again promising, but can't figure out the syntax to use.

Big Questions

* Is 2) or 3) above doable at all? What is the rough syntax needed?

* Is there a better (working!-) way to query this cube?

* Is there a better way to design the cube for answering these types of combined questions (remembering we have thousands of distinct questions and answers, and new ones get added over time, and a total of millions of facts)?

Any and all suggestions Much Appreciated!

Kristian

Very complex problem. My question is simply if you have tried data mining and a decision tree model on this problem? Is DM not an option?

Regards

Thomas Ivarsson

|||

Assuming that the question: "For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]" refers to counting product/call combinations, cascading NonEmpty() might compute what you're looking for, like:

Count(NonEmpty(NonEmpty({[Call].[Call].[Call] * [Product].[Product].[Product]},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

|||Yes! This gives the right total on my mini test cube. One (hopefully simple) follow-up question:

The calculation now happens at the right

[Call].[Call].[Call] * [Product].[Product].[Product]

level. How do I write the query so that I get the Count in a colum, and the list of Products on the rows? I.e.

Occurrences
Car 2
Bowl 1

For instance, this won't work:

WITH
SET MySet AS
[... Deepak's code from above ...]
MEMBER Measures.Occurrences AS
Count(MySet)
SELECT
[Product].[Product].[Product] ON 0
FROM cube

since it will give all Products the same total. Ideas?

Many thanks!

Kristian

|||

Hi Kristian,

To your query above, maybe you can add "Existing", to select only relevant tuples for each cell context:

MEMBER Measures.Occurrences AS
Count(Existing MySet)

|||Many thanks Deepak, EXISTING works for small sets, except:

When using large sets, the SSAS service crashes when running the above query. Any ideas on how to make the above query not crash the service, either through increasing limits on the SSAS instance or optimizing the query itself, any ideas?

Kristian|||

Kristian, unfortunately I haven't done much tweaking of memory settings for AS 2005 - maybe someone else can comment on this?

One alternative, which may save memory but be much slower, is to avoid creating the cross-joined named set. So, the Occurrences calculated measure could be directly defined as:

Count(NonEmpty(NonEmpty({(Existing [Call].[Call].[Call]) * (Existing [Product].[Product].[Product])},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

sql

"OR" Condition in MDX?

Hi!

I'm new to MDX and have a simple problem with apparently no simple solution in MDX word, according to discussions, e.g. http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/25ea010035f3f097/8ab30ca61c23bbfd%238ab30ca61c23bbfd

Is it possible to get records from OLAP cube, using OR condition to members of different dimensions? Below is an abstract example using [Adventure Works]:

select
non empty {[Measures].[Reseller Sales Amount]} on columns,
non empty { [Employee].[Employee].AllMembers
* [Product].[Product Line].AllMembers } on rows
from [Adventure Works]
where
[Geography].[Country].&[Canada]
OR SOMEHOW
[Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop]

Records should be returned if either country is Canada OR Business Type is Bike Shop. It's trivial with regular SQL, but seems to be very tricky with MDX. There may be more than 2 parameters.
It is easy to implement OR condition if parameters belong to the same dimension. But how if they are different?

Thanks!
Andy.

It is easy to do as well. Depending on how calculations are inside the cube it might be done in couple of different ways, but below is probably what most people would write here.

select

non empty {[Measures].[Reseller Sales Amount]} on columns,

non empty { [Employee].[Employee].AllMembers

* [Product].[Product Line].AllMembers } on rows

from [Adventure Works]

where

{([Geography].[Country].&[Canada], [Reseller].[Reseller Type].[Business Type].MEMBERS),

([Geography].[Country].MEMBERS,[Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop])}

"Less Than or Equal To" MDX query

The following MDX query is generated by the Reporting Services? I need to edit the this MDX query so that it will accept the parameter value "Less Than Or Equal To" the @.AccountPeriodAccountPeriod parameter to filter teh cube data. Thanks.

SELECT NON EMPTY { [Measures].[Costing], [Measures].[Distinct Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[Account Period].[Account Period].ALLMEMBERS * [Account Period].[Prescription Date].[Prescription Date].ALLMEMBERS * [Drug].[Protocol Code].[Protocol Code].ALLMEMBERS * [Drug].[Drug Name].[Drug Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED)) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED) ) ON COLUMNS FROM [Drug Cost By Account Period])) WHERE ( IIF( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED).Count = 1, STRTOSET(@.DrugDrugFirstWord, CONSTRAINED), [Drug].[Drug First Word].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Assuming that there is an [AccountPeriod].[AccountPeriod] attribute, ordered ascending by time:

SELECT (Filter([AccountPeriod].[AccountPeriod].[AccountPeriod].Members,

Rank([AccountPeriod].[AccountPeriod].CurrentMember,

[AccountPeriod].[AccountPeriod].[AccountPeriod].Members) < =

Rank(STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED).Item(0).Item(0),

[AccountPeriod].[AccountPeriod].[AccountPeriod].Members)))

|||Thank you Deepak Puri and it is worked beautifully. However I forgot to include one other condition that only the AgencyID has the Account Period equal to the @.AccountPeriod should be allowed in the final dataset. AgencyID is from another dimension different from the Account Period dimension. Thanks.|||

If you want only those AgencyID members with [Measures].[Costing] data for the selected Account Period, try replacing:

[AgencyID].[Agency Id].[Agency Id].ALLMEMBERS

with:

NonEmpty([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS,

{[Measures].[Costing]} *

STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED))

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

If you don't want to base that on the system date is this still the "best practice" to introduce some "flags" in the time dimension to identifiy the actual day, last week, ... Just the way it was introduced with the BI Accelerator tool...

Thanks,

It's an interesting question...

I can't actually remember how SSABI did its current time periods, but from what you say I guess it created sets which filtered on a member property value such as 'Is Current Day'. That should still work, but my feeling is that in AS2005 it might be better to create relative time attributes. So, for example, if you had Year, Quarter and Month attributes you would add Relative Year, Relative Quarter and Relative Month attributes too. These would have members on them such as 'Current Month', 'Current Month - 1', 'Current Month - 2' etc. This would allow users to create much more sophisticated relative time period reports, and at the same time still see the actual dates involved if they crossjoined the actual time period attributes with the relative time period attributes because autoexists would automatically do filter out everything but the correct combinations.

Incidentally, using the NOW() function to find the current date is a bad idea in AS2005 because as far as I can see, server-defined sets are now evaluated at processing time. So for example, add the following set to Adventure Works:

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

If you run a query which shows the contents of this set, you should see the CY2004 member returned. However if you change the system date of your machine to be 2005 instead of 2006, then rerun the same query, you'll see that CY2004 is still returned. You need to reprocess the cube ('Process Script Cache' seems to be sufficient) to get CY2003 to appear in your query.

Chris

|||

Chris, I think your suggestion is good. However, it does have some rather nasty processing implications, since you have to reprocess your time dimension each day (if the granularity of your time dimension is date). A "Process Update" is sufficient of course, but this method invalidates (and therefore removes) all aggregations that include attributes from the time dimension. You are therefore forced into doing a "Process Index" on all your measure groups (except those that might not contain the time dimension) to get the aggregations back online.

It puzzles me that named sets should be evaluated at processing time?! As far as I know they are evaluated the first time they are requested by a query in a given session. The set is then cached until the session expires or the set is removed by the DROP SET statement. I have not verified that this is true for server-defined sets, though...

Anyway, using named sets for dynamic time, should in my opinion be one of the best approaches. Using Chris' suggestion allows you to define a single named set "Current Day", which you can base a number of other sets on. For instance, having created the set "Current Day", you can easily create the set "Current Month" by using the Exists function:

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

If you are using a front-end tool, make sure that it supports the use of named sets. If not, you have to create a calculated member that aggregates across the named set, but this approach has quite a few disadvantages (one of which is that a calculated member does not establish current cube context).

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

Yes, I can see that the two queries return the same set. I don't think the example proves your point, though, since the named set under no circumstances is evaluated in the context of the query, which is why the WHERE clause has no effect on the set of dates returned.

I have also tested the fact that the named set is evaluated at processing time. This is actually not the case. It is evaluated the first time it is requested in a query. This can be verified (following the example in a previous post) by doing a process, changing the system date and then running the query, in which case the returned set will accurately reflect the new system date. Anyway, you are absolutely right that the content of the named set does not change until the cube is reprocessed. I wonder if this behavior can be changed? For the sake of dynamic time, however, it doesn't really matter too much. If only you process a part of your cube database once a day (after midnight), you should be good to go.

|||

You're right about the sets - sorry, my mistake. I think I was getting confused with a slightly different issue which is that the same set can return different results depending on where you put it in the MDX Script. Here's an example in Adventure Works: if you add the following onto the end of your MDX Script -

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Then run queries showing the contents of test1 and test2, you can see that they return different results - test2 accurately reflects the change made by the assignment. This of course isn't inconsistent with the set being evaluated the first time it's queried (presumably the results are then stored in the 'script cache' I was processing), just that it's correctly evaluated in the context of the script.

Anyway, I've had an idea on how to have a relative time dimension of the type I've described without incurring any of the processing penalties. What might work (and I need to test this) would be to create a separate Relative Time dimension then add it to your cube with *no* relationship to any measure group; you could then use MDX Script assignments to map the members on it to the equivalent members on the real time dimension. Definitely worth investigating...

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success). |||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

|||That link appears to point back to this thread, but I would be extremely interested in seeing your proof of concept code. I didn't know it was possible to dynamically link dimensions at run time, but that would solve for me a number of very difficult issues I am struggling with.|||

I have seen two other ways of implementing the current day.

Never have members in the time dimension after the current date. Today it is Aug 22 so never let at time member enter the dimension after this date. With this solution you can look for the last time member in MDX by lastchild.lastchild and so on. In this case it is the ETL process and SSIS that manage the time dimension.

Another solution is to look for a measure in the cube that you know will reflect the current day or the day before the current day. Actual Sales is a good candidate if budget sales is entered for the full year in advance. In this case you can use Tail with Filter, in MDX, to look for the last non empty member. If this set works you can add Lag(MDX) och Lead(MDX) to your first named set, together with Lead(MDX).

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

In ETL, I select the min and max trx date keys (a bit more involved if multiple fact tables which I have) from the fact table and store them in a one row table called DIM_DAY_RANGE. Then use the following where clause for your DAY_DIM view

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

I need this information ("today") because I need to build up reports which show "todays" Orders, Revenue or whatever. The users should do nothing but open the report, so I need something like a set to use instead of a fixed day or something what a user has to select.

Using the approach to "just offer what you have" is OK in many cases, however what do you do if you not only have actual sales but also your planned sales? Then you have to be able to show all days (or months) of the current year or also the next year... So this will not help you much...

|||

Quote:"what do you do if you not only have actual sales but also your planned sales". Do you have a version dimension like Actual, Budget, Planned, Forecast ? Or is this in the measures like, ActualSales, BudgetSales, Planned Sales, ForecastSales?

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

If you don't want to base that on the system date is this still the "best practice" to introduce some "flags" in the time dimension to identifiy the actual day, last week, ... Just the way it was introduced with the BI Accelerator tool...

Thanks,

It's an interesting question...

I can't actually remember how SSABI did its current time periods, but from what you say I guess it created sets which filtered on a member property value such as 'Is Current Day'. That should still work, but my feeling is that in AS2005 it might be better to create relative time attributes. So, for example, if you had Year, Quarter and Month attributes you would add Relative Year, Relative Quarter and Relative Month attributes too. These would have members on them such as 'Current Month', 'Current Month - 1', 'Current Month - 2' etc. This would allow users to create much more sophisticated relative time period reports, and at the same time still see the actual dates involved if they crossjoined the actual time period attributes with the relative time period attributes because autoexists would automatically do filter out everything but the correct combinations.

Incidentally, using the NOW() function to find the current date is a bad idea in AS2005 because as far as I can see, server-defined sets are now evaluated at processing time. So for example, add the following set to Adventure Works:

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

If you run a query which shows the contents of this set, you should see the CY2004 member returned. However if you change the system date of your machine to be 2005 instead of 2006, then rerun the same query, you'll see that CY2004 is still returned. You need to reprocess the cube ('Process Script Cache' seems to be sufficient) to get CY2003 to appear in your query.

Chris

|||

Chris, I think your suggestion is good. However, it does have some rather nasty processing implications, since you have to reprocess your time dimension each day (if the granularity of your time dimension is date). A "Process Update" is sufficient of course, but this method invalidates (and therefore removes) all aggregations that include attributes from the time dimension. You are therefore forced into doing a "Process Index" on all your measure groups (except those that might not contain the time dimension) to get the aggregations back online.

It puzzles me that named sets should be evaluated at processing time?! As far as I know they are evaluated the first time they are requested by a query in a given session. The set is then cached until the session expires or the set is removed by the DROP SET statement. I have not verified that this is true for server-defined sets, though...

Anyway, using named sets for dynamic time, should in my opinion be one of the best approaches. Using Chris' suggestion allows you to define a single named set "Current Day", which you can base a number of other sets on. For instance, having created the set "Current Day", you can easily create the set "Current Month" by using the Exists function:

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

If you are using a front-end tool, make sure that it supports the use of named sets. If not, you have to create a calculated member that aggregates across the named set, but this approach has quite a few disadvantages (one of which is that a calculated member does not establish current cube context).

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

Yes, I can see that the two queries return the same set. I don't think the example proves your point, though, since the named set under no circumstances is evaluated in the context of the query, which is why the WHERE clause has no effect on the set of dates returned.

I have also tested the fact that the named set is evaluated at processing time. This is actually not the case. It is evaluated the first time it is requested in a query. This can be verified (following the example in a previous post) by doing a process, changing the system date and then running the query, in which case the returned set will accurately reflect the new system date. Anyway, you are absolutely right that the content of the named set does not change until the cube is reprocessed. I wonder if this behavior can be changed? For the sake of dynamic time, however, it doesn't really matter too much. If only you process a part of your cube database once a day (after midnight), you should be good to go.

|||

You're right about the sets - sorry, my mistake. I think I was getting confused with a slightly different issue which is that the same set can return different results depending on where you put it in the MDX Script. Here's an example in Adventure Works: if you add the following onto the end of your MDX Script -

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Then run queries showing the contents of test1 and test2, you can see that they return different results - test2 accurately reflects the change made by the assignment. This of course isn't inconsistent with the set being evaluated the first time it's queried (presumably the results are then stored in the 'script cache' I was processing), just that it's correctly evaluated in the context of the script.

Anyway, I've had an idea on how to have a relative time dimension of the type I've described without incurring any of the processing penalties. What might work (and I need to test this) would be to create a separate Relative Time dimension then add it to your cube with *no* relationship to any measure group; you could then use MDX Script assignments to map the members on it to the equivalent members on the real time dimension. Definitely worth investigating...

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success). |||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

|||That link appears to point back to this thread, but I would be extremely interested in seeing your proof of concept code. I didn't know it was possible to dynamically link dimensions at run time, but that would solve for me a number of very difficult issues I am struggling with.|||

I have seen two other ways of implementing the current day.

Never have members in the time dimension after the current date. Today it is Aug 22 so never let at time member enter the dimension after this date. With this solution you can look for the last time member in MDX by lastchild.lastchild and so on. In this case it is the ETL process and SSIS that manage the time dimension.

Another solution is to look for a measure in the cube that you know will reflect the current day or the day before the current day. Actual Sales is a good candidate if budget sales is entered for the full year in advance. In this case you can use Tail with Filter, in MDX, to look for the last non empty member. If this set works you can add Lag(MDX) och Lead(MDX) to your first named set, together with Lead(MDX).

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

In ETL, I select the min and max trx date keys (a bit more involved if multiple fact tables which I have) from the fact table and store them in a one row table called DIM_DAY_RANGE. Then use the following where clause for your DAY_DIM view

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

I need this information ("today") because I need to build up reports which show "todays" Orders, Revenue or whatever. The users should do nothing but open the report, so I need something like a set to use instead of a fixed day or something what a user has to select.

Using the approach to "just offer what you have" is OK in many cases, however what do you do if you not only have actual sales but also your planned sales? Then you have to be able to show all days (or months) of the current year or also the next year... So this will not help you much...

|||

Quote:"what do you do if you not only have actual sales but also your planned sales". Do you have a version dimension like Actual, Budget, Planned, Forecast ? Or is this in the measures like, ActualSales, BudgetSales, Planned Sales, ForecastSales?

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...

#Err results from query.

Hi,
Would you know why the following MDX returns '#Err' as values?
WITH
SET [Set1] AS '[Customers].[Country].Members'
MEMBER [Measures].[T] as '[Set1].Current.Item(0).UniqueName'
SELECT {[measures].[t]} ON COLUMNS,
[Set1] on Rows
FROM [Sales]
Thanks.Current function returns the current tuple from a set *during an iteration*.
Here, you should modify the calcualted member as following,
MEMBER [Measures].[T] AS 'Customers.CurrentMember.UniqueName'
Ohjoo Kwon
"John" <nospam> wrote in message
news:%23%23U0H68LFHA.3420@.tk2msftngp13.phx.gbl...
> Hi,
> Would you know why the following MDX returns '#Err' as values?
> WITH
> SET [Set1] AS '[Customers].[Country].Members'
> MEMBER [Measures].[T] as '[Set1].Current.Item(0).UniqueName'
> SELECT {[measures].[t]} ON COLUMNS,
> [Set1] on Rows
> FROM [Sales]
> Thanks.
>