MEMBER UNIQUE NAME

We are having some trouble with the MEMBER UNIQUE NAME property. We are using ID's as our Key Columns, and Descriptions as our Name Columns.

So when we query the cube we use something to the effect of : [Products].&[00001].&[87235].&[62511].&[0918981].

But when we ask for the MEMBER UNIQUE NAME for that row we get something completely deifferent:[Products].[00001].[CLASS DESCRIPTION].[SUBCLASS DESCRIPTION].[0918981]

Is MEMBER UNIQUE NAME the same as the 2000 version? It used to return the key for the MEMBER, but now seems to return the NAME path. Is there any properties to change this functionality. We are using the very latest SP (March).

Thanks.

Add this to your connection string: MDX Unique Name Style=3. I think this should return the key path.

Steve

MEMBER UNIQUE NAME

We are having some trouble with the MEMBER UNIQUE NAME property. We are using ID's as our Key Columns, and Descriptions as our Name Columns.

So when we query the cube we use something to the effect of : [Products].&[00001].&[87235].&[62511].&[0918981].

But when we ask for the MEMBER UNIQUE NAME for that row we get something completely deifferent:[Products].[00001].[CLASS DESCRIPTION].[SUBCLASS DESCRIPTION].[0918981]

Is MEMBER UNIQUE NAME the same as the 2000 version? It used to return the key for the MEMBER, but now seems to return the NAME path. Is there any properties to change this functionality. We are using the very latest SP (March).

Thanks.

Add this to your connection string: MDX Unique Name Style=3. I think this should return the key path.

Steve

Member security question

Hello!
In our AS2005 project we use two roles for two different group of users. First group included key account manager - each manager can see only his account sales. Second group - sales region managers - each sales region manager can see only sales in his region. We use custom clr callback method and everything works fine. Solution is based on dimension members and visual totals are enabled for both groups.

But now new requirement arrived: one manager is both key account AND sales region at the same time and therefore he should be able to see UNION of key account sales and region sales. Can this be accomplished somehow? I'm able to get the intersection, but never union. One solution is to turn visual totals off, but then other dimensions show all cube data, which is not acceptable.

Any help is appreciated.

Radim

I think you will need a third role for this person.

Basically I am guessing that your account security produces a set that looks something like:

Descendants([Account].[Account].[Account 1])

And that your Region security produces a set that looks something like:

Descendants([Region].[Region].[Region1])

This will result for an effective permission set of:

Descendants([Account].[Account].[Account 1]) * Descendants([Region].[Region].[Region1])

Which is effectively an intersection of the selected account with the selected region. Whereas, for the situation you describe, you would want an effective final set of:

{ {[Region].[Region].[All Region]} * Descendants([Account].[Account].[Account 1]) }

* { Descendants([Region].[Region].[Region1]) * { [Account].[Account].[All Account] } }

Which, instead of giving you a logical AND between the two sets (where members must be descendants of the Regions AND Account) will give you a logical OR (which will return members which are descendants of either the Account OR Region)

Hope this helps

|||Hi Darren!
That's good description of desired solution. But how could I implement such set? Right now callback function simply returns allowed members to role, but this solution doesn't seem to be that straitforward. New role should restrict cell data, instead of dimension members?

Radim|||

Hi Radim,

Since these users (given their dual role) should be able to see all members of both Account and Region, I think that the solution would have to restrict at the cell, rather than dimension, level. However, I'm not aware of a "Visual Totals" option for Cell Security, which is one of your requirements. Visual Totals behavior could conceivably be simulated via the MDX script, if that's a viable option for you. But this would entail introducing security-related logic into the cube script.

sql

Member Property length limitation

I have a member property in a shared dimension MSAS 2000. The length of column from which this member property fetches value is nvarchar(2000). But the OLAP cube is reading only 255 characters ONLY. Is this a common limitation ? If yes, what is the alternative to increase its length to grater than 255 ?

Any help will be greatly appreciated. Thanks.

If you go into the dimension editor, expand the member properties and click on the property and have a look at the advance properties - what is the data size set to? I am guessing that it may be set to 255 and you should be able to set it larger, but it's been a while since I used AS2k.|||I had checked it. It has LongWVarchar(2000).|||

I just fired up a copy of AS2k and did a quick test and got the same behaviour - member properties were truncated to 255 characters. At a guess I would say that they are only using a byte to store the length of the property, so there would be no way of working around this. Also AS2k stores all the dimension members and properties in RAM, so having very large member properties can put a lot of pressure on the RAM usage.

The only thing I could think of is to store an ID that relates back to the record in question and then use an action or something similar to link the two pieces of information together. If you setup a linked server in SQL Server you could write stored procedures that would query AS2k and you could join the cube data with a relational table before sending the results back to the client.

