Search code examples
sqlssasmdxcubeolap-cube

Simple query conversion from sql to mdx

    select MAX(InsertionDT),TaskStatus,tasksubstatus 
    from TransTasksFFMS_GVT 
    where TaskCode = 323155 group by TaskStatus,tasksubstatus

To use this query in the cube browser I have created a name query and used it as a dimension and it works, however I'm asked to do the calculation as a measure. Is it possible to translate this query to mdx to use it as the expression in a calculation?

What the query does: Returns the max insertion date for a each pair of status and sub status.

I'm new to MDX and to the whole cube idea!

Solution

  • Yes, first make the whole thing a star schema with a fact table/view and a dimension table for the tasks containing Task code, status, and substatus. Then define a measure for the InsertionDt using Max as the aggregation function.