Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis FoutoWhat edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
--
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>|||The Book SQL Server 2000 Administrators Companion has a
rough guide formula for the amount memory to be used by
SQL Server which goes something like
(500K * The Number of Users) + (Database prcoess memory)
The data process memory is to do with the data and log
cache sizes although unless you know a lot more on what
your going to have on it is going to be a bit hard to
calculate.
Personally I would do as Keith sugested, get as much of it
as you can, but remember stealing it is again the law in
most counties.
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>Hi,
> How do i calculate de right memory for my MSSQL
Server 2000, is there
>any formula?
>Thanks
>Luis Fouto
>
>.
>

Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis FoutoWhat edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>sql

Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis Fouto
What edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>
sql

Median query with Group by clause

Hi,

I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
difference in time in minutes.I want to calculate Median(diff) group
by pno,ptno...using a sql query for SQL server...

Any help is greatly appreciated..

Thanks

AJ"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0404211014.79b1e842@.posting.google.c om...
> Hi,
> I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
> difference in time in minutes.I want to calculate Median(diff) group
> by pno,ptno...using a sql query for SQL server...
> Any help is greatly appreciated..
> Thanks
> AJ

http://groups.google.com/groups?hl=...80%40ietsmet.nl

Simon

Median Calculation - Analysis Services

A really simple question,,,, I need to calculate the median of a
measure. I'm having a tough time to figure out which dimension to use.
If If I have 4 dimensions, Date, region, site, and process code and I
want to get the median for the measure number of days, what do I use to
define the set?
median ([Process].AllMembers,measures.[No Of Days]) this is not working,
but at least the syntax was clean
standard median prompt
Median (Set[, Numeric Expression])
As I select different subsets of region, site and process code I
obviously want the median of No of days to be dynamic.
Stuck here and waiting anxiously for some bright person to shed the
light!!!
thanks,,,
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I would try the sqlserver.olap group with this one. This is more the
relational engine area.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Peter Weiler" <pweiler@.rogers.com> wrote in message
news:eLlFlRDIFHA.572@.tk2msftngp13.phx.gbl...
> A really simple question,,,, I need to calculate the median of a
> measure. I'm having a tough time to figure out which dimension to use.
> If If I have 4 dimensions, Date, region, site, and process code and I
> want to get the median for the measure number of days, what do I use to
> define the set?
> median ([Process].AllMembers,measures.[No Of Days]) this is not working,
> but at least the syntax was clean
> standard median prompt
> Median (Set[, Numeric Expression])
> As I select different subsets of region, site and process code I
> obviously want the median of No of days to be dynamic.
> Stuck here and waiting anxiously for some bright person to shed the
> light!!!
> thanks,,,
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Median calculation

I want to calculate the MEDIAN time interval between the two date field. In an OLAP cube one of the dimension consists of the two date fields as well as Interval(days) field (calculated at the data source view). How to create a Measure in the OLAP cube to calculate the MEDIAN Interval?

MEDIAN( <<Set>>[, <<Numeric Expression>>])

Since I'm not sure of the details your dimension, here's an Adventure Works sample query, for the Median duration in days of a Promotion:

>>

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Promotion].Properties("Start Date", TYPED),

[Promotion].[Promotion].Properties("End Date", TYPED)))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

MedPromoDays
All Promotions 83.5
Discontinued Product 53
Excess Inventory 61
New Product 91
No Discount 1309
Seasonal Discount 30
Volume Discount 1095

>>

|||

And to slightly change Deepak's query to be more efficient:

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Promotion].[End Date].MemberValue))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

Median Calc