Member Properties

Hi

How to create a member properties in AS 2005?

Regards

In AS 2005 (SSAS2005) it is the same as attribute hierarchies. All attributes in a dimension table no longer have to be included in a user hierarchy.

If you run the dimension wizard and turn off the autocreate hierarchies you will get a dimension without hierarchies which is the same as member properties in AS2000. Or , if you use this feature(autocreate) you will get user hierarchies(the same as dimensions in AS2000) and attribute hierarchies(the same as member properties).

Still the word member properties is sometimes used for attribute relations. But this is not the same as in AS2000.

HTH

Thomas Ivarsson

|||

Actually member properties are not synonimous to the attribute hierarchies.

To model member property for particlar attribute in your dimension, you create a new attribute and than relate it to the original one.
Then you should disable attribute hierarchy, this way it wouldnt clutter your nice multidimensional model.
Once you've processed you should be able to get information from the new attribute by referring to it as memeber property from the original attribute member.

As you think of it, in many cases you have many attributes in dimension related to the key attribute. That means key attribute of the dimension. Or better say, members of the dimension key attribute have many member properties. It is good practice to disable attribute hierchies for the attributes you can access as member properties.

HTH.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Interesting answere Edward. I refer to member properties in a general BI design way. If so you can say that member properties are attributes that do not form a natural hierarchy, like the color of a product. In this case the attribute hierarchies will qualify as member properties.

You can use an attribute as a member property, in the way you describe it, only to sort another attribute and without showing it in the dimension.

If I show an attribute as an informative box on another attribute or as an attribute hierarchy is really not that important. I can show color as an information box on the leaf members in the product dimension or I can combine the attribute hierarchy color with a product user hierarchy and show this on an axis.

But thank's for the information anyway.

Regards

Thomas Ivarsson

Member login - Timeout Expired error

Hi,

I get this timeout error from the member login page. It does not happenalways. Sometimes when I enter the email and password and try to login,it will take forever and then it throws a timeout error. "Timeoutexpired. The timeout period elapsed prior to completion of theoperation or the server is not responding."

I went into our server, started the sql profiler and tried to run thelogin sproc manually from the query analyzer. It took 46 secs to finishrunning the query. I checked the profiler, there was nothing much goingon, I don't think there is any resource bottleneck.

I went back to the website and I tried to login, it worked fine. Ichecked the query analyzer again, this time it took 0 secs (or fractionof a sec?) to execute the sproc.

It looks like it takes a long time to run the sproc the "first" time,from then on it only takes less than a sec to execute the sproc. How doI solve this problem?

The users table has only 50,000 users. The sproc is only a couple of lines. The user table is indexed on userid and email.

----------
CREATE PROCEDURE [dbo].[proc_userverifylogin]
@.email varchar(100), @.pass varchar(50)
AS

IF EXISTS (SELECT * FROM users WHERE email = @.email AND pass = @.pass)
BEGIN
UPDATE users SET lastlogin = GETDATE() WHERE email = @.email
SELECT userid, fname, state FROM users WHERE email = @.email
END
----------

Thanks.

new2aspdotnet:

Hi,

It looks like it takes a long time to run the sproc the "first" time, from then on it only takes less than a sec to execute the sproc.

SQL Server will generate a execution plan for the stored procedure when it runs the first time, so it will cause a long duration. Then next times SQL can reuse the exection plan so the duration becomes shorter. However a duration of 46secs is abnormal, you can try to create a clustered index on the table, in order to speed up query.

|||Thats what everyone tells me, 46 secs is really really long. Even inthe development server it takes that long. I made sure nothing else isgoing on in the server and no one is accessing it, it still takes along time.

There are two indexes on the user table - userid and email. userid isthe primary key and created as clustered index. Do you suggest I removethe clustered index on userid and create a clustered index on email?

Thanks.|||

new2aspdotnet:

Do you suggest I remove the clustered index on userid and create a clustered index on email?

No need As your query uses "pass" and "email" as searching condition, how about create a index that covers both the 2 columns? Or you can select your query in Query Analyzer, then run Index Tuning Wizard for it.

However SQL does not always uses index(es) for query even index(es) does exist on the columns(s). If the "first" execution of the stored procedure still last a long time after tuning indexes on the table, you can take a look at the generated execution plan by set this option on:

SET SHOWPLAN_TEXT ON

If the exection plan shows SQL choose "table scan" other than "index scan", it indicates SQL generates "inefficient" plan based on current statistics of the index(es). So try to update the statistics on the index:

UPDATESTATISTICS users

