Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculated Measure in Cube

Posted on 2013-10-31
9
Medium Priority
?
340 Views
Last Modified: 2016-02-11
Hi. Use to belong to Experts-Exchange for years and now have re-subscribed.
I am trying to create a calculated measure in a cube for an average.

What I want it to do is first determine if the count of services is 0 then 0
else divide the total turn around days by the count of services. My turn around time
is based on certain statuses.  I included a field in my status table to indicate if it is one of the statuses that the turn around time is calculated on.

WITH MEMBER [Measures].[AverageTurnAroundTime] AS
 
      [Measures].[Turn Around Days]/[Measures].[SAR Services]
      select [Measures].[AverageTurnAroundTime] on columns
      from [SAR Management]
      where [Sar Status Code].[Turn Around Time Indicator] = 1
0
Comment
Question by:LauraLynne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1500 total points
ID: 39614296
>first determine if the count of services is 0 then 0
>else divide the total turn around days by the count of services

Can't help you with any SSAS aspects of this question, but raw T-SQL would be <air code>..
SELECT CASE 
    WHEN COALESCE(COUNT(services),0) > 0 THEN 0 
    ELSE SUM(Turn around days) / CAST(COUNT(Services) as numeric(19,4))  END as column_name
FROM [SAR Management]
WHERE [Sar Status Code].[Turn Around Time Indicator] = 1 

Open in new window

btw the CAST..as Numeric is so that the result has decimal values.
0
 

Author Comment

by:LauraLynne
ID: 39614359
Appreciate the help, jimhorn! Still need the solution for the cube average though. Changed the logic above to read  

case when COALESCE(COUNT(services),0) = 0 THEN 0 rather than > 0.

Now I just have to figure out how that code gets modified to MDX. Thanks
0
 

Author Comment

by:LauraLynne
ID: 39614388
I will be awarding points for your help, btw, because it is helping me in my solution. Still seeking assistance for the cube calculation. Laura
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39614436
Yep.  I'll back away gracefully now to encourage SQL - SSAS experts to respond.
0
 

Accepted Solution

by:
LauraLynne earned 0 total points
ID: 39621547
We were able to find the answer in house. I am posting the solution. I am awarding jimhorn points for helping with the divide by zero logic. Thanks.

 
WITH MEMBER [Measures].[AverageTurnAroundDays] AS
IIF([Measures].[Turn Around Days]= 0,0,[Measures].[Turn Around Days]/[Measures].[SAR Services])
select {[Measures].[Turn Around Days],[Measures].[SAR Services],[Measures].[AverageTurnAroundDays] } ON COLUMNS,
         [Initial Submit Date].[Month].&[2013-10-01T00:00:00]ON ROWS
from [SAR Management]
where [Sar Status Code].[Turn Around Time Indicator].&[1]
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39621562
Thanks for the accept, and thanks for posting your ultimate solution.
Good luck with your project.

Jim
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39621580
btw I've noticed that there's not a lot of us SSIS experts on EE, and probably fewer SSAS experts, so you can probably score a lot of points by answering questions here.
0
 

Author Closing Comment

by:LauraLynne
ID: 39635423
We figured it out inhouse.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question