MDX QUERY

Analyzer, Cubes, MDX, DeepSee

Hi All,

I need some help to create mdx for the following scenario.


My source table is like this format

FileNameAuditDateFileTypeFileStatusAuditStatus
A2016110220open0
A2016110120read1
A2016103120write0
A2016103020close1
B2016110240open1
C2016110145read1
D2016103140read1
E2016103120open0
E2016110240read1
F2016110245read1

 

When the user selects any Date in my application, Till selected date results are displayed for all filename.

My current MDX is

SELECT
{
    [FileTypeD].[H1].[FileType].&[20],
    [FileTypeD].[H1].[FileType].&[40],
    [FileTypeD].[H1].[FileType].&[45]
} ON 0,
CROSSJOIN([FileStatusD].[H1].[FileStatus].Members,[AuditStatusD].[H1].[AuditStatus].Members) ON 1 
FROM [Audit]
%FILTER %OR([AuditDateD].[H1].[AuditDate].&[60000]:&[SelectedDate])

In my cube, AuditDate is in Horolog Format

My mdx display result like this

FileStatus with Audit Type \ FileType204045
open02  
1 1 
read0   
1122
write01  
1   
close0   
11  

 

Further I want to change my mdx according to the below condition. How should i write my mdx?

(Display top 1 record, group by file name, order by AuditDate desc) - Display the recent file status group by filename.

 

The problem which i am facing is, it is taking more time and got timeout error. because of more than 20 lakh records in source table.

 

Thanks in advance

  • 0
  • 0
  • 266
  • 3
  • 2

Answers

Ashok, can you reproduce the problem in HoleFoods cube which comes with Samples namespace?  It would be easier to play with the data and suggest a solution.

I Didn't find the same situation or logic in holefoods or patients cube.

It' seems you can use HEAD function for your dimension expression, e.g.

HEAD([AuditStatusD].[H1].[AuditStatus].Members)

This will show only the first member from all the statuses for the set.

Also, you can sort the members of AuditStatus dimension by AuditDate. To do this introduce to the AuditStatus level in the cube AuditDate property (just drag it to the level from the properties list in the Architect) and apply the sorting for this property as Descending. It will sort all the members of the dimension AuditStatus level by AuditDate in all the queries and will show you the last one for the HEAD function.

Comments

Hi Eduard,

I need to write mdx in the below format.

Because, i convert my MDX into JSON and using the json string i do some calculations and finally displaying all together into html table in zenpage.

 If i use listing concept. I don't know it will work.

FileNameAuditDateFileTypeFileStatusAuditStatus
A2016110220open0
A2016110120read1
A2016103120write0
A2016103020close1
B2016110240open1
C2016110145read1
D2016103140read1
E2016103120open0
E2016110240read1
F2016110245read1

 

I want to display only these records which are shown above in blue text. So my output will be like this

FileStatus with Audit Type \ FileType204045
open01  
1 1 
read0   
1 22
write0   
1   
close0   
1   

 

i.e , Group by FileName, Top1 Record from each filename, Order by AuditDate Desc(latest filestatus).