For more information about statistics used by query optimization, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_72r9.asp

Member function error

I am using Oracle 9i on Windows XP.
I am trying to complete a tutorial for my Database class and have come across an error which I can't get around. I enter the following code which seems to work:

ALTER TYPE student_type REPLACE AS OBJECT
(FirstName varchar2(15),
LastName varchar2(15),
Address address_type,
Contact contact_type,
DateOfBirth date,
Nationality varchar2(15),
AttendanceMode varchar2(15),
StudentNo varchar2(15),
Userid varchar(15),
FeeStatus varchar2(25),
MEMBER FUNCTION getName RETURN varchar2,
PRAGMA RESTRICT_REFERENCES (getName, WNDS, WNPS, RNDS, RNPS)
)
/

It produces the result "Type altered."

Then I enter the following code to implement my member function:
CREATE TYPE BODY student_type IS
MEMBER FUNCTION getName RETURN VARCHAR2 IS
BEGIN
RETURN (FirstName || '' || LastName);
END;
/

(not sure if I should have the "/" at the end but thats the only way I can get it to run)

and I recieve the following error message:
CREATE TYPE BODY student_type IS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

I know I already have an object type named student_type but have been given the code to implement my member function by my teacher and assumed it would work.

Any suggestions?
Thanks in advance.
JamesYou should use
CREATE OR REPLACE TYPE BODY.....

INSTEAD OF JUST

CREATE TYPE BODY|||HI,

Enter a unique name for the database object type or modify or drop the existing object so it can be reused.

Member count limit

Hi Guys,

Is there a limit in the number of members in a dimension?

I'm having a problem in showing the other members of my dimension in the pivot table.

I've queried the dimension table and found out that the records displayed are from 1 - 32000 only. The rest were not shown in the pivot table.

Please enlighten me...

What version of Analysis Services you are using: 2000 or 2005?

But anyhow. It looks like you are running into the limitations of pivot table. Try running your query in some other client tool.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Larry is using Analysis Services 2005 along with the new Pivot Table Services.

It seems that the limitation is only present in the page field. It doesnt display all the members of a particular parent. It stops listing somewhere around 35k of members.

Any insights on this one anyone?

|||

I would guess that is being limitation of the Pivot Tables. You can try and contact Excel product support and try and post on the Excel public newsgroup. (microsoft.public.excel or microsoft.public.excel.misc)

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

sql

Member based security & aggregations

Performance question:

We are using member based security, which restricts at the lowest level of our hierarchies. When a user connects to AS we are using the CustomData attribute of the connection string, which passes in a user ID which is resolved to a keyset. If we are quering a few levels higher than the lowest level, and an aggregation has been created at the level being queried, does the engine "crawl" the hierarchy to determine if it can use the aggregation (provided the user has access to all lowest level members), or will abandon the aggregation and sum them up since it is already touching the lowest level?

If anyone has any insight, it would be much appreciated.

Thank you in advance,

John Hennesey

Depends on how you set the Visual Totals attribute on the dimension attribute permission. If it is set to False, which is the default, your totals will reflect all child values whether or not the child is available per the security setting. This helps to keep performance high in the cube and in most situations does not cause problems so long as end-users are aware of why their totals do not match the values for the members visible to them.

If you set the property to true, totals will reflect just those members available to the end user.

B.

|||

And to continue on from Bryan, visual totals is known to reduce performance. I don't know if it actually checks if a given user has access to all the children of a given member and then uses the higher level member, or if it will always add up the applicable members of the allowed set. I think it might be intersecting the allowed set and adding up the results, but I don't know for sure, this is just a guess.

Either way you are better to set your security at the highest level that you can. If you want a person to see all the cities in a given state, given them access to the state member rather than to an explicit list of cities.

|||

Bryan / Darren -

Thank you both for your quick responses - certainly not what I was expecting (or hoping for); it is a bummer because our security is set at the lowest level, but I completely understand why it works this way. Oh well.

Thanks

John Hennesey

measure using calculated member

I have fact and 2 dimensions. i have to create a mesure using calculated member.

fact is having

fkey

ckey

monthid

hits

responses

CDIM is having

Ckey

ADateID

MonthDim is having

Monthid

MonthName

now i want a measure(XYZ) to be calculated using calculated member as Count of Ckey for AdateID = '20000101' for current member of monthdim. finally when i browse thru a cube it should display some thing like below.

Monthid XYZ

200601 1000

200602 2000

Thanks in adv

If you have a measure that is simply a row count of rows in the fact table called "Measures.FactRowCount" you can create the following calculation that would count the number of rows with AdateID = '20000101' given that you had a dimension "CDIM" with "AdateID" as an attribute:

CREATE MEMBER CURRENTCUBE MEASURES.XYZ

AS

(CDIM.AdateID.[20000101],Measures.FactRowCount),

FORMAT_STRING="#,#';

HTH,

Steve

|||

I tried this but same value is repeating for all my monthdim members. i used below query

select [Measures].[XYZ] on columns,

[Month Dim].[Month Dim Hierarchy].members on rows

from [MyCube]

--

And also I changed Expressions as below but not worked.

([Month Dim].[Month Dim].currentmember,[Cdim].[adateid].[20000101], Measures.FactRowCount)

Could you please help me

Thanks in adv

|||

Open up the cube editor and look at the "Dimension Usage" tab. Are all of your dimensions related correctly to the measure group?

- Steve

|||

Steve, they are correctly related. and i want to tell you that most of my dimensions are not directly related to fact table. they are reference thru a fact(this table is a dim for main fact) table (say FD). The measure i am creating is the distinct count of FD dimension for the selected month.

hope above info helps you to understand my problem

|||

Lets take this offline. You can email me at stevepon@.microsoft.com

It would be helpful if you could send me a copy of your project files so I could better understand the relationships.

Steve

MDX: Rolling YTD

Hi,
I have been searching for a solution in MDX to create rolling
Year-to-Date calculated members.
For example:
When the current member time is June 2006, I want the total sales YTD
of last year, that
means from January 2005 till June 2005.
Thanks for any hints for a solution
RS.
ps.
My starting point was:
sum({ytd()},[Measures].[Sales])
But this formula is the sum YTD for this year. I'm searching for a
formula for the sum YTD of last year.
I've tried every combination with sum, OpeningPeriod(), lag,
ParallelPeriod().RS wrote:
> Hi,
> I have been searching for a solution in MDX to create rolling
> Year-to-Date calculated members.
>
> For example:
> When the current member time is June 2006, I want the total sales YTD
> of last year, that
> means from January 2005 till June 2005.
>
> Thanks for any hints for a solution
>
> RS.
>
> ps.
> My starting point was:
>
> sum({ytd()},[Measures].[Sales])
>
> But this formula is the sum YTD for this year. I'm searching for a
> formula for the sum YTD of last year.
>
> I've tried every combination with sum, OpeningPeriod(), lag,
> ParallelPeriod().

> I datagrep is offer the solution for most of the problems
> so visit this for ur solutions
>http://www.datagrep.com

MDX, can''t make correct totals at [(All)] level

Hi,

I'm trying to make a calculate member using this script:

Code Snippet

iif([Stores].[Store Name].CurrentMember.level is [Stores].[Store Name].[(All)],

sum(

(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

(([Stores].[Store Name].currentmember, [Measures].[S v net price budget]) <> 0)

), [Measures].[S v net price disc])),

[Measures].[S v net price disc])

but the total generated is always the same as if I made a sum for the [Measures].[S v net price disc].

I checked folowing:

Code Snippet

with member measures.test1 as sum(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

[Measures].[S v net price budget] <> 0)

, [Measures].[S v net price disc])

select

measures.test1 on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

Code Snippet

select

