The short answer is yes. The OLAP interface supports cubes with date only or with date and time dimensions.
Now, for the long answer:
Since all cubes are different, you will need to write a query that uses the correct dimension names, but the pattern is the same.
For cubes with date only, use STARTDATE, ENDDATE and TIMESTAMP keywords in your queries.
For cubes that include time as a dimension, you’ll use the new reserved keyword extensions:
StartDate
- {STARTDATE}
- {STARTDATE.Date}
- {STARTDATE.Time}
EndDate
- {ENDDATE}
- {ENDDATE.Date}
- {ENDDATE.Time}
TimeStamp
- {TIMESTAMP}
- {TIMESTAMP.Date}
- {TIMESTAMP.Time}
Note: If you have existing queries with both Date format and Time format, you will need to modify the trend queries to use {STARTDATE.Date} and {ENDDATE.Date}
Example Queries
Below are some example queries that incorporate the new keywords. Note the use of the keywords in each example:
- The Current Value query requires nothing special as the TAIL function handles it.
- The Trend Data query retrieves all data for each day on both ends of the query, then discards the unused portions of the days.
- The Historical Value query uses the .Time keyword.
For this example:
— Input {0} = KPI Name
— Input {1} = KPI Aggregation Type
Current Value Query
SELECT [Measures].[{1}] ON COLUMNS, {TAIL(NONEMPTY([Date].[Date].Members* [Time].[Time].[Time].ALLMEMBERS))} ON ROWS FROM [CubeName] WHERE ([KPI].[KPI].[{0}], [Time Zone].[Time Zone].[(UTC) Coordinated Universal Time])
Trend Data Query
SELECT {[Measures].[{1}]} ON COLUMNS, NONEMPTY{[Date].[Date].[{STARTDATE.Date}]:[Date].[Date].[{ENDDATE.Date}] * [Time].[Time].[Time].ALLMEMBERS} ON ROWS FROM [CubeName] WHERE ([KPI].[KPI].[{0}], [Time Zone].[Time Zone].[(UTC) Coordinated Universal Time])
Historical Value Query
SELECT [Measures].[{1}] ON COLUMNS, TAIL(NONEMPTY([Date].[Date].FirstChild:[Date].[Date].[{TIMESTAMP.Date}] * [Time].[Time].FirstChild:[Time].[Time].[{TIMESTAMP.Time}])) ON ROWS FROM [CubeName] WHERE ([KPI].[KPI].[{0}], [Time Zone].[Time Zone].[(UTC) Coordinated Universal Time])
The table shows details for how these OLAP queries work. (STARTDATE, ENDATE & TIMESTAMP behave the same way.)
Given: Input date of 12/25/2016 1:00:00 PM
Default Date Format: yyyy-MM-dd
Default Time Format: HH:mm
| Date Format | Time Format | Result for {TIMESTAMP} | Result for {TIMESTAMP.Date} | Result for {TIMESTAMP.Time} |
| NONE | NONE | 2016-12-25 13:00 Note: uses default Date & Time format | 2016-12-25 Note: uses default Date format | 13:00 Note: uses default Time format |
| NONE | HH:mm | 2016-12-25 13:00 Note: uses default Date format & User Defined Time format | 2016-12-25 Note: uses default Date format | 13:00 Note: uses User Defined Time format |
| yyyy-MM-dd | NONE | 2016-12-25 Note: uses default Date format ONLY | 2016-12-25 Note: uses default Date format | INVALID |
| yyyy-MM-dd | HH:mm | 2016-12-25 13:00 Note: uses User Defined Date & Time format | 2016-12-25 Note: uses User Defined Date format | 13:00 Note: uses User Defined Time format |