I'm trying to use the following to calculate the median by year for the fiel
d
"labor_rate". This should calculate the median for any year where the count
is ODD. I haven't started on the "ELSE" part to calculate when the counts
are EVEN.
Anyway, the following doesn't work. I get an "incorrect syntax" error
message near line 3 and it doesn't like my order by clause either.
Any help would be greatly appreciated. Thanks.
SELECT [YYYY], median from
(SELECT [YYYY]
(SELECT TOP 1 [LABOR_RATE] FROM
(SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
order by labor_rate) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC))
as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
CraigThere is a whole chapter on Medians in SQL FOR SMARTIES, with several
different methods.
If you have SQL-2005, you can use the row numbering to create an
ascending column and a descendng column with an OVER() clause.
The median is the row wher these two columns:
1) Are equal to each other (odd number of rows)
2) The average of the columns where they differ by one (even number of
rows) .
I have not tried this yet; I am haivng a XXXXX of time getting 2005 on
my machines for some reason.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Craig,
You're missing a comma after [YYYY] in line 2. That's why the
error is in line 3, where the ( is, because that's where the error
becomes apparent.
I didn't look further.
Steve Kass
Drew University
Craig wrote:

>I'm trying to use the following to calculate the median by year for the fie
ld
>"labor_rate". This should calculate the median for any year where the coun
t
>is ODD. I haven't started on the "ELSE" part to calculate when the counts
>are EVEN.
>Anyway, the following doesn't work. I get an "incorrect syntax" error
>message near line 3 and it doesn't like my order by clause either.
>Any help would be greatly appreciated. Thanks.
>
>SELECT [YYYY], median from
>(SELECT [YYYY]
>(SELECT TOP 1 [LABOR_RATE] FROM
> (SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
> FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
> order by labor_rate) sub
>WHERE [YYYY] = z.[YYYY]
>group by [YYYY], [LABOR_RATE]
>having COUNT(*) % 2 <> 1
>ORDER BY [LABOR_RATE] DESC))
>as median
>FROM [dbo].[tbl_Data] z
>group by [YYYY], median
>order by [YYYY]
>
>|||Thanks. That cleared up the error. But one error is left: There is
incorrect syntax in the "From" line...can't figure it out.
Any help would be greatly appreciated. Thanks.
--
Craig
"Steve Kass" wrote:

> Craig,
> You're missing a comma after [YYYY] in line 2. That's why the
> error is in line 3, where the ( is, because that's where the error
> becomes apparent.
> I didn't look further.
> Steve Kass
> Drew University
> Craig wrote:
>
>|||...it's the last "From" line, FYI..
--
Craig
"Craig" wrote:
> Thanks. That cleared up the error. But one error is left: There is
> incorrect syntax in the "From" line...can't figure it out.
> Any help would be greatly appreciated. Thanks.
> --
> Craig
>
> "Steve Kass" wrote:
>|||Craig,
If you format the code more clearly, you'll see:
SELECT [YYYY], median from (
SELECT
[YYYY],
(
SELECT TOP 1
[LABOR_RATE]
FROM (
SELECT TOP 50 PERCENT
[LABOR_RATE],
[YYYY]
FROM [dbo].[tbl_Data]
WHERE [YYYY] = z.[YYYY]
order by labor_rate
) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC
)
) as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
Note what "as median" applies to. It's the name of a table,
not the name of a column, as you probably intend. This
query currently says "select stuff from T from U", basically.
Get rid of the ) before "as median", and add it before "z",
and get into the habit of reading your code, not just moving around
symbols until it stops generating syntax errors.
SK
Craig wrote:

>...it's the last "From" line, FYI..
>

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 Year to Date

I have a date heirarchy like this

dim date

Year

Quarter

Month

When i calculate YTD - 1, i get an error.. I read on the posts here that Parallel Period with YTD does not work properly..is it true?

SUM(YTD(ParallelPeriod([Dim Date].[Date].[Year],1, [Dim Date].[Date].CurrentMember)),[Measures].[SalesVolume])

Change the time dimension to your needs and try:

Gets YearToCurrentDate

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Ano],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

regards!

|||thanx Pedro..it worked

|||

YES!! :-)

mark your answer as resolved!

|||

mark your answer as resolved!

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.

In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes

Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.

In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes

Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

mdx question

