Membership Database

I need to create a membership database that includes levels and premiums for each level. Can anyone offer any examples of how this should be done? What tables I would need and how they would be related to each other?

Thank you for any suggestions,Assuming that there are multiple premiums for each membership level, the following would be a basic approach.

Membership Level Table (Level ID, Level Name)
Premium Table (Premium ID, Premium Description, Level ID)
Members (Member ID, Level ID, First Name, Last Name, Address, City, State, Zip, Phone, Email, Date Joined)

Premium relates to Membership Level through the Level ID field, and Membership Level to Member through the Level ID field.

Lots of options, but this should get you started.

Jeff|||Thank you, this will help greatly. I just have one other question. If the databse is setup to allow multiple premiums for each level, how do we know what premiums the member received?

Thanks again,|||If the member can only receive one of a number of premiums, simply have a Premium Received field in the Member table that references the Premium ID. If the member can receive more than one premium, have a join table called Member Premiums, which will have a composite primary key of Member ID and Premium ID. This combination will always be unique, so long as the same Member cannot receive the same premium twice.

Jeff

MDX script to scope on all but the leaf levels

Hi,

I am trying to specify a scope statement on all non-leaves members of all hierarchies of a dimension (time dimension basically) so I would need to say something like this:

scope (MyMeasure, not leaves(TimeByDay));
this = (MyMeasure,timeByDay.currentHierarchy.currentmember.firstChild);
end scope;

Does anybody see a way of doing this without explicitly repeating a scope statement for each implemented hierarchy.level-above-leaf like the one below?

scope (
{[Measures].[RG Queue State],[Measures].[RQ Distinct Queue State]},
[Processed Statistics Period].[YMD].[Month of year],
[Processed Statistic Type].[Processed Statistic].[Processed Statistic Action].&[1.]);

this = ([Processed Statistics Period].[YMD].currentmember.FirstChild);

end scope;

Thanks

I'm not sure of your particular implementation, but have you considered inverting the problem. Something like:

a) Set the aggregation method for your measure to FirstChild
b) If necessary, redefine the aggregation at the leaves level:

Scope (myMeasure, leaves(TimeByDay));
this = custom aggregation;
end scope;|||using leaves in the scope statement makes the performance go waaaay down...|||Hi Zoran,

What you should be able to do is to scope your calculation on the All Member of the granuarity attribute of your dimension. So if the granularity attribute of your TimeByDay dimension is Month, then

SCOPE([MEASURES].[MYMEASURE], [TIMEBYDAY].[MONTH].[ALL]);
THIS={EXISTING([TIMEBYDAY].[MONTH].[MONTH].MEMBERS)}.ITEM(0).ITEM(0);
END SCOPE;

should do the job of returning the first month that exists with every member on every attribute of the dimension, which is what you want to do I think. Does this work for you? Is the performance ok?

Chris

MDX Help - Should be easy

I want to have a calculation that rolls up monthly closed sales and displays the total month volume no matter what dimension levels have been added.

Example:

Month Store # Sales Total Monthly Sales

April 1 10,000 50,000
April 2 10,000 50,000
April 3 30,000 50,000
May 1 20,000 70,000
May 2 20,000 70,000
May 3 30,000 70,000

This could be across multiple dimensions (ie, I could add product type, business day number, etc) and I would still want the total monthly sales to be the sum of all sales for the month for the entire company.

I have tried several things, but I do not know enough about MDX to know where to look.

Thanks for your help!!

Bob

I think this is what you are looking for:

with member [Measures].[Total Month Sales] as '([Time].currentmember, [Store].[All Stores], [Measures].[Unit Sales])'

select
{[Measures].[Unit Sales],[Measures].[Total Month Sales]} on columns,
{nonemptycrossjoin({descendants([Time].[1997],[Time].[Month])},{[Store].[Store Name].members})} on rows
from Sales

The trick is to add all the 'All' members to [Measures].[Total Month Sales]. For instance, if you have a product dimension, it should be

'([Time].currentmember, [Store].[All Stores], [Product].[All Product], [Measures].[Unit Sales])'

Hope this helps,

Santi

|||

AWESOME! Thanks for your quick answer!!

BobP

Mdx Function to get Descentants until a specific level is reached?

Hi,

I have a parent-child dimension in wich i need to analyse data only to a specific level...

Imagine that my dimension have 10 levels but i only want to get the hierarchy to reach the level number 3..

So it would be in the report like this:

Level0

Level 1

Level 2

Level 1

Level 1

Level 2

Level 3

Best Regards,

Luis Simoes

I may be wrong but I think AS does give the levels names in a P-C dimension. Unless you change the default I think they are called [Level 0], [Level 1], [Level 2], etc...

I am assuming that you can use those level names in the DESCENDANTS() function. I don't have a P-C dimension to hand so can't try it out. Let us know if it works.

-Jamie

|||

True, you get the level names in a Parent Child dimension.

However, if you just one the first 3 levels you could just use:

descendants([Dimension].[All Member Name],2, SELF_AND_BEFORE)

Santi

|||

Hey nice, i havent tought about that one... ehehehe

Best Regards,