How can I calculate average age in relatede fact tables?

MS SQL SERVER

    Sponsored Links

    Next

  • 1. Question on YTD
    Hello everyone, Here my problem, I have a customer who want to have YTD on columns, and measures on row.. Here an example of want I would like to achieve WITH MEMBER [TIME].[1998 YTD] AS 'ClosingPeriod(Month, [Time].[1998])' MEMBER [TIME].[1997 YTD] AS 'ParallelPeriod(Year,1,ClosingPeriod(Month, [Time].[1998]))' SELECT {[TIME].[1998 YTD],[TIME].[1997 YTD]} ON ROWS, Measures.members on columns from sales Currently the query return only the value for the last month of 1998, and the same month in 1997. But I would like to have the same grid but with the 1998 to 'ClosingPeriod(Month, [Time].[1998]) and the same period last year. If I try with WITH MEMBER [TIME].[1998 YTD] AS 'YTD(ClosingPeriod(Month, [Time].[1998]))' MEMBER [TIME].[1997 YTD] AS 'YTD(ParallelPeriod(Year,1,ClosingPeriod(Month, [Time].[1998])))' SELECT {[TIME].[1998 YTD],[TIME].[1997 YTD]} ON ROWS, Measures.members on columns from sales i got the error... (hint you can copy paste messagebox since windows 2000) --------------------------- MDX Sample Application --------------------------- Unable to open cellset Formula error - cannot convert set to Expression - in the PeriodsToDate function --------------------------- OK --------------------------- Thanks Best regards, Patrice
  • 2. Volume Testing of Analysis Services
    Does anyone know of a tool that can be used to volume test Analysis Services through Excel Pivot Table Services? We have a 16GB cube that we want to implement to the field who are all going to be accessing it through Excel. Before we implemented it, we wanted to find out how many simultaneous queries can be submitted before it (meaning the Analysis Services service as well as the server) dies. Any suggestions are most appreciated. Gary
  • 3. Help With "MS OLEDB FOR ORACLE"
    Hey I have a critical problem While I trying to use "Microsoft Oledb provider for oracle" In order to process my cube My relational db stored in Oracle So I have to use this driver Every time I trying to process the cub I get immediately error "one or more command.. Something about my relational db I check the select statement and saw the problem Somehow in the "from" part the olap implicit the fact tabl Twice!! (Select ... From Sales, Sales. Where My schema is correct and I can see the reason When I using the Oracle provider, I get another error The olap generate incorrect select for my time dimension By implicit the year level twice Again somehow the olap generate incorrect SQL querie What should I do?? Best Regards Chai Cheh.
  • 4. Exchange rate
    Can any one guide me in implementing Exchange rate I have sales cube with measures which need to multiply with currrency conversion factor from Exchangerate cube My sales cube has countrykey,Timekey Exchange rate cube has CurrencyKey,Timekey,Countrykey,Convfactor Each measure in sales cube should multiply with the Convfactor I am planning to have currency dimension with one level Currency which will have values USD,EUR,GBP If user selects USD should multiply with USD convfactor Can any one tell me best approach
  • 5. Percentage measure
    Hi, I a newbie in MDX. I want to create a calculated member which shows the percentage allocation of a measure. I use the following code: CREATE MEMBER [myCube].[Measures].ZM1 AS '(Dim1.CurrentMember, [Measures].ZM1perc) / (Dim1.CurrentMember.parent, [Measures].ZM1perc)' With only one dimension everything is OK. But with more than one dimension, the value of "(RD1.CurrentMember.parent, [Measures].ZM1perc)" is wrong starting with the second dimension. My goal is to display the results in one of two different ways (or better, both of them, making the behaviour configurable): 1. In this case the percentage values for the members in the 2nd dimension refer to the total data set: Dim1 Value M1 30% M2 60% M3 10% --------------------------- expand M1 Dim1 Dim2 Value M1 B1 10% M1 B2 15% M1 B3 05% M2 B4 30% ... ... ... 2. In this case the percentage values for the members in the 2nd dimension would refer to the data set represented by the parent member, interpreting the parent member value as 100%. An example would be: Dim1 Value M1 30% M2 60% M3 10% --------------------------- expand M1 Dim1 Dim2 Value M1 B1 23% M1 B2 60% M1 B3 12% M2 B4 30% ... ... ... Any ideas how I can do this? Regards, Thorsten Blawatt

How can I calculate average age in relatede fact tables?

Postby thomas.bendel » Fri, 01 Oct 2004 20:50:42 GMT

How can I calculate the average age of those customers, who bought
something in a concrete time period? Is it possible to measure it in
any way?
 
My fact table 'customer' contains customer_ID and age.
A related table 'position' contains the information about the shopping
activities of these customers. The tables  are 1:n related with
customer_ID.
The table 'position' has the dimension 'time', 'customer' does not.
 
The problem is:
 
Cause of the relation between tables the aggregate function  'count'
lists the customer more than one time.--> distinct count
Ok, but the 'Sum' is my problem.
 
