topic Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures in SAS Web Report Studio

Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures

deleted_user — Wed, 19 May 2010 15:36:36 GMT

I would like to use PROC OLAP to create a calculated measure so that when I re-create the cube it is there and ready to go. It is a simple ratio of 2 other sum measures that I am calculating already. I tried using the DEFINE statement, but got the following error message...

ERROR: None of the global definitions in the DEFINE statement(s) could be verified as syntactically correct, or none of the global
definitions could be registered with the metadata server.

Maybe I need to try a different approach?

Re: Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures

AngelaHall — Wed, 19 May 2010 16:44:32 GMT

The DEFINE statement should work. Can you post an example of your current syntax for us to review?

Here is an example of another ratio (built off of a prior DEFINE sum):

DEFINE MEMBER '[CUBE_NAME].[Measures].[Total_Measure_Name]' AS
'sum(YTD([DIM_NAME].[HIER_NAME].LastChild),[Measures].[Measure_Name]),format_string="DOLLAR20."'
;

DEFINE MEMBER '[CUBE_NAME].[Measures].[Ratio_Measure_Name]' AS
'[Measures].[Total_Measure_Name] / [Measures].[Denominator_Measure_Name],format_string="PERCENT10.2"'
;

~ Angela Hall
http://sas-bi.blogspot.com

Re: Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures

deleted_user — Wed, 19 May 2010 16:51:29 GMT

Here is the DEFINE statement I used:

define member '[SMB_TOTMKT].[Measures].[mkt_share]'
as '[Measures].[locations]/[Measures].[vendor_nums]';

This looks similar to your 2nd example, only without the format string.

Re: Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures

AngelaHall — Wed, 19 May 2010 16:58:09 GMT

When you run the proc olap full code - do you have a prior step to delete the entire OLAP cube - or just the delete_physical?
(it needs to be 'delete' since you are adding metadata)
Use my post: http://sas-bi.blogspot.com/2005/08/refreshing-olap-cubes.html as an example (just change 'delete_physical' to 'delete').

Does the user that is running the proc OLAP step have 'write metadata' and 'delete' access to the OLAP schema?

~ Angela Hall
http://sas-bi.blogspot.com

Re: Use PROC OLAP to Calculate a New Measure based on ratio of 2 SUM Measures

deleted_user — Fri, 21 May 2010 22:38:00 GMT

Yes I have a step to delete the cube prior to running PROC OLAP to create the new cube. This delete step is done using a macro call, delete_cube_reg(CubeName) which in turn executes PROC OLAP cube=&CubeName DELETE;

The userid I am using is a common one that I believe has all of the necessary access priveleges since I am able to create and delete cubes using this userid.