i have a mdx in which in am using "sum" function to calculate WTD, MTD, YTD
etc
when i change i to "avg" function it does not give any difference in numbers
Is the "avg" being used - ?
select 1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'sum(time.[20040430]:time.[200
40430])'
member [time].[wktd] as 'sum(time.[20040426]:time.[2004043
0])'
member [time].[monthtd] as
'sum(openingperiod([day],ancestor([20040430],[month])):time.[
;20040430])'
member [time].[lastmonth] as 'sum(time.[20040301]:time.[2004
0331])'
member [time].[ytd] as 'sum(time.[20040301]:time.[20040430
])'
member [measures].[00] as '[measures].[ECH_Acw_Time]'
member [measures].[15] as '[measures].[inbound_AHT]'
member [measures].[44] as '[measures].[inbound_contacts_per_
hour]'
member [measures].[1003] as '[measures].[inbound_ACW]'
member [measures].[1004] as '[measures].[inbound_ATT]'
member [measures].[1006] as '[measures].[inbound_hold]'
member [measures].[1007] as '
iif([measures].[ECH_InCall]=0,0,[measures].[CAS_Ic_C]/[m
easures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measur
es].[44],
[measures].[1003], [measures].[1004], [measures].[
1006],
[measures].[1007]} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")
-- MDX with avg
select 1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'avg(time.[20040430]:time.[200
40430])'
member [time].[wktd] as 'avg(time.[20040426]:time.[2004043
0])'
member [time].[monthtd] as
'avg(openingperiod([day],ancestor([20040430],[month])):time.[
;20040430])'
member [time].[lastmonth] as 'avg(time.[20040301]:time.[2004
0331])'
member [time].[ytd] as 'avg(time.[20040301]:time.[20040430
])'
member [measures].[00] as '[measures].[ECH_Acw_Time]'
member [measures].[15] as '[measures].[inbound_AHT]'
member [measures].[44] as '[measures].[inbound_contacts_per_
hour]'
member [measures].[1003] as '[measures].[inbound_ACW]'
member [measures].[1004] as '[measures].[inbound_ATT]'
member [measures].[1006] as '[measures].[inbound_hold]'
member [measures].[1007] as '
iif([measures].[ECH_InCall]=0,0,[measures].[CAS_Ic_C]/[m
easures].[ECH_InCall]*100.00)'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[00], [measures].[15], [measur
es].[44],
[measures].[1003], [measures].[1004], [measures].[
1006],
[measures].[1007]} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")Can you give an example of a Foodmart MDX query that illustrates your
issue? This query works as expected - the Sum (107,551) is 5 times the
Avg (21,510.20):
[vbcol=seagreen]
With Member [Time].[ThisMonth] as
'Sum([Time].[1997].[Q2].[5]:[Time].[1997].[Q2].&
#91;5])'
Member [Time].[SumYTD] as
'Sum([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].&
#91;5])'
Member [Time].[AvgYTD] as
'Avg([Time].[1997].[Q1].[1]:[Time].[1997].[Q2].&
#91;5])'
Set [TimeSet] as
'{[Time].[ThisMonth], [Time].[SumYTD], [Time].[
AvgYTD]}'
Select {[Measures].[Unit Sales]} on columns,
{[Time].[1997].[Q1].[1]:[Time].[1997].[Q2].
[5],[TimeSet]} on rows
from Sales[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 Question

I have a cube that I have to calculate a 100 Weight for each Measure. Currently I have created Calculated Members to the tune of what you see below:

CREATEMEMBERCURRENTCUBE.[MEASURES].[AccrualCostCWT]

AS (IIF(([Measures].[Accrual Cost]/[Measures].[Lbs]) = 0, 0,([Measures].[Accrual Cost]/[Measures].[Lbs])*100)),

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Lbs] },

VISIBLE = 1;

CREATEMEMBERCURRENTCUBE.[MEASURES].[BrokerCostCWT]

AS (IIF(([Measures].[Broker Cost]/[Measures].[Lbs]) = 0, 0, ([Measures].[Broker Cost]/[Measures].[Lbs])*100)),

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Lbs] },

VISIBLE = 1;

What I would like to do is create an MDX Statement that will calculate the 100 Weight (CWT) for each measure and post it next to the measure. I am sure that there is a way to do this but I am not good enough at MDX to determine what I need to do.