The measure 'sum of age / distinct count of customer_id' lets get the
customers very old...
The measure 'sum of age / count of customer_id' weights the age of
customers wrong.
 
Can anybody help me? Thanks a lot.

RE: How can I calculate average age in relatede fact tables?

Postby UGV0ZXI » Sat, 02 Oct 2004 17:29:03 GMT

Hi,

If you create a average measure with Sum function, this will not give a 
correct value in the aggregating level.

f.e.:

Time	Customer	Age	Average

1998	Joe Little	20	20
1998	John Big	30	30

Total	Total		50	25

2000	Marry Big	20	20
2000	Marry Little	26	26

Total	Total		46	23

Group Total		96	48 (!!!)

Because the leaf members average will be aggregating, if you use sum function.
This case you can use the Min, or Max function. In this case you will get 
correct values(Only just the min or max values).

Other ways:

You can use Calculeted Member, because you will get the correct values.

f.e.:

Time	Customer	Age	Average

1998	Joe Little	20	20
1998	John Big	30	30

Total	Total		50	25

2000	Marry Big	20	20
2000	Marry Little	26	26

Total	Total		46	23

Group Total		96	24

The correct formula is, if you use Calculated Member: 'sum of age / distinct 
count of customer_id' 

I hope, I can help you.





RE: How can I calculate average age in relatede fact tables?

Postby thomas bendel » Sat, 02 Oct 2004 20:21:09 GMT


Hallo Peter, 

thanks for your answer.
I'm not sure if we talk about the same problem.

table customer:
cust_id
age

table position:
posi_id
cust_id
date

The tables are joined with kund_id. For each cust_id in customer there
exists 0, one or more posi_id in position.

If I sum the age of customers as a measure, the result is wrong in that
case, that customers are counted twice or more.

The calculated member sum of age / distinct count of customer is
therefor also wrong.

Tanks

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: How can I calculate average age in relatede fact tables?

Postby catalin.bobarnac » Sun, 03 Oct 2004 21:05:32 GMT

the solution to your problem is
sum of age / (count of position * distinct count of customer)





Re: How can I calculate average age in relatede fact tables?

Postby thomas bendel » Tue, 05 Oct 2004 15:41:10 GMT

Thanks Catalin for your reply.

Please correct me if I'm wrong:

Given the following data:

position:

posi_id posi_customer_id
1       1
2       1
3       1
4       2

customer:

customer_id age
1           30
2           40

The related view looks like:

posi_id customer_id age
1       1           30
2       1           30
3       1           30
4       2           40

Sum of age: 130
Count of position: 4
Distinct count of customer: 2

Result with your formula: 130/(4 * 2)=16.25

Have I missunderstood your answer? Thanks for reply, Thomas



*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: How can I calculate average age in relatede fact tables?

Postby Deepak Puri » Wed, 06 Oct 2004 01:06:03 GMT

Thomas,

For your sample data below, what weighted average age would you like
(presumably it will be in the range of ages).


- Deepak

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: How can I calculate average age in relatede fact tables?

Postby thomas bendel » Wed, 06 Oct 2004 16:42:26 GMT


OK, I see, my description of my problem seems to be not quite clear.

The question is:

How can I calculate the average age of those customers, who bought
something in a given time period (or a special product, or a product of
a special company and so on?

The existing relational database (simplified):

The age of each customer is written in column geof table customer.
Customer_id is a unique key.

Table customer:
Customer_id  age
1            30
2            40

The shopping activities are written in table osition Posi_ID is a
unique key, customer_ID is the foreign key.

Table position:
Posi_ID  Customer_ID  Date
1        1            2002
2        1            2002
3        2            2002
4        2            2003

How can I model this in OLAP with MS Analysis Manager, so that I can get
an answer to:
What is the average age of my customers who bought something in 2002?
The answer I would like to get is 35.

My idea was, to make table ustomerto a fact table. Make Date to a
dimension. Relate the dimension ateover ositionto ustomer

Measure ge= sum of age, Measure umber of customer= count of
customer_id. Calculated member verage age= age / number of customer.
But this weights the customers who bought more often too much.

Has anybody a solution to my problem? 

Thanks Thomas.


*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: How can I calculate average age in relatede fact tables?

Postby Deepak Puri » Thu, 07 Oct 2004 12:52:42 GMT

Thomas,

Based on your clarifications, how about this approach?

- Customer table is used to build Customer dimension, with
  the "Age" Member Property at the [Customer_ID] level.

- Position table is the cube fact table, with Customer,
  Date and Product dimensions.

- Define [Measures].[AverageAge] as
  'Avg(NonEmptyCrossJoin([Customer].[Customer_ID].Members,
   {[Date].CurrentMember}, {[Product].CurrentMember}, 1),
   StrToValue([Customer].CurrentMember.Properties("Age")))'


- Deepak

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: How can I calculate average age in relatede fact tables?

Postby thomas bendel » Fri, 08 Oct 2004 17:52:47 GMT


Hallo Deepak Puri, (and all other people handling this problem),

