Count of Units * Unit Price

My dsv has two tables (one is used materials another is material price).

My used material dimension has "stock nr" and "count of material" attributes and material price table has "stock nr" and "unit price" attributes.

My goal is to calculate "total price" measure for the cube with count of material * unit price. Not all the materials have price value...

I don't know what to do. Could you recommend something?

- Using integration services instead of analysis service before processing.
- Using an MDX query (I am not good at MDX)
- Using Calculations?

Please help me in detail?

You could also join the 2 tables in the DSV, within a Named Query which returns total price as a computed column, like:

select u.[stock nr], p.[unit price], u.[count of material] * p.[unit price] as [total price]

from [used material] u

join [material price] p

on u.[stock nr] = p.[stock nr]

|||too late but thanks. I've used it

Count of Units * Unit Price

My dsv has two tables (one is used materials another is material price).

My used material dimension has "stock nr" and "count of material" attributes and material price table has "stock nr" and "unit price" attributes.

My goal is to calculate "total price" measure for the cube with count of material * unit price. Not all the materials have price value...

I don't know what to do. Could you recommend something?

- Using integration services instead of analysis service before processing.
- Using an MDX query (I am not good at MDX)
- Using Calculations?

Please help me in detail?

You could also join the 2 tables in the DSV, within a Named Query which returns total price as a computed column, like:

select u.[stock nr], p.[unit price], u.[count of material] * p.[unit price] as [total price]

from [used material] u

join [material price] p

on u.[stock nr] = p.[stock nr]

|||too late but thanks. I've used it

Count of employees

Hi,

We got of count of employees from measures against a date dimension.

we need to get average count for a time period (ie..week,quarter,year ).

and the formula for avg employee count: (empl-count on firstday of period+empl-count on last dayof period)/2

Date EMPCount

for ex : 1-Nov-2005 2361

2-Nov-2005 2521

3-Nov-2005 2762

4-Nov-2005 2500

avg count for week in novemeber: 2361+2500/2.

Kindly let me know how we can do this in SSAS cube.

Thanks in advance

Raj

I can think of a couple of ways of doing this, I don't have a descent sample set to perfomance test against, I suspect the second method may be faster, specially at higher level as it will not have to evaluate a large set of days.

Method "a" gets the descendants of the current time member and grabs the first and last member of that set to average them.

create member measures.a as ((Head({descendants([Time].[Financial].CurrentMember,[Time].[Financial].[Financial Date]) as mths},1).item(0),Measures.Amount) + (TAIL(mths,1).item(0),Measures.Amount))/2

Method "b" uses 2 recursive functions to walk down the time hierarchy to grab the first and last member underneath the currentmember. The third calculation then simple adds the first 2 and divides by 2.

create member measures.bhead as iif([Time].Financial.CurrentMember.Level is [Time].[Financial].[Financial Date],Measures.Amount,([Time].Financial.CurrentMember.FirstChild,Measures.Measures.bHead))

create member measures.btail as iif([Time].Financial.CurrentMember.Level is [Time].[Financial].[Financial Date],Measures.Amount,([Time].Financial.CurrentMember.LastChild,Measures.Measures.bTail))

create member measures.b as (measures.bhead + measures.btail)/2

|||

Hi,

I tried with method "b" it's showing "#Value!"

We have server created time Dimension is it because of that ?

or we are getting active employees count thro named query?

server created time dimension is "atrntime" and dimension attributes are Date,year,week,day of week,day of year.

please help

thanks

|||

No, neither of those things should stop the query from working. I did not have access to Adventure Works when I posted the last sample, so I had to remove some client specific stuff from the sample I sent. Below is an actual working query that will run against the Adventure Works sample database.

WITH

member measures.DateHead as iif([Date].Fiscal.CurrentMember.Level is [Date].[Fiscal].[Date]
,[Measures].[Internet Order Count]
,([Date].Fiscal.CurrentMember.FirstChild,measures.DateHead)
)