This is running on SQL Server 2005 service pack 2 with the latest critical updates.

Sorry, but I don't understand the "post it next to the measure" part? You just want to cells next to each other in the cell set returned by a query?

Thanks,
Bryan

|||

Thanks for wanting clarification. Here is what I am looking for.

I have a measure with a dollar amount such as accrualCost, brokerCost, etc. (20 Columns like this)

I also have a measure called LBS, I need a way to do the CWT without creating a number of Members. If I could just have one member to work this it would be great.

I would like to see it like either of the following ways.

Amount CWT accrualCost 48,247 3.7 brokerCost 29,297 4.2 accrualCost brokerCost Amount 48,247 29,297 CWT 3.7 4.2

Let me know if you need further details.

Thanks,

Scott

|||

I think I'm starting to see where you're going with this. Let me ask, where is the Amount measure coming from? When you say "Amount" are you actually referring the value of whatever measure you are referencing?

B.

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.

In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes

Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

MDX Question

I have a cube that I have to calculate a 100 Weight for each Measure. Currently I have created Calculated Members to the tune of what you see below:

CREATE MEMBER CURRENTCUBE.[MEASURES].[AccrualCostCWT]

AS (IIF(([Measures].[Accrual Cost]/[Measures].[Lbs]) = 0, 0,([Measures].[Accrual Cost]/[Measures].[Lbs])*100)),

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Lbs] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[BrokerCostCWT]

AS (IIF(([Measures].[Broker Cost]/[Measures].[Lbs]) = 0, 0, ([Measures].[Broker Cost]/[Measures].[Lbs])*100)),

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Lbs] },

VISIBLE = 1;

What I would like to do is create an MDX Statement that will calculate the 100 Weight (CWT) for each measure and post it next to the measure. I am sure that there is a way to do this but I am not good enough at MDX to determine what I need to do.

This is running on SQL Server 2005 service pack 2 with the latest critical updates.

Sorry, but I don't understand the "post it next to the measure" part? You just want to cells next to each other in the cell set returned by a query?

Thanks,
Bryan

|||

Thanks for wanting clarification. Here is what I am looking for.

I have a measure with a dollar amount such as accrualCost, brokerCost, etc. (20 Columns like this)

I also have a measure called LBS, I need a way to do the CWT without creating a number of Members. If I could just have one member to work this it would be great.

I would like to see it like either of the following ways.

Amount CWT accrualCost 48,247 3.7 brokerCost 29,297 4.2 accrualCost brokerCost Amount 48,247 29,297 CWT 3.7 4.2

Let me know if you need further details.

Thanks,

Scott

|||

I think I'm starting to see where you're going with this. Let me ask, where is the Amount measure coming from? When you say "Amount" are you actually referring the value of whatever measure you are referencing?

B.

MDX performance on calculated measure

Hi,

i have two problems with the following mdx statement:

CALCULATE;

create member [Logistik_TakteReklaRedressen].[MEASURES].[delivery quantity] as null;

scope ([MEASURES].[delivery quantity]);

this = Sum([EVT Date].[HierWeek].currentMember.Children, [MEASURES].[delivery quantity]);

/*Non_Empty_Behavior(This) = {[MEASURES].[lfmenge1],[MEASURES].[lfmenge]};*/

end scope;

scope ([MEASURES].[delivery quantity], [EVT Date].[Week].[Week].Members);

this = Iif( Not (IsEmpty([MEASURES].[lfmenge1]) AND IsEmpty([MEASURES].[lfmenge])),

Aggregate(PeriodsToDate([EVT Date].[Week].[(All)], [EVT Date].[Week].currentMember), [MEASURES].[lfmenge1]) + [MEASURES].[lfmenge],

null);

/*Non_Empty_Behavior(This) = {[MEASURES].[lfmenge1]};*/

end scope;

My primary objective is to improve performance for any query with measure delivery quantity. Can the second scope statement with iif could better coded?

With the storage of weekly delta for one part of my data (lfmenge1), i can save about 90% of data sets in the fact table. Would it be better to store any data set for performance standpoints?