([Stores].[Store Name], [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

it gives the same result, but the:

Code Snippet

select

(FILTER(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price budget] <> 0), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 92 colums, and:

Code Snippet

select

(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 146 colums.

So the totals should be different.

I have been trying many different scripts and the result was always the same, so if You have any idea how to make it work, help me !!!

Thanks,

Micha?

Assuming you're using AS 2005, it may be worth explictly specifying member and level in Descendants(), rather than relying on hierarchy defaults - does that make any difference?

Descendants([Stores].[Store Name].CurrentMember, [Stores].[Store Name].[Store Name])

MDX, can''t make correct totals at [(All)] level

Hi,

I'm trying to make a calculate member using this script:

Code Snippet

iif([Stores].[Store Name].CurrentMember.level is [Stores].[Store Name].[(All)],

sum(

(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

(([Stores].[Store Name].currentmember, [Measures].[S v net price budget]) <> 0)

), [Measures].[S v net price disc])),

[Measures].[S v net price disc])

but the total generated is always the same as if I made a sum for the [Measures].[S v net price disc].

I checked folowing:

Code Snippet

with member measures.test1 as sum(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

[Measures].[S v net price budget] <> 0)

, [Measures].[S v net price disc])

select

measures.test1 on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

Code Snippet

select

([Stores].[Store Name], [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

it gives the same result, but the:

Code Snippet

select

(FILTER(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price budget] <> 0), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 92 colums, and:

Code Snippet

select

(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 146 colums.

So the totals should be different.

I have been trying many different scripts and the result was always the same, so if You have any idea how to make it work, help me !!!

Thanks,

Micha?

Assuming you're using AS 2005, it may be worth explictly specifying member and level in Descendants(), rather than relying on hierarchy defaults - does that make any difference?

Descendants([Stores].[Store Name].CurrentMember, [Stores].[Store Name].[Store Name])

MDX to get member names?

I want to get a list of member name for an attribute, without accessing the fact table, for speed.

I can write a statement like "select {Measures.SalesAmount} on columns, {CostCenter.Members} on rows from MyCube", which will give me a list of Cost Centers going down. Is there a way to get this same list, but without accessing the fact table, with the hopes that the query will execute faster?

Thanks for any assistance,

Ernie

Assume the CostCenter attribute belongs to the dimension [CCDim]

select CostCenter.members on rows

from [$CCDim]

|||

Works great.

Thanks Jeffrey!

MDX to aggregate measure over specific dimensions

Hi,

I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).

I was thinking that the solution would be to have an MDX expression of the form

([Measures].[Measure],
[D1].CurrentMember,
[D2].CurrentMember,
[D3].CurrentMember,
[D4].[(All)],
[D5].[(All)])

but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.

My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!

Thanks in advance.
James

Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
*
[Customer].[Country].[Country].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo)

You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

HTH,

Chris

|||
Hi Chris, many thanks for your reply.

This indeed worked. Going back to my previous example, I created a calculated member as:

(Root([D4]),
Root([D5]),
[Measures].[Measure])

and the measure is only sliced by dimensions D1, D2 and D3.

Thanks for your help!

James
|||

Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.

One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use

([D4].[All], [D5].[All], [Measures].[Measure])

|||
Hi, yes I thought those were my two options.

I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!

Many thanks for your help.
James

MDX Sumproduct and excel functions

Hi
Has anyone used SUMPRODUCT in a calculated member in analysis services?
Apparantly, according to MS documentation, its possible, but I can see
nothing in the books online - or on the web.
I am using the syntax below, which I would have thought would have worked.
SUMPRODUCT({[Accounts].&[123],[Accounts].&[456]},{
[Accounts].&[111],[Accounts].&[321]})
So does anyone know how to use SUMPRODUCT with AS?
Thank you for your help
JeremySince the arguments to SumProduct() are arrays, use the MDX SetToArray()
function to generate them. The second argument to SetToArray() is the
numerical value to use:
[vbcol=seagreen]
SUMPRODUCT(
SetToArray({[Accounts].&[123],[Accounts].&[456]},
[Measures].[Sales]),
SetToArray({[Accounts].&[111],[Accounts].&[321]},
[Measures].[Sales]))[vbcol=seagreen]
From SQL Server BOL>>
SetToArray
Converts one or more sets to an array for use in a user-defined
function.
Syntax
SetToArray(Set[, Set...][, Numeric Expression])
Remarks
This function converts one or more sets to an array for use in a
user-defined function. The number of dimensions in the resulting array
is the same as the number of sets specified.
The optional numeric expression can be used to provide the values in the
array cells. If omitted, the default value of the set member is used for
the array cell value.
..[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

mdx reporting

hi all,
examine the following piece :
with
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[September])'
As you can see, i summate the months January to September, september being
the current month. Now, is there a way to replace [September] with the month
that we are currently in, or perhaps by a parameter ?
Thanks in advanceKoen,
Here's an example of what I do...
MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
... I do this to get data up to the previous month. I include the Qtr
because that is how my dimensions are set up. You may have to use the
Monthname function to return the full month name to match your dimension
structure.
"Koen" wrote:
> hi all,
> examine the following piece :
> with
> member [month_list].[jan-sept] AS 'sum([month_list].[All
> month_list].[January]:[Month_list].[All month_list].[September])'
>
> As you can see, i summate the months January to September, september being
> the current month. Now, is there a way to replace [September] with the month
> that we are currently in, or perhaps by a parameter ?
> Thanks in advance|||hi,
first of all, thanks for your (quick) reply.
i tried your suggestion, and i fabricated something like this :
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[" &
datename(month,getdate()) & "])'
but it says : Cannot find dimension member ("Month_list]. ...
perhaps there's a bad quote or something ? maybe you see it..
Thanks,
"williamericnichols" wrote:
> Koen,
> Here's an example of what I do...
> MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
> 1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
> & Month(Dateadd("m",-1,Now())) & "])'
> ... I do this to get data up to the previous month. I include the Qtr
> because that is how my dimensions are set up. You may have to use the
> Monthname function to return the full month name to match your dimension
> structure.
>
> "Koen" wrote:
> > hi all,
> >
> > examine the following piece :
> >
> > with
> >
> > member [month_list].[jan-sept] AS 'sum([month_list].[All
> > month_list].[January]:[Month_list].[All month_list].[September])'
> >
> >
> > As you can see, i summate the months January to September, september being
> > the current month. Now, is there a way to replace [September] with the month
> > that we are currently in, or perhaps by a parameter ?
> >
> > Thanks in advance|||Koen,
I'm assuming you are using 2000 RS and the query builder. It sucks and I
can't wait to try out 2005 mdx builder. So with that assumption... when you
build dynamic mdx statements you must surround it in quotes insert an "=" at
the beginning.
So my full mdx statement is this...
="WITH
MEMBER TIME_DIM.[SAMEAS 2003] AS 'SUM([TIME_DIM].[2003].[Qtr
1].[1]:[TIME_DIM].[2003].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
SELECT
{ TIME_DIM.[YTD 2004], TIME_DIM.[SAMEAS 2003], [TIME_DIM].[Yr].[2004],
[TIME_DIM].[Yr].[2003], [TIME_DIM].[Yr].[2002]} ON Axis(0),
NonEmptyCrossJoin([" &
Left(Parameters!Cat_1.Value,Instr(Parameters!Cat_1.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_2.Value,Instr(Parameters!Cat_2.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_3.Value,Instr(Parameters!Cat_3.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_4.Value,Instr(Parameters!Cat_4.Value,"|")-1) &
"].Levels(1).members) ON Axis(1)
FROM [IICASE_COUNTS]
WHERE ([Measures].[Rec Count])"
... WARNING! Once you change it to a dynamic statement, the RS field list
will NOT update itself if you add\change fields. So if you need to
add/change fields, you must change it back to a static...refresh the fields
and then change it back to dynamic. I usually keep an extra copy of the
report with the static statement so I can go back and forth during debugging.
Hope this helps good luck.
"Koen" wrote:
> hi,
> first of all, thanks for your (quick) reply.
> i tried your suggestion, and i fabricated something like this :
> member [month_list].[jan-sept] AS 'sum([month_list].[All
> month_list].[January]:[Month_list].[All month_list].[" &
> datename(month,getdate()) & "])'
> but it says : Cannot find dimension member ("Month_list]. ...
> perhaps there's a bad quote or something ? maybe you see it..
>
> Thanks,
> "williamericnichols" wrote:
> > Koen,
> >
> > Here's an example of what I do...
> >
> > MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
> > 1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
> > & Month(Dateadd("m",-1,Now())) & "])'
> >
> > ... I do this to get data up to the previous month. I include the Qtr
> > because that is how my dimensions are set up. You may have to use the
> > Monthname function to return the full month name to match your dimension
> > structure.
> >
> >
> > "Koen" wrote:
> >
> > > hi all,
> > >
> > > examine the following piece :
> > >
> > > with
> > >
> > > member [month_list].[jan-sept] AS 'sum([month_list].[All
> > > month_list].[January]:[Month_list].[All month_list].[September])'
> > >
> > >
> > > As you can see, i summate the months January to September, september being
> > > the current month. Now, is there a way to replace [September] with the month
> > > that we are currently in, or perhaps by a parameter ?
> > >
> > > Thanks in advance

mdx questions

I'm going to start reading through the performance document, but in the mean time any suggestions on how to speed up the following?

with member Measures.bucket1 AS sum(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members * [Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),

NON_EMPTY_BEHAVIOR = { [FACT CUT RELEASE Count] }

SELECT NON EMPTY { [Measures].bucket1} ON COLUMNS, NON EMPTY { ([CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] } ) ON COLUMNS FROM [DW INSIGHT]) WHERE ( [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] )

Before you start optimizing this - you need to have it correct. Please remove incorrect NON_EMPTY_BEHAVIOR clause.|||

A couple of other clarifications/questions:

Are the lower members of [REL TURN HRS].[REL TURN HRS] known in advance (like 1, 2, 3, ..)? If so, filter() wouldn't be needed to select the desired range (MemberValue < 6) of members.|||

I'm new to working with Analysis Services, MDX and reporting services. So i'm doing my best to read books and learn as fast as possible, so any information i'm able to gleen from these forums is a big help. So let me thank you in advance for your input.

First off you should know i'm working on this through reporting services in creating a dataset. I find I can use both the gui and design mode to see how it writes the mdx.

First in response to Mosha suggestion about the non empty behavior, this is a left over from what i copy from the calculated member in ssas. Surprisingly when it is removed the performance drops off even further to the point of locking up Visual Studio. If I write it into the mdx like I believe it should be

member Measures.bucket1 AS sum(nonempty(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members *[Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),[FACT CUT RELEASE Count])

I get a preparing Query pop up and things pretty much get hung up again.

now your to questions, I'll answer the 2nd question first. That's the way that ssrs generated the mdx. I thought it was a bit strange. but it performed well so i left it alone.

ok 1st question, I think a little background info would help. I have a fact table with 3.5 million recs, each record has a start and end timestamp the duration of which is "REL TURN HRS". I want to eventually have a report where theses duration times can be grouped into buckets of time. 0 to < 6, >= 6 to <12 etc... The end user of the would enter a number into a parameter that would determine the size of the buckets in this case 6.

So i started to work on the mdx just using hardcoded values, the results of which I initially posted. When I started to design the cube,

I created a dimension with all the distinct values (77,000 +) of the duration times and linked that back to the fact table.

I now believe that maybe a flawed design perhaps this should have been a fact dimension?

|||The fact dimension would presumably be pretty large (3.5 million recs?), so I'm not sure that approach will perform better. Are [REL TURN HRS] rounded to integer hours, or could they be, for the purposes of bucketing (ie. if the user is only selecting buckets in multiples of hours)? In that case, wouldn't there be far fewer than 77K distinct values? Furthermore, as I mentioned earlier, in that case, you could direcltly specify the range of members for a bucket (assuming that there are no "holes" in the values), like [0]:[5], rather than applying Filter(). But if you really do need a dimension with large numbers of distinct values, maybe creating a multi-level hierarchy could help improve performance via aggregations.|||

I can see how converting to integer values would simplify things and would shink the size of the dimension. I'll look into this. However i think the end users may come back with a need to also show the value carried out to two places. But I should be able to set up the dimension with both values the detail 10.25 as one attribute and another attribute with the value of 11 and set up the muti level hierarchy off of that.

your thoughts?

|||A multi-level hierarchy (higher level being integer) could improve performance, if aggregations exist at that level and bucketing is done on integer boundaries. But if users only need to know actual values when drilling down to the fact level, then a fact dimension with drillthrough could meet that need.|||

here is what i finally did and the associated mdx, I would appreciate any critiquing thanks.

on the fact table I converted the values to whole integers, then set up a dimension with a list of the values.

On the reporting services report i have a parameter than excepts a integer value (bucket size) which is then used in the following mdx for the report

The only thing i noticed is that I probably should be using Aggregate instead of Sum below.

WITH member measures.bucket1 as sum([REL TURN HRS].[TRN HRS].&[0]:strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(@.bucket_size) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 2)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 2) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 3)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket4 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 3) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 4)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket5 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 4) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 5)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket6 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 5) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 6)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket7 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 6) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 7)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket8 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 7) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 8)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket9 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 8) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 9)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket10 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 9) + 1)) + "]"): null,[Measures].[FACT CUT RELEASE Count])