1. I understood your solution and 
2. it works.(the first is even more surprise)
Thanks a lot!

What I have to do is to test it with my 'big and real' database to
evaluate the performance of this calculation.

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Similar Threads:

1.Calculate average age when persons are not unique

I have a employee-cube where each person can have more than one
employments. I have a distinct count measure for counting distinct
persons and an Age measure.
But how can I calculate the average age of employees? I can't just sum
the ages and divide by number of distinct persons since each person can
have more than employment. It wouldn't be correct to divide by number
of employments either.
E.g.

Person,Employment,Age
----------------------------------------
Person1,Employment1,20
Person1,Employment2, 20
Person2,Employment1, 80

The correct age average is 50 (100/2).
It would seem like simple enough, but I can't figure out how to solve
this in MDX

Any hints?

Thanks

2.Query to calculate running average and update table ?

I am running an Inventory Application which records daily receipts and 
issues of supplies in a industry.

The issue of an item is recorded with the average cost at the time of issue.

The problem is that if the wrong price is entered while recording the 
receipts, all later issues of that item will be issued with the wrong 
average price.

Now, the user go back to that date in Inventory, and change the purchase 
price of that item and save it. As such, all the issue prices of that item 
of every transaction should be changed and updated.

Currently I am doing this with a VB-6 Do/While Loops to recalculate the 
running average price of item and it takes much time, because the data is 
about 50,000 records.

If I get any query which should do this, will be quite helpful for me.

Best Regards,

Luqman

For example:

Item       Receipt                       Issue 
Balance
             Qty  Rate  Amount      Qty  Rate  Amount   Qty  Rate  Amount
 A          2     2.10  4.20                                           2 
2.10  4.20
 A          2     2.05  4.10                                           4 
2.07  8.30
 A                                             2     2.07  4.15        2 
2.07   4.15
 A          2     2.15  4.30                                           4 
2.11   8.45
 A                                             2     2.11  4.22        2 
2.11   4.23
 A          2     2.13  4.26                                           4 
2.12   8.49





3.Calculating an average via multiple tables

Hey all  I am currently working on a database and stuck on a question
regarding SQL. I know the basics of SQL although this question has me stumped


A list is required of dogs that have career winnings better than that of the
average dog. List the dog_id, dog name and the career winnings. A career
winning is the sum of all money won by a dog. The solution must include dogs
that have not won any prizes when calculating average winnings.

PRIZE 
Event_id Place Money 
101 1 120 
101 2 60 
101 3 30 
102 1 10 
102 2 5 
102 3 2 
103 1 100 
103 2 60 
103 3 40 
401 1 1000 

ENTRY

event_id dog_id place
101 101 1
101 102 2
101 201 3
101 301 4
102 201 2
103 201 3
201 101 1
301 301 2
401 102 7

DOG
Dog Name
101 James
102 Sarah
201 Phillip
301 Jasmine
401 Steven
501 Emma
502 Michael

I am not sure if this is too much to ask but would anyone be able to show me
what i am meant to do? I am working on a html project and was suddenly
smacked with a SQL question and i need to work this out before i can continue


I have always wanted to learn but this html assignment is hard enough without
a SQL requirement i have NO idea about lol

Thanks for at least reading this far haha

4.Splitting monthly fact tables and daily fact tables into separate databases

OK...I need someone's opinion on this:

My database1 is growing so big (70 GB) that the time frame 
it takes for nightly dbase backup to disk and tape backup 
will eventually run over my daily morning ETL.

Currently, the database1 structure consists of daily fact 
tables, monthly fact tables, and dimensional tables (in a 
nutshell).  I retain 60 days of details in the daily fact 
tables, and once a month, there is a process to roll-up 
previous months details into the monthly fact tables.

Here is what I need opinion on:
I plan to move all the monthly fact tables to a separate 
database.  However, my problem with this way is having to 
duplicate the dimensional tables in both databases, and 
the hassle of keeping the dim tables in synch.  Any other 
options to handle this?

So, I thought of creating views of dim tables off 
database1 in database2.  My concern here is the impact on 
query performance within database2 (including monthly 
MOLAP cubes) since the underlying dim data is from another 
database.  Any other implications.  Or should I not be 
concern about this?  

5.Grand total with calculated measure multiple fact tables

We have a cube that uses multiple fact tables.  We are on sql server
2007.  The measure is created by multiplying the allocation from one
fact table to the $ amount in the other fact table.  It seems to work
when at the lowest level(day or using a dimension from the line leve),
but if you go up any higher, it does not add up the values correctly.
See example below.   The grand total is completely off.  It is also
slightly off at month levels when there is more than one child for the
month, etc.


Cost Centr Cd	2009-M01	2009-M02	2009-M04	2009-M05	2009-M06	Grand Total
47861	                      $141 	 $107 	 $25 	 $8,027 	 $27
$70,227


Any ideas for a workaround or fix to this problem?

Thanks!

6. Average Age

7. Age Average

8. fact-less fact table



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 36 guest