MDX QUERY
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 | ||
| 1 | 1 | |||
| read | 0 | |||
| 1 | 1 | 2 | 2 | |
| write | 0 | 1 | ||
| 1 | ||||
| close | 0 | |||
| 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
Comments
I think listing would be a more effective tool for that purpose.
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 | ||
| 1 | 1 | |||
| read | 0 | |||
| 1 | 2 | 2 | ||
| write | 0 | |||
| 1 | ||||
| close | 0 | |||
| 1 | ||||
i.e , Group by FileName, Top1 Record from each filename, Order by AuditDate Desc(latest filestatus).
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.