Datazen - SSAS Tabular Model - Dates not being considered

Question

I have been using Datazen for a while now, struggling with understanding how it internally works, especially with the lack of documentation about it.

Anyway, I have a SSAS tabular model which has a Datetime field in a table marked as Date. I use this tabular model to create reports in excel and it works fine.

Now, I wanted to use this tabular model in Datazen, and I was trying to create a data view that reads the measures and the attributes in MDX

The problem is when I mark the field Creation Date as DateTime in Datazen control panel, it gives me the following message: Failed to refresh in Dashboard

This is the MDX query I'm using:

SELECT NON EMPTY { 
    [Measures].[Count of Misuse Bugs], 
    [Measures].[Count of Valid Bugs], 
[Measures].[Count of Bugs], 
[Measures].[Count of Invalid Bugs], 
[Measures].[Time Spent on invalid Bugs], 
[Measures].[Time Spent on Valid Bugs], 
[Measures].[Invalidity Ratio], 
[Measures].[Misuse Ratio] 
} ON COLUMNS, 
NON EMPTY { 
    (
    [Bugs].[BugID].[BugID].ALLMEMBERS * 
    [Bugs].[BugTitle].[BugTitle].ALLMEMBERS * 
    [Bugs].[Client].[Client].ALLMEMBERS * 
    [Bugs].[Current State].[Current State].ALLMEMBERS * 
    [Bugs].[Final Resolution].[Final Resolution].ALLMEMBERS * 
    [Bugs].[Internal Vs. External].[Internal Vs. External].ALLMEMBERS * 
    [Bugs].[Last Reasonable State].[Last Reasonable State].ALLMEMBERS * 
    [Bugs].[Owner].[Owner].ALLMEMBERS * 
    [Bugs].[Owner State].[Owner State].ALLMEMBERS * 
    [Bugs].[Project].[Project].ALLMEMBERS * 
    [Bugs].[Release].[Release].ALLMEMBERS * 
    [Bugs].[Responsibility].[Responsibility].ALLMEMBERS * 
    [Bugs].[TAR].[TAR].ALLMEMBERS * 
    [Creation Dates].[Creation Date].[Creation Date].ALLMEMBERS  
) 
} ON ROWS FROM [Bugs]

I am using SQL Server 2012. Any help would be greatly appreciated. ADDING FIELD AS DATETIME IN DATAZEN DATA VIEW

Nighty_ · Accepted Answer · 2016-03-02T12:14:01.073

Datazen is not the most dynamic at understanding how dates are being formatted. Try formating them as yyyy-MM-dd.

Edit: I am creating a new member that is the formatted date. Hopefully this will work for you.

WITH 
MEMBER DatazenDate AS
cdate(format([Creation Dates].[Creation Date].CURRENTMEMBER.MEMBER_VALUE, "yyyy-MM-dd"))       

SELECT NON EMPTY { 
    DatazenDate        
    [Measures].[Count of Misuse Bugs], 
        [Measures].[Count of Valid Bugs], 
    [Measures].[Count of Bugs], 
    [Measures].[Count of Invalid Bugs], 
    [Measures].[Time Spent on invalid Bugs], 
    [Measures].[Time Spent on Valid Bugs], 
    [Measures].[Invalidity Ratio], 
    [Measures].[Misuse Ratio] 
    } ON COLUMNS, 
    NON EMPTY { 
        (
        [Bugs].[BugID].[BugID].ALLMEMBERS * 
        [Bugs].[BugTitle].[BugTitle].ALLMEMBERS * 
        [Bugs].[Client].[Client].ALLMEMBERS * 
        [Bugs].[Current State].[Current State].ALLMEMBERS * 
        [Bugs].[Final Resolution].[Final Resolution].ALLMEMBERS * 
        [Bugs].[Internal Vs. External].[Internal Vs. External].ALLMEMBERS * 
        [Bugs].[Last Reasonable State].[Last Reasonable State].ALLMEMBERS * 
        [Bugs].[Owner].[Owner].ALLMEMBERS * 
        [Bugs].[Owner State].[Owner State].ALLMEMBERS * 
        [Bugs].[Project].[Project].ALLMEMBERS * 
        [Bugs].[Release].[Release].ALLMEMBERS * 
        [Bugs].[Responsibility].[Responsibility].ALLMEMBERS * 
        [Bugs].[TAR].[TAR].ALLMEMBERS * 
        [Creation Dates].[Creation Date].[Creation Date].ALLMEMBERS  
    ) 
    } ON ROWS FROM [Bugs]

How can we do that exactly? using cdate? Can you please show me in the above mentioned MDX query how it can be done? — mazazino, Feb 23 '16 at 09:10
Edited my answer with a new member that is the formated date. — Nighty_, Mar 02 '16 at 12:14
Thank you very much, it did work. But the problem is performance now. with each member i'm adding or measure, the cdate member is slowing down the query performance exponentially. Is there any other alternative? — mazazino, Mar 07 '16 at 11:24
If you are able to format the date in your data source during ETL that will do the trick as well so you don't have to create the member in your MDX. So if you are able to do that that is the optimal solution. Doing it in your MDX query will affect performance as you have seen. — Nighty_, Mar 08 '16 at 11:08

score 0 · Answer 2 · answered Sep 20 '16 at 20:33

I had the same problem,

I have used the following ways,

WITH 
MEMBER SalesDateAsDateTime as
Iif([Measures].[WorkOrderCount]=0, null, [Sales Date].[Calendar Date].CurrentMember.MEMBER_VALUE)

WITH 
MEMBER SalesDateAsDateTime AS
cdate(format([Sales Date].[Calendar Date].CURRENTMEMBER.MEMBER_VALUE, "yyyy-MM-dd")) 

WITH MEMBER [Measures].[SalesDateAsDateTime] AS 

([Sales Date].[Calendar Date].CurrentMember.MEMBERVALUE)

But, all these takes a longer time to get the results, finally i have added a date measure in the cube directly for that MAXIMUM

It's pretty quick

Datazen - SSAS Tabular Model - Dates not being considered

2 Answers2