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

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

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 102))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESC
Joe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>
|||The following is copied from the SQL 2000 Books online. "WITH" is part of
the example.
Am I missing something?
Thanks for you help
George
Topic last updated -- July 2003
Returns the median value of a numeric expression evaluated over a set.
Syntax
Median(Set[, Numeric Expression])
Remarks
The Median function returns the median value of a numeric expression that is
specified in Numeric Expression and evaluated over a set specified in
Set. The median value is the middle value in a set of ordered numbers
(unlike the mean value, which is the sum of a set of numbers divided by the
count of numbers in the set). The median value is determined by choosing the
smallest value such that at least half of the values in the set are no
greater than the chosen value. If the number of values within the set is
odd, the median value corresponds to a single value. If the number of values
within the set is even, the median value corresponds to the sum of the two
middle values divided by two.
Example
The following example, a calculated member that is executed against the
Sales cube of the FoodMart 2000 database, returns the median value of the
Unit Sales measure for the children of the Juice member in the Product
dimension:
WITH MEMBER [Measures].[MedianJuiceUnitSales] AS
'MEDIAN(Product.Juice.CHILDREN, Measures.[Unit Sales])'
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:Oeti76luEHA.3152@.TK2MSFTNGP14.phx.gbl...
> Joe Celko always writes standard ANSI SQL. Existing products more or less
> comply to the standard. In version 2000, T-SQL language used by SQL Server
> does not support WITH clause yet. Check how to calculate the median in
> T-SQL
> at http://www.aspfaq.com/show.asp?id=2506.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "george collins" <george@.nospan.com> wrote in message
> news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> perfectly,
> 102))
>
begin 666 update_topic.gif
M1TE&.#EA$@.`6`/<`````````A ``_P!"0@."$A #_`$*$A(0`A(2$`(2$A(2$
M_\;&QO\``/__`/______________________________________________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_____________________RP`````$@.`6```(? `="!188*#!@.PX*$ARH$.'"
MA \=,H384"+#!046:-RHD6&!CQ@.3?E2XP$')@.P4K"CQYDF!(E0IBQEP8$B+"
M!0ILAI3)<V7.@.34EXC1XDJ=,GT0M(@.4JT.A,DS]7*H6:U('3GT.9*LTJU:K3
3I5TM<C4Y=2S'LQRC>KUJ-" `.P``
`
end
|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
sql

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 102))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESCJoe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>|||The following is copied from the SQL 2000 Books online. "WITH" is part of
the example.
Am I missing something?
Thanks for you help
George
Topic last updated -- July 2003
Returns the median value of a numeric expression evaluated over a set.
Syntax
Median(«Set»[, «Numeric Expression»])
Remarks
The Median function returns the median value of a numeric expression that is
specified in «Numeric Expression» and evaluated over a set specified in
«Set». The median value is the middle value in a set of ordered numbers
(unlike the mean value, which is the sum of a set of numbers divided by the
count of numbers in the set). The median value is determined by choosing the
smallest value such that at least half of the values in the set are no
greater than the chosen value. If the number of values within the set is
odd, the median value corresponds to a single value. If the number of values
within the set is even, the median value corresponds to the sum of the two
middle values divided by two.
Example
The following example, a calculated member that is executed against the
Sales cube of the FoodMart 2000 database, returns the median value of the
Unit Sales measure for the children of the Juice member in the Product
dimension:
WITH MEMBER [Measures].[MedianJuiceUnitSales] AS
'MEDIAN(Product.Juice.CHILDREN, Measures.[Unit Sales])'
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:Oeti76luEHA.3152@.TK2MSFTNGP14.phx.gbl...
> Joe Celko always writes standard ANSI SQL. Existing products more or less
> comply to the standard. In version 2000, T-SQL language used by SQL Server
> does not support WITH clause yet. Check how to calculate the median in
> T-SQL
> at http://www.aspfaq.com/show.asp?id=2506.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "george collins" <george@.nospan.com> wrote in message
> news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
>> Can someone tell me where I am blowing it, I get
>> Server: Msg 156, Level 15, State 1, Line 1
>> Incorrect syntax near the keyword 'WITH'.
>> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
>> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
>> I am trying to get the min, max, avg and MEDIAN of sales grouped by
>> customer, dollars in decending order.
>> Without the WITH MEMBER, the statement geives me min, max and avg
> perfectly,
>> HELP what am i missing out on here.
>> Thanks so much for you insight.
>> SQL 2000
>> George Collins
>> WITH MEMBER [INV History].[INV HIST Selling Price] as
>> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
>> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
>> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
>> Price])
>> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
>> AVG([INV HIST Selling Price]) AS [Avg]
>> FROM [INV History]
>> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
> 102))
>> GROUP BY [INV HIST Cust Name]
>> ORDER BY SUM([INV HIST Selling Price]) DESC
>>
>
begin 666 update_topic.gif
M1TE&.#EA$@.`6`/<`````````A ``_P!"0@."$A #_`$*$A(0`A(2$`(2$A(2$
M_\;&QO\``/__`/______________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M_____________________RP`````$@.`6```(? `="!188*#!@.PX*$ARH$.'"
MA \=,H384"+#!046:-RHD6&!CQ@.3?E2XP$')@.P4K"CQYDF!(E0IBQEP8$B+"
M!0ILAI3)<V7.@.34EXC1XDJ=,GT0M(@.4JT.A,DS]7*H6:U('3GT.9*LTJU:K3
3I5TM<C4Y=2S'LQRC>KUJ-" `.P``
`
end|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> Am I missing something?
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Pr
ice,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIS
T Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 10
2))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESCJoe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS
Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV H
IST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS &
#91;Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Measuring Bandwidth used by SQL server

Does anyone know how one can measure the network bandwidth used by sql
server on a per database level? Is there any 3rd party tools available that
anyone is aware of. Any ideas would be appreciated.
Thanks,
-RyanMy 2 cents:
I don't think you're going to be able to measure it on a db level. However,
to measure sql server's bandwidth you'd need a network sniffer, isolate the
sql server traffic (be careful of what network libraries you're using, if
only TCP/IP then count packets on TCP port 1433) and do some accounting...
HTH
Argenis
"Ryan" <ryanz(nospam)@.nospamiqmetrix.com> wrote in message
news:OfcaPQ89DHA.3792@.TK2MSFTNGP09.phx.gbl...
> Does anyone know how one can measure the network bandwidth used by sql
> server on a per database level? Is there any 3rd party tools available
that
> anyone is aware of. Any ideas would be appreciated.
> Thanks,
> -Ryan
>sql

Measuring Bandwidth used by SQL server

Does anyone know how one can measure the network bandwidth used by sql
server on a per database level? Is there any 3rd party tools available that
anyone is aware of. Any ideas would be appreciated.
Thanks,
-RyanMy 2 cents:
I don't think you're going to be able to measure it on a db level. However,
to measure sql server's bandwidth you'd need a network sniffer, isolate the
sql server traffic (be careful of what network libraries you're using, if
only TCP/IP then count packets on TCP port 1433) and do some accounting...
HTH
Argenis
"Ryan" <ryanz(nospam)@.nospamiqmetrix.com> wrote in message
news:OfcaPQ89DHA.3792@.TK2MSFTNGP09.phx.gbl...
> Does anyone know how one can measure the network bandwidth used by sql
> server on a per database level? Is there any 3rd party tools available
that
> anyone is aware of. Any ideas would be appreciated.
> Thanks,
> -Ryan
>

MdxScript error when processing a cube

Im getting an error while processing my cube:

The level '<level>' object was not found in the cube when the string, <fact>, was parsed.

Do you guys know why this could be happening?

Thanks,

-E

Maybe an error in your MDX statments.

Check the MDX script you have in Calculations Tab of SSAS...

I hope helped you!

regards!

MDX: Getting the first child at a lower level

I'm trying to get the first day in a specific week or month or year. I've tried with HEAD, but the following measure returns #Error for every row.

Isn't HEAD returning a member ?

What am I doing wrong ? I have tried TopCount too.

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].currentmember,[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

where ([Calendar].[Fiscal].[Month].[2007-02-26])

You are using hierarchy [Calendar].[Fiscal] in Axis 0 and in WHERE clause. You can use same hierarchy just in one place.

So you should do

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].[Month].[2007-02-26],[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

And: Head returns set, not member.

Vidas Matelis

|||

Thanks Vidas,

How do I get the first member of the set ?

|||

Alan,

I pointed about member versus set as head function can return more than 1 member if specified, so it returns set. This will work without you actually worrying if this is set or tuple or member and that is nice feature of SSAS 2005.

Function .Item(0) gets first tuple from set or first member from tuple.

Vidas Matelis

|||That did it ! Many thanks.

MDX: combining SUM and Existing

Hi,
I have problem summing values in leaf level and total level.
Lets say, I would like to to get orders in dealer price for each selected
product as well as sum for selected products only. As example, this MDX
could demonstrate my case in Adventure Works database:

with member [Dealer Price Orders] as

SUM(
Existing ([Product].[Product Categories].[Product]),
iif([Product].[Dealer Price].MemberValue = 0 , null,
[Order Quantity]*[Product].[Dealer Price].MemberValue)
)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product
Categories].[All Products]})})
ON COLUMNS
FROM (SELECT ({[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure
Works])
WHERE ([Measures].[Dealer Price Orders])

Unfortunately, this MDX returns Dealer Price Sales for all Products, but not
for Selected [Accessories] and [Bikes]. VisualTotals function does not help
in this case. Does anyone has a solution how to rework my calculated member?

Ramunas Balukonis

on 2005 sp2, I get a blank resultset from your test query - so a lot of people probably can't use it to give you an answer.

are you on something like sp1 or ssas 2000?

|||

Unfortunately the Existing statement currently doesn't take into account subselect restrictions.

One possibility is if you can define your calculation on leaf cells, then subselect visualtotals can be achieved. Here is one example, note that I changed [Product] to [Product Name] which matches my version of Adventure Works.

with cell calculation X for '([Product].[Product Categories].[Product Name].members, [Order Quantity])' as

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Order Quantity])

|||

Here is another way which uses calculated member instead of cell calculation. It uses the fact that query scope named sets are resolved using subselect restrictions.

with set S as [Product].[Product Categories].[Product Name].members

member [Dealer Price Orders] as

SUM(

existing(S),

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Dealer Price Orders])

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 SCOPING at ALL level

Hi all,

I am trying to Translate the case statement to using SCOPE but the scoping at the ALL level does not seem to work right. It seems to set all the values for all other measures at the ALL level to the same value as well.

But I only want it to set the value for [MEASURES].[Def OTS] when I am at the [Dim OTS].[ALL] level.

What am I doing wrong?

thank you

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null

When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])

When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;
SCOPE ([Dim OTS].[All]);
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

I don't think you're specifying the measure for your assignments which would mean that you're assigning to all measures in the cube. So get rid of the keyword THIS everywhere and specify the measure. Or just surround the whole thing with "SCOPE ([Measures].[Def OTS])".

Also, I'm not sure what Dim OTS looks like in terms of attributes and relationships, but you might try changing:

SCOPE ([Dim OTS].[All]);
to

SCOPE (Root([Dim OTS]));

If you've got multiple attributes, the latter will probably work more like you're expecting.|||

1. could this be related to this post ?

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1205362&SiteID=17

2. the dimension [Dim OTS] only has one attribute and no user hierarchy. The physical table itself has only one column.

3. So you mean I can do something like this below ?

Thanks

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE (ROOT([Dim OTS]));

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||looks good... does it work like you expect?|||

yes! it works great.

thank you

I was wondering if this also work if I included [MEASURES].[Closing Balance]

in the scope statement since I only care about this one measure at these levels

especially when the measure is NOT empty (I do not care about when this measure is empty) ?

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE ([MEASURES].[Closing Balance], ROOT([Dim OTS]));

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([MEASURES].[Closing Balance],[Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||

What you've done in that last calc script you posted is override the measure it applies to, I believe. What you need to do is specify two measures in the scope. The outermost scope should be:

scope({[Measures].[Def OTS], [Measures].[Closing Balance]})

Then I think you'll get what you want assuming you want the assignments to impact both measures.

|||

yes, this works, thank you. The final statement looks like this.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS],[MEASURES].[Closing Balance]);

SCOPE ([ROOT([Dim OTS]);

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||

sorry, check the wrong cube.

No that scoping statement will not work because 2 measures appearing together (same measure dimension).

BIDS was complaining when I deploy.

so the working code is this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE ([ROOT([Dim OTS]);

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

MDX query to filter out members starting with a particular alphabet

Hi,

I would like to come up with an MDX query such that the query works on a subset of the members present at a particular level of a dimension. A filter would do but the subset should have all the members whose name start with alphabet falling in the range 'A' - 'K'. Is there a way to have a regular expression in the filter of the MDX query.
Do help me out on this with your suggestions.

cheers,
Arun

I would you reccomend to use pure MDX. It is the quickest method. It use indexes on hierarchies. If you use VBA fubnctions or another stuff SSAS schuld mack full scan of hierarchy and the query would be slower.

Filter([Dimension].[Hierarchy].[Level].members, ([Dimension].[Hierarchy].CurrentMember.Name >= "A" and [Dimension].[Hierarchy].CurrentMember.Name < "L"))

|||Thanks a bunch Vladimir for the timeous reply
Is it also possible to filter the members by count ? I mean can I get the first 10 members at a level in one query followed by the next 10 in another query and so on.

Thanks in advance.

Arun
|||

Maybe like this:

BottomCount( TopCount( {YourSet}, X ), 10 )

Where you control X each time you query, e.g.

BottomCount( TopCount( {YourSet}, 10 ), 10 )

BottomCount( TopCount( {YourSet}, 20 ), 10 )

BottomCount( TopCount( {YourSet}, 30 ), 10 )

Best regards

- Jens

|||Hi Jensch,

Thanks a lot. It was quite handy. Much appreciated.

cheers,
Arun

MDX query to filter out members starting with a particular alphabet

Hi,

I would like to come up with an MDX query such that the query works on a subset of the members present at a particular level of a dimension. A filter would do but the subset should have all the members whose name start with alphabet falling in the range 'A' - 'K'. Is there a way to have a regular expression in the filter of the MDX query.
Do help me out on this with your suggestions.

cheers,
Arun

I would you reccomend to use pure MDX. It is the quickest method. It use indexes on hierarchies. If you use VBA fubnctions or another stuff SSAS schuld mack full scan of hierarchy and the query would be slower.

Filter([Dimension].[Hierarchy].[Level].members, ([Dimension].[Hierarchy].CurrentMember.Name >= "A" and [Dimension].[Hierarchy].CurrentMember.Name < "L"))

|||Thanks a bunch Vladimir for the timeous reply
Is it also possible to filter the members by count ? I mean can I get the first 10 members at a level in one query followed by the next 10 in another query and so on.

Thanks in advance.

Arun
|||

Maybe like this:

BottomCount( TopCount( {YourSet}, X ), 10 )

Where you control X each time you query, e.g.

BottomCount( TopCount( {YourSet}, 10 ), 10 )

BottomCount( TopCount( {YourSet}, 20 ), 10 )

BottomCount( TopCount( {YourSet}, 30 ), 10 )

Best regards

- Jens

|||Hi Jensch,

Thanks a lot. It was quite handy. Much appreciated.

cheers,
Arun

MDX Hierarchy help

I have two calendar hierarchies in a 2005 SSAS cube. I have to make some calculations based on the date level, but of course, my calculated measures dont work when the wrong hierarchy is chosen.

I have tried to look at the .hierarchy and hierarchy() function but am unable to really understand how they work and I haven't been terribly successful looking online for info on them.

Has anyone had a similar issue? How did you test for the current hierarchy?

Here's an Adventure Works example, when either the Calendar or Fiscal hierarchy is selected on rows:

>>

With Member [Measures].[DateHierarchy] as

Axis(1).Item(0).Item(0).Hierarchy.Name

select {[Measures].[Order Quantity],

[Measures].[DateHierarchy]} on 0,

Non Empty [Date].[Calendar].[Calendar Year] on 1

from [Adventure Works]

-

Order Quantity DateHierarchy
CY 2001 11,848 Calendar
CY 2002 60,918 Calendar
CY 2003 124,615 Calendar
CY 2004 77,395 Calendar

>>

Another approach is to test whether the current member of each hierarchy is the default member (assuming that only 1 hierarchy has been navigated).

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,