How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

how to calculate percentage

b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanksjust do select table1.column1/table2.column2 from table1,table2

are the tables related in anyways? are they just numeric values that have nothing to do with each other? i need more information to help|||yes they are related and numberic
it gives me 0{zero] through in the results

i did table1.column1/table2.column2 as Percentage from table1,table2
do i have to create column? why outputing 0's only?|||Probably one or both columns are declared as integer. Try:

select convert(numeric(38, 19), table1.column1)/convert(numeric(38, 19), table2.column2) from table1,table2|||error
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.table1' does not match with a table name or alias name used in the query.

this column name has space and im using it like

select convert(numeric(38, 19), table1.[column1 name])/convert(numeric(38, 19), table2.column2)|||i just ran it and it worked fine

select convert(numeric(38,19), table1.[a c])/convert(numeric(38,19), table2.[b d])
from table1, table2

i guess double check that you are using the right DB, and you've typed everything correctly. Otherwise can you post up your query for me to look at?|||thks
it's working|||do you know how to format this to 2 decimal places?
like 20 instead of 0.02|||Umm...I'll just take a guess here...multiply by 100?

I assume you meant that 0.02 should display as 2, not 20...