SELECT NON EMPTY{ measures.bucket1,measures.bucket2,measures.bucket3,measures.bucket4,measures.bucket5,measures.bucket6,measures.bucket7,

measures.bucket8,measures.bucket9,measures.bucket10} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].allmembers} ON ROWS

FROM [DW INSIGHT] WHERE ( {[CUSTOMER JOB].[TITLE CD].&[FST1]} )

|||

You could use Subset() to simplify the bucket definitions, like:

WITH

Member [Measures].[BucketSize] as

Val(@.bucket_size)

member measures.bucket1 as

sum(Head([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 2) + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

...

member measures.bucket10 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 9) + 1),

[Measures].[FACT CUT RELEASE Count])

mdx questions

I'm going to start reading through the performance document, but in the mean time any suggestions on how to speed up the following?

with member Measures.bucket1 AS sum(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members * [Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),

NON_EMPTY_BEHAVIOR = { [FACT CUT RELEASE Count] }

SELECT NON EMPTY { [Measures].bucket1} ON COLUMNS, NON EMPTY { ([CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] } ) ON COLUMNS FROM [DW INSIGHT]) WHERE ( [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] )

Before you start optimizing this - you need to have it correct. Please remove incorrect NON_EMPTY_BEHAVIOR clause.|||

A couple of other clarifications/questions:

Are the lower members of [REL TURN HRS].[REL TURN HRS] known in advance (like 1, 2, 3, ..)? If so, filter() wouldn't be needed to select the desired range (MemberValue < 6) of members.|||

I'm new to working with Analysis Services, MDX and reporting services. So i'm doing my best to read books and learn as fast as possible, so any information i'm able to gleen from these forums is a big help. So let me thank you in advance for your input.

First off you should know i'm working on this through reporting services in creating a dataset. I find I can use both the gui and design mode to see how it writes the mdx.

First in response to Mosha suggestion about the non empty behavior, this is a left over from what i copy from the calculated member in ssas. Surprisingly when it is removed the performance drops off even further to the point of locking up Visual Studio. If I write it into the mdx like I believe it should be

member Measures.bucket1 AS sum(nonempty(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members *[Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),[FACT CUT RELEASE Count])

I get a preparing Query pop up and things pretty much get hung up again.

now your to questions, I'll answer the 2nd question first. That's the way that ssrs generated the mdx. I thought it was a bit strange. but it performed well so i left it alone.

ok 1st question, I think a little background info would help. I have a fact table with 3.5 million recs, each record has a start and end timestamp the duration of which is "REL TURN HRS". I want to eventually have a report where theses duration times can be grouped into buckets of time. 0 to < 6, >= 6 to <12 etc... The end user of the would enter a number into a parameter that would determine the size of the buckets in this case 6.

So i started to work on the mdx just using hardcoded values, the results of which I initially posted. When I started to design the cube,

I created a dimension with all the distinct values (77,000 +) of the duration times and linked that back to the fact table.

I now believe that maybe a flawed design perhaps this should have been a fact dimension?

|||The fact dimension would presumably be pretty large (3.5 million recs?), so I'm not sure that approach will perform better. Are [REL TURN HRS] rounded to integer hours, or could they be, for the purposes of bucketing (ie. if the user is only selecting buckets in multiples of hours)? In that case, wouldn't there be far fewer than 77K distinct values? Furthermore, as I mentioned earlier, in that case, you could direcltly specify the range of members for a bucket (assuming that there are no "holes" in the values), like [0]:[5], rather than applying Filter(). But if you really do need a dimension with large numbers of distinct values, maybe creating a multi-level hierarchy could help improve performance via aggregations.|||

I can see how converting to integer values would simplify things and would shink the size of the dimension. I'll look into this. However i think the end users may come back with a need to also show the value carried out to two places. But I should be able to set up the dimension with both values the detail 10.25 as one attribute and another attribute with the value of 11 and set up the muti level hierarchy off of that.

your thoughts?

|||A multi-level hierarchy (higher level being integer) could improve performance, if aggregations exist at that level and bucketing is done on integer boundaries. But if users only need to know actual values when drilling down to the fact level, then a fact dimension with drillthrough could meet that need.|||

here is what i finally did and the associated mdx, I would appreciate any critiquing thanks.

on the fact table I converted the values to whole integers, then set up a dimension with a list of the values.

On the reporting services report i have a parameter than excepts a integer value (bucket size) which is then used in the following mdx for the report

The only thing i noticed is that I probably should be using Aggregate instead of Sum below.

WITH member measures.bucket1 as sum([REL TURN HRS].[TRN HRS].&[0]:strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(@.bucket_size) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 2)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 2) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 3)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket4 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 3) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 4)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket5 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 4) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 5)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket6 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 5) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 6)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket7 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 6) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 7)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket8 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 7) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 8)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket9 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 8) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 9)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket10 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 9) + 1)) + "]"): null,[Measures].[FACT CUT RELEASE Count])

