Knowledge Base

Search
Generic filters
Exact matches only
  1. Home
  2. Knowledge Base
  3. Questions & Answers (FAQ)
  4. Can the OLAP Interface be used for data which has both a time and date field in the cube?

Can the OLAP Interface be used for data which has both a time and date field in the cube?

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

 

 

Was this article helpful?

Yes No

Related Articles