MDX QUERY

Question

Question

Ashok S · Nov 2, 2016

#Analyzer #Cubes #MDX #InterSystems IRIS BI (DeepSee)

Hi All,

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

My source table is like this format

FileName	AuditDate	FileType	FileStatus	AuditStatus
A	20161102	20	open	0
A	20161101	20	read	1
A	20161031	20	write	0
A	20161030	20	close	1
B	20161102	40	open	1
C	20161101	45	read	1
D	20161031	40	read	1
E	20161031	20	open	0
E	20161102	40	read	1
F	20161102	45	read	1

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 \ FileType		20	40	45
open	0	2
open	1		1
read	0
read	1	1	2	2
write	0	1
write	1
close	0
close	1	1

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

Discussion (5)0

Log in or sign up to continue

Evgeny Shvarov · Nov 4, 2016

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.

0 0

score 0 · Answer 1 · 2016-11-02T12:25:26-04:00

Eduard Lebedyuk · Nov 2, 2016

I think listing would be a more effective tool for that purpose.

0 0

score 0 · Answer 2 · 2016-11-03T01:18:54-04:00

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.

FileName	AuditDate	FileType	FileStatus	AuditStatus
A	20161102	20	open	0
A	20161101	20	read	1
A	20161031	20	write	0
A	20161030	20	close	1
B	20161102	40	open	1
C	20161101	45	read	1
D	20161031	40	read	1
E	20161031	20	open	0
E	20161102	40	read	1
F	20161102	45	read	1

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 \ FileType		20	40	45
open	0	1
open	1		1
read	0
read	1		2	2
write	0
write	1
close	0
close	1

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

score 0 · Answer 3 · 2016-11-04T08:24:36-04:00

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.

score 0 · Answer 4 · 2016-11-07T07:49:08-05:00

Ashok S · Nov 7, 2016

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

0 0