SELECT NON EMPTY{ measures.bucket1,measures.bucket2,measures.bucket3,measures.bucket4,measures.bucket5,measures.bucket6,measures.bucket7,

measures.bucket8,measures.bucket9,measures.bucket10} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].allmembers} ON ROWS

FROM [DW INSIGHT] WHERE ( {[CUSTOMER JOB].[TITLE CD].&[FST1]} )

|||

You could use Subset() to simplify the bucket definitions, like:

WITH

Member [Measures].[BucketSize] as

Val(@.bucket_size)

member measures.bucket1 as

sum(Head([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 2) + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

...

member measures.bucket10 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 9) + 1),

[Measures].[FACT CUT RELEASE Count])

MDX Question - Counting members in a Dimension with a "dateCreated" attirbute

Hello,

I'm fairly new to MDX and would like to add a calculated member:

In a dimension called DimCustomers I have a field called dateJoined which specifies when. Customers have the ability to create comments.

I have created a Fact table called FactComments, which has a id, customerid, timeid

What I would like is create a calculated member which uses the dateJoined field as a way to accumulate how many members had joined at given time.

e.g.

2005 - commentCount - memeberCountat2005 - comments/member-ratio
2006 - commentCount - memberCountin2006 - comments/member-ratio

The question is, is this possible with MDX or do i have to look at the data transferred to the DW and create a field called memberCount (or something like that... )

hope anyone can help.The PeriodsToDate MDX function seems like it would work for your scenario.