Percent

Percent

Post by Jespe » Sat, 12 Jan 2002 21:59:01



Hello
I have a percentage problem.
I have a store dimension (Profile - Subprofile - Store)
and a time dimension (YQM). In my virtual cube I would
like to have one measures percent (called Keyvalue) of
another measure. Fine, A/B*100...
but...
for some stores I have one measure but not the other
(which makes it A/null*100 or null/B*100). The Keyvalue
gets an ERR for that store, and that is great. But on the
TOTAL (and the totals for each level in the dimension
Store) the Keyvalue is wrong:

          A      B     Keyvalue
Store1    12     120     10%
Store2    26       -     Err
Store3    20      80     25%
Store4    60     100     60%
TOTAL    118     300   39,3%

I dont want the TOTAL to include the Store2's A-value. I
would like it to be (12+20+60)/(120+80+100)*100=30,7%

Jesper

 
 
 

Percent

Post by Amir Net » Tue, 15 Jan 2002 07:20:42


Your best bet would be to have A be reset to NULL if B is NULL. You can do
it in the source column expression for the measure. All you need to do is
put a conditional SQL expression that such as ISNULL for SQL Server.

Amir Netz
Microsoft Corp.


Quote:> Hello
> I have a percentage problem.
> I have a store dimension (Profile - Subprofile - Store)
> and a time dimension (YQM). In my virtual cube I would
> like to have one measures percent (called Keyvalue) of
> another measure. Fine, A/B*100...
> but...
> for some stores I have one measure but not the other
> (which makes it A/null*100 or null/B*100). The Keyvalue
> gets an ERR for that store, and that is great. But on the
> TOTAL (and the totals for each level in the dimension
> Store) the Keyvalue is wrong:

>           A      B     Keyvalue
> Store1    12     120     10%
> Store2    26       -     Err
> Store3    20      80     25%
> Store4    60     100     60%
> TOTAL    118     300   39,3%

> I dont want the TOTAL to include the Store2's A-value. I
> would like it to be (12+20+60)/(120+80+100)*100=30,7%

> Jesper


 
 
 

Percent

Post by Jespe » Tue, 15 Jan 2002 18:44:36


Ok. So what your saying is that I have to solve this in
the fact table in the database (Oracle) and not in the
OLAP-cube

Jesper

 >-----Original Message-----
>Your best bet would be to have A be reset to NULL if B is
NULL. You can do
>it in the source column expression for the measure. All
you need to do is
>put a conditional SQL expression that such as ISNULL for
SQL Server.

>Amir Netz
>Microsoft Corp.



>> Hello
>> I have a percentage problem.
>> I have a store dimension (Profile - Subprofile - Store)
>> and a time dimension (YQM). In my virtual cube I would
>> like to have one measures percent (called Keyvalue) of
>> another measure. Fine, A/B*100...
>> but...
>> for some stores I have one measure but not the other
>> (which makes it A/null*100 or null/B*100). The Keyvalue
>> gets an ERR for that store, and that is great. But on
the
>> TOTAL (and the totals for each level in the dimension
>> Store) the Keyvalue is wrong:

>>           A      B     Keyvalue
>> Store1    12     120     10%
>> Store2    26       -     Err
>> Store3    20      80     25%
>> Store4    60     100     60%
>> TOTAL    118     300   39,3%

>> I dont want the TOTAL to include the Store2's A-value. I
>> would like it to be (12+20+60)/(120+80+100)*100=30,7%

>> Jesper

>.

 
 
 

Percent

Post by Amir Net » Wed, 16 Jan 2002 01:16:39


What I am saying is that the most performing solution would be to correct
the problem in the processing stage when the data is being read in. You do
not have to change anything in the Oracle database. All you need to do is
refine the Source Column setting in analysis services.

Amir.
Microsoft Corp.


> Ok. So what your saying is that I have to solve this in
> the fact table in the database (Oracle) and not in the
> OLAP-cube

> Jesper

>  >-----Original Message-----
> >Your best bet would be to have A be reset to NULL if B is
> NULL. You can do
> >it in the source column expression for the measure. All
> you need to do is
> >put a conditional SQL expression that such as ISNULL for
> SQL Server.

> >Amir Netz
> >Microsoft Corp.



> >> Hello
> >> I have a percentage problem.
> >> I have a store dimension (Profile - Subprofile - Store)
> >> and a time dimension (YQM). In my virtual cube I would
> >> like to have one measures percent (called Keyvalue) of
> >> another measure. Fine, A/B*100...
> >> but...
> >> for some stores I have one measure but not the other
> >> (which makes it A/null*100 or null/B*100). The Keyvalue
> >> gets an ERR for that store, and that is great. But on
> the
> >> TOTAL (and the totals for each level in the dimension
> >> Store) the Keyvalue is wrong:

> >>           A      B     Keyvalue
> >> Store1    12     120     10%
> >> Store2    26       -     Err
> >> Store3    20      80     25%
> >> Store4    60     100     60%
> >> TOTAL    118     300   39,3%

> >> I dont want the TOTAL to include the Store2's A-value. I
> >> would like it to be (12+20+60)/(120+80+100)*100=30,7%

> >> Jesper

> >.

 
 
 

1. SELECT TOP @Percent PERCENT?

I am using SQL Server 7.0.

Is there a PERCENT equivalent to SET ROWCOUNT?

Effectively I want something like this:  (This is a simplified version)



    C1
FROM
    T1
ORDER BY
    C1

I guess I could use sp_executesql but it's going to be a long SELECT using a
LEFT OUTER JOIN with additional filtering before the JOIN and what about the
performance gain lost?

Thanks,
Doug

2. Change printed font, and font size

3. Creating a percent.

4. User built SQL querys?

5. Top X Percent and NewID() (Median)

6. Create or replace function doesn't work so well

7. Help with proper MDX Percent syntax

8. help! - MSDE Problem

9. calculate percent

10. CPU at 80-90 percent on SQL 7

11. Percent

12. Need help - Percents in store procedure

13. SLA Percents