member measures.DateTail as iif([Date].Fiscal.CurrentMember.Level is [Date].[Fiscal].[Date]
,[Measures].[Internet Order Count]
,([Date].Fiscal.CurrentMember.LastChild,measures.DateTail)
)

member measures.AvgOrderCnt as (measures.DateHead + measures.DateTail)/2

SELECT
{Measures.DateHead
,Measures.DateTail
,Measures.AvgOrderCnt
,Measures.[Internet Order Count]} ON COLUMNS

,[Date].Fiscal.Month.Members ON ROWS

FROM [Adventure Works]

If you are still having issues, you may find that displaying the results of the two underlying measures may help to diagnose any issues. If you are still unable to resolve the #value problem, try posting your calcuations and I (or someone else) may be able to spot the issue.

|||

Hi,

Thanks for the answer.

I tried with this calculated measure

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[Week]
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);
and the output from cube browser was

Active Empl

DateHead

Calendar 2006

Week 25, 2006

Monday, June 19 2006

2368

Tuesday, June 20 2006

2369

Thursday, June 22 2006

2367

Friday, June 23 2006

2365

Saturday, June 24 2006

2364

Total

2375

2375

Week 26, 2006

Monday, June 26 2006

2374

Tuesday, June 27 2006

2373

Wednesday, June 28 2006

2372

Total

2375

2376

Total

2387

but the expected answer for datehead measure was
2368 for week 25 and 2374 for week 26

and one more thing, i was not able to figure it out is total :2375 for wk25 and 2375 for week 26 (it's not the average also....)

We have sever created time dimension "AtrnTime" and "drill" is the herarchy defined as year-week-date

thanks

Raj

|||

I think what you are getting is the distinct count for the week and I think what you are after for the DateHead measure is the count for the first day. By putting the Week Level/Attribute in the test for the IIF clause, you have effectively stopped the recursion there. Changing the level in the test for the iif clause should give you the result you are after.

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[Date]
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);

I could possibly have coded my example better to show what I was intending, by using the IsLeaf() function, maybe the following is a better way of coding this measure.

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);

This will make the measure recurse down until it hits the leaf level of the hierarchy.

|||

Hi,

Yeah you were right, i was getting Distinct Count for ActiveEmpl measure and i am after getting count for the first day.

i tried executing bothe the MDX scripts. It works at the day level but when i aggregate to the week level i should get the count of first day in that week .. but i am getting blank in that place.

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1

AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)

,[Measures].[ActiveEmpl]

,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead1)

);

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead2

AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[date]

,[Measures].[ActiveEmpl]

,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead2)

);

and the output of that was

Active Employees

DateHead1

DateHead2

Calendar 2006

Week 25, 2006

Monday, June 19 2006

2368

2368

2368

Tuesday, June 20 2006

2369

2369

2369

Thursday, June 22 2006

2367

2367

2367

Friday, June 23 2006

2365

2365

2365

Saturday, June 24 2006

2364

2364

2364

Total

2375

Week 26, 2006

Monday, June 26 2006

2374

2374

2374

Tuesday, June 27 2006

2373

2373

2373

Wednesday, June 28 2006

2372

2372

2372

Total

2375

Total

2387

but when i aggregate to the week level datehead1 and datehead2 was blank as below.

Active Employees

DateHead1

DateHead2

Calendar 2006

Week 25, 2006

2375

Week 26, 2006

2375

Total

2387

datehead1/datehead2 should be 2368 for wk25 and 2374 for wk26

thanks in advance..

|||

Would I be right if I were to guess that your week starts on Sunday, which normally does not have any data? I'm guessing that members without data are probably what is causing the blanks here. There are probably a number of ways of dealing with this, we could nest another IIF clause to effectively "walk" along the siblings at the day level, looking for a non-empty one, but I don't think that would be terribly efficient.

We could grab all the siblings at the date level and return the first non-empty.

eg.

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1
AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)
,HEAD(NONEMPTY([AtrnTime].[drill].CurrentMember.Siblings, {[Measures].[ActiveEmpl]}),1)
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead1)
);

