Solved

SSAS Calculated Measure Using Link Member

Posted on 2014-10-08
4
212 Views
Last Modified: 2016-02-18
Hello,

I have been trying to get a calculated measure working in SSAS to show the number of open records against a date dimension.

My fact table contains "Created Date" and "Resolved Date". Measure i am using is simply "Record Count".

I have 3 Date Dimensions "Created Date", "Resolved Date" and another with no relationships called "Open Date".

I have been able to get a Calculated Measure working using the following:

CREATE MEMBER CURRENTCUBE.[Measures].[Open Records]
 AS AGGREGATE(
    {NULL:LINKMEMBER([Open Date].[Year - Month - Date].CurrentMember
        ,[Created Date].[Year - Month - Date])}
    * {LINKMEMBER([Open Date].[Year - Month - Date].CurrentMember
        ,[Resolved Date].[Year - Month - Date]):NULL}
    ,[Measures].[Record Count])

When I utilise this measure in Excel i can open up year and month, but when I open date, it takes an exceptionally long time to run, however it does eventually complete (I estimate it takes at least 30mins).

Am i doing something obviously wrong or is there a better method I could be using to get the result I am after?

I am using SQL2014.

Thanks,
Daniel
0
Comment
Question by:Daniel Djuracic
[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
  • 3
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40380224
I believe that is because when you try to open it in Excel by the day it will have to count with a "GROUP BY" at that date part level and proper data type/indexes could help in this case.  Can you please check to see the underlying data type for the three date columns and to see if there are any indexes on them?
0
 

Author Comment

by:Daniel Djuracic
ID: 40381269
Thanks for the reply, the date dimension table had a Clustered Index on the Primary Key (which is a datetime) data type. I have added a non-clustered index to the "CreatedDateKey" and "ResolvedDateKey" fields with no effect.
0
 

Accepted Solution

by:
Daniel Djuracic earned 0 total points
ID: 40412314
I worked around the issue by creating a whole new cube based on a view. The view unions 2 versions of the fact table, 1 uses created date as "Open Date" and the other uses resolved date as "Open Date" it then has another field which is 1 when open and -1 when closed, summing this measure produces the required results.

SELECT        CreatedDateKey AS OpenDateKey, 1 AS OpenCount
FROM            [DataWarehouse].[dbo].[FactITSMRecords]
UNION ALL
SELECT        ResolvedDateKey AS OpenDateKey, - 1 AS OpenCount
FROM            [DataWarehouse].[dbo].[FactITSMRecords]

Open in new window

0
 

Author Closing Comment

by:Daniel Djuracic
ID: 40419068
Workaround provides required result.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 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