The second problem is that, when I have query with a where statement on time dimension and the time dimension is not on the axis. The first scope statement work not correctly.

Sorry for my broken english.

Regards Alexander

Hi Alexander,

Could you explain the logic for this calculated measure, with examples of data - that would help in making suggestions. Also, what are the attributes and structure of the [EVT Date] dimension - and is [Week] the key attribute or lowest level?

|||

Hi,

i work for a magazine publisher. We have weekly shipping from magazines for our subscribers. The delivery take place with post or mostly with our own delivery system.
The geographic structure on it's lowest level has a dimensionality of 40000. Every week we have an avervage supply quantity about 4 million.

I want to refresh the cube every week.

I separate the data in two fact tables. In the first table are the facts from the magazines with non weekly frequency (lfmenge). In this table I store every delivery (accumulated in the lowest level over all dimensions).
In the second table I store only changes and stock at beginning from the magazines with weekly frequency (lfmenge1). So I can save 90% of storage amount.

I have a hierarchy with Year -> Week. The time dimension is build from server time with key date to the fact tables.

Weekly Magazin Non Weekly Magazin

A

B

C

D

Week 52 27.12.2006 100 200 Week 52 28.12.2006 1000 2000 Week 01 02.01.2007 Week 01 03.01.2007 -10 Week 01 04.01.2007 20 Week 02 10.01.2007 5 210 Week 02 11.01.2007 1 1900 MDX sumPeriodsToDate Cum. Week 52 100 1000

+

200 2000

=

3300 Week 01 90 1020

+

=

1110 Week 02 95 1021

+

210 1900

=

3226 2006 100 1000

+

200 2000

=

3300 2007 185 2041

+

210 1900

=

4336 All 285 3041

+

410 3900

=

7636

Regards Alexander|||

Maybe a different approach could work, where [MEASURES].[lfmenge1] is assigned cumulative values at the week level, so (assuming it is a "sum" measure) these values get rolled up to the year and "All" levels. Then [MEASURES].[delivery quantity] could simply be a sum of the 2 cube measures:

CALCULATE;

scope ([MEASURES].[lfmenge1], [EVT Date].[Week].[Week].Members);

this = Aggregate(PeriodsToDate([EVT Date].[Week].[(All)], [EVT Date].[Week].currentMember));

end scope;

create member [Logistik_TakteReklaRedressen].[MEASURES].[delivery quantity] as

[MEASURES].[lfmenge1] + [MEASURES].[lfmenge];

MDX moving averages

I calculate the Moving Average Total (MAT) like this

Sum(LastPeriods(12,[Dim Date].[Date].CurrentMember),[Measures].[Salesvolume])

Now i need to calculate the MAT-1 and % of the total of MAT

can somebody help me how to do this

thanx

Check if this post help you:

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

regards!!

|||

I think the MAT-1 can be solved by using parallel, If by MAT-1, you mean the MAT at the same time last year then something like the following should work.

Code Snippet

Sum(LastPeriods(12,ParallelPeriod([Dim Date].[Year], 1, [Dim Date].[Date].CurrentMember),[Measures].[Salesvolume])

For the % of MAT you should be able to build on the MAT calculation itself.

Code Snippet

[Measures].[Salesvolume] / Measures.[MAT Sales]

|||thanx Darren it worked...

MDX in KPI Value Expression

Hi,

I'm having some problems when developing a couple of KPI's in analysis services. I'd like them to calculate new contracts and renewal of contracts. I have a measure called [Contract Count] which i want to use but i need this to be filtered by the specific contract type member. In my KPI Value expression i have the folowing:

{[Measures].[Contract Count], [Contract].[By Contract Type].[ContractType].&[Renewal]}

I get the following error when i try to run this:

Members belong to different hierarchies in the function

Can anyone suggest what i'm doing wrong. I'm new to MDX and believed that this was the way to get return the required set.

Any help would be much appreciated.

Thanks in advance,

Grant

Hi all,

Apologies, my bad. I've found that i've been using {} instead of () parentheses.

It's working now.

Grant