Problem using Date fields with SSAS tabular and an XMLA endpoint

Hi Maksym,

Thanks for your response. We would prefer to continue to use our XMLA endpoint as it supports a number of features required by our customers, such as logging queries and results, handling some security issues etc. We also use direct connections to Azure AS and are not in a position to implement any middleware, so Flexmonster Accelerator is not really a viable solution for us at the moment.

Our proxy works fine with dates when used in excel, surely you can recognize date fields when delivered through the XMLA endpoint.

Thanks,

Brian

Hi Maksym,

Thanks for following up.

I'm working on this issue as well.

Can you give us more information on the format type that needs to be assigned to the [Date] fields from the XMLA handler between SSAS and Flexmonster?

Here's what I can see from the msmdpump request in the demo you linked:

<row>
<CATALOG_NAME>Adventure Works DW Standard Edition</CATALOG_NAME>
<CUBE_NAME>Adventure Works</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Date]</DIMENSION_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Date].[Date]</HIERARCHY_UNIQUE_NAME>
<LEVEL_NAME>(All)</LEVEL_NAME>
<LEVEL_UNIQUE_NAME>[Date].[Date].[(All)]</LEVEL_UNIQUE_NAME>
<LEVEL_CAPTION>(All)</LEVEL_CAPTION>
<LEVEL_NUMBER>0</LEVEL_NUMBER>
<LEVEL_CARDINALITY>1</LEVEL_CARDINALITY>
<LEVEL_TYPE>1</LEVEL_TYPE>
<DESCRIPTION/>
<CUSTOM_ROLLUP_SETTINGS>0</CUSTOM_ROLLUP_SETTINGS>
<LEVEL_UNIQUE_SETTINGS>0</LEVEL_UNIQUE_SETTINGS>
<LEVEL_IS_VISIBLE>true</LEVEL_IS_VISIBLE>
<LEVEL_ORDERING_PROPERTY>(All)</LEVEL_ORDERING_PROPERTY>
<LEVEL_DBTYPE>3</LEVEL_DBTYPE>
<LEVEL_KEY_CARDINALITY>1</LEVEL_KEY_CARDINALITY>
<LEVEL_ORIGIN>6</LEVEL_ORIGIN>
</row>

Our handler also returns a similar result on our cube:

<row>
<CATALOG_NAME>BIMS</CATALOG_NAME>
<CUBE_NAME>Model</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Date]</DIMENSION_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Date].[Date]</HIERARCHY_UNIQUE_NAME>
<LEVEL_NAME>(All)</LEVEL_NAME>
<LEVEL_UNIQUE_NAME>[Date].[Date].[(All)]</LEVEL_UNIQUE_NAME>
<LEVEL_CAPTION>(All)</LEVEL_CAPTION>
<LEVEL_NUMBER>0</LEVEL_NUMBER>
<LEVEL_CARDINALITY>0</LEVEL_CARDINALITY>
<LEVEL_TYPE>1</LEVEL_TYPE>
<CUSTOM_ROLLUP_SETTINGS>0</CUSTOM_ROLLUP_SETTINGS>
<LEVEL_UNIQUE_SETTINGS>0</LEVEL_UNIQUE_SETTINGS>
<LEVEL_IS_VISIBLE>true</LEVEL_IS_VISIBLE>
<LEVEL_ORDERING_PROPERTY>(All)</LEVEL_ORDERING_PROPERTY>
<LEVEL_DBTYPE>3</LEVEL_DBTYPE>
<LEVEL_KEY_CARDINALITY>1</LEVEL_KEY_CARDINALITY>
<LEVEL_ORIGIN>2</LEVEL_ORIGIN>
</row>

Also I noticed in the documentation it says "For Microsoft Analysis Services, Flexmonster uses date formatting configured inside the cube."

Our Date table has the following Date column configured in the cube and it's similar with the same datatype as the AdventureWorks demo you linked:

{
"name": "Date",
"dataType": "dateTime",
"sourceColumn": "Date",
"formatString": "General Date",
"sourceProviderType": "DBDate"
}

I can see a difference in the value format in the XMLA handler response - could this make a difference?
DEMO

<Member Hierarchy="[Date].[Date]">
<UName>[Date].[Date].&amp;[20110124]</UName>
<Caption>January 24, 2011</Caption>
<LName>[Date].[Date].[Date]</LName>
<LNum>1</LNum>
<DisplayInfo>131072</DisplayInfo>
<PARENT_UNIQUE_NAME>[Date].[Date].[All Periods]</PARENT_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Date].[Date]</HIERARCHY_UNIQUE_NAME>
<MEMBER_TYPE>1</MEMBER_TYPE>
</Member>

OURS

<Member Hierarchy="[Date].[Date]">
<UName>[Date].[Date].&amp;[2014-01-01T00:00:00]</UName>
<Caption>1/1/2014</Caption>
<LName>[Date].[Date].[Date]</LName>
<LNum>1</LNum>
<DisplayInfo>0</DisplayInfo>
<PARENT_UNIQUE_NAME>[Date].[Date].[All]</PARENT_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Date].[Date]</HIERARCHY_UNIQUE_NAME>
<MEMBER_TYPE>1</MEMBER_TYPE>
</Member>

Thank you Maksym,

This got us thinking.

As per your documentation which said that the date format needs to be configured on the cube, we found there was a property on the cube's Date table to set for Flexmonster to recognize it as a date. 

{
"create": {
"parentObject": {
"database": "AventureWorksTabular"
},
"table": {
"name": "Date",
"dataCategory": "Time",

Maybe this could be modified on the XMLA handler as you mentioned too, we haven't tried.