sql-server-2012ssasbusiness-intelligencedashboarddatazen-server

Datazen - SSAS Tabular Model - Dates not being considered


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


Solution

  • 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]