Background
This document will describe the steps needed to setup or modify your existing Date Dimension to support the use of Predefined Date Advanced Filter. The best example of how to properly set up your Date Dimension is to take a look at the Adventure Works sample cube from Microsoft, as this document will use it as an example.
Requirements
In order to use the Predefined Date Advanced Filter, your date dimension design must follow the below rules:
1. Must set a Date attribute as the Key (Usage). Refer to Diagram 1 below.
2. This attribute’s ValueColumn must refer to a Date data column. Refer to Diagram 1 below.
3. The type of the data column must be DateTime type. Refer to Diagram 2 below.
Query Examples
Below is a query used in Analyzer to filter This Year and This Month using the Date Advanced Filter. As shown, Analyzer uses a member’s MEMBERVALUE to determine the date:
1. This Year
SELECT
… …
FROM (
SELECT FILTER
(
- [Date].[Date].LEVELS(1).MEMBERS,
- (
- [Date].[Date].CURRENTMEMBER.MEMBERVALUE >= CDATE("1/1/2007") AND
- [Date].[Date].CURRENTMEMBER.MEMBERVALUE <= CDATE("12/31/2007")
- )
) ON 0 FROM [Adventure Works])
To put it briefly, Analyzer will first look for this dimension’s key attribute and then use its MEMBERVALUE to do the comparison.
- [Date].[Date].CURRENTMEMBER.MEMBERVALUE >= CDATE("1/1/2007")
2. This Month
SELECT
… …
FROM
(
- SELECT FILTER(
- [Date].[Date].LEVELS(1).MEMBERS,
- (
- [Date].[Date].CURRENTMEMBER.MEMBERVALUE >= CDATE("6/1/2007") AND
- [Date].[Date].CURRENTMEMBER.MEMBERVALUE <= CDATE("6/30/2007")
- )
- ) ON 0 FROM [Adventure Works]
)
***Please refer to the PDF format of this document***
0 Comments