But if we have to deal with sets of members and finding non-empty children it might be better not to use recursion and to grab all the non-empty descendants of the time dimension.

CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1
AS HEAD(NONEMPTY(Descendants([AtrnTime].[drill].CurrentMember
,[AtrnTime].[drill].[Date]), {[Measures].[ActiveEmpl]}),1)
;

And if we are going down that path I would suggest looking into coding the whole thing into one measure so that you do not have to do the nonempty twice (once for the first day and once for the last day). You can do this by naming the non empty set and re-using it.

eg

CREATE MEMBER CURRENTCUBE.[MEASURES].Avg
AS (HEAD(
NONEMPTY(Descendants([AtrnTime].[drill].CurrentMember
,[AtrnTime].[drill].[Date]) * {[Measures].[ActiveEmpl]}) AS NonEmptySet
,1).Item(0)
+
TAIL( NonEmptySet
,1).Item(0))
/ 2;

;

|||

yeah your guess was right ,,sunday was the starting day of the week, and the blank row was because of no data for that day..,, finally the avg query worked which takes head/tail of nonempty set.

Thanks a lot Darren.

Count Children

Hi

I have a time dimension, that has an hierarchy with three levels, Year, Half Year and Month.

Is it possible to count how many days there are in each level?

Regards

You should be able to do this using the "Existing" function to count the number of days using the attribute hierarchy for days. Here is an example using AdventureWorks:

WITH

MEMBER MEASURES.[Count of Days]

AS

{Existing [Date].[Date].[Date].Members}.Count

SELECT

{MEASURES.[Count of Days]} ONCOLUMNS,

Hierarchize(

{{[Date].[Calendar].[Calendar Year].Members},

{[Date].[Calendar].[Calendar Quarter].Members},

{[Date].[Calendar].[Month].Members}}) ONROWS

FROM

[Adventure Works]

HTH,

Steve

|||

Hi Guys

Working with the same hierarchy,

Is it possible to know the FirstChild and LastChild members in each level?

I'm trying to do this but I get an error #Error

WITH

MEMBER [Measures].[First Day]

AS

{[Tiempo].[Fecha].FirstChild}

select

[Measures].[First Day] ON COLUMNS

from [MyCube]

Regards

|||

If you return a "day" member, then you will have to create the member on your time hierarchy. Try the following:

MEMBER [Tiempo].[Fecha].[First Day]

AS

Head({Existing [Tiempo].[Fecha].DefaultMember.Children},1)(0)

MEMBER [Tiempo].[Fecha].[Last Day]

AS

Tail({Existing [Tiempo].[Fecha].DefaultMember.Children},1)(0)

HTH,

Steve

|||

Hi Steve, thanks for answer

Let me undenstand, Do I need to add the day level in my hierarchy?

Those new members aren't measures, they are Time dimension members. Aren't they?

Do you have an example about how to use it?

Regards

corrupted file error

I think one of my file got corrupted when I tried to clean up my dsv and dimension since I got the following message when try to open the cube:

Errors in the metadata manager. An error occurred when loading the
DimProduct dimension, from the file, '\\?\E:\Program Files\Microsoft SQL
Server\MSSQL.2\OLAP\Data\Financial.0.db\DimProduct.4.dim.xml'.
Errors in the metadata manager. An error occurred when loading the
RevenueAnalysis cube, from the file, '\\?\E:\Program Files\Microsoft SQL
Server\MSSQL.2\OLAP\Data\AW.0.db\AW\View.26.cub.xml'.

What is the easy way to fix this, I have the back up copy or xmla script, but it won't allow me to run it. I can not even delete the AS database since I am thinking about restore from my backup

Thanks

Don

MS tech support claims this normally goes away after a full process of the database but I haven't found this to be true.

I had to shut down the SSAS service, delete all files within the data directory of the particular database with the problem, start back up the service, then delete the database via SSMS. After this I just re-deployed from our source control and did a full process.

Hasn't happened since our SP2 upgrade (fingers crossed).

Hope this helps.