Post reply

MDX Filter by Date

  • July 6, 2010 at 6:14 am

    #157058

    I have 2 date columns - StartDate & EndDate in fact table. So in effect these will go to the fact dimension table.

    The user will select a date in the web page and this date will be passed. My MDX query should be able to return the records which satisfy this below criteria

    -- T-SQL

    declare @dtdate datetime

    SELECT * FROM factable where @dtdate between Startdate and EndDate

    How do i translate above in MDX to retrieve records from the cube. Please help.

    Thanks,

    Sugavaneswaran.S

  • July 8, 2010 at 4:39 pm

    #1190550

    try this...

    SELECT MeasureGroupMeasures('Fact Internet Sales') ON 0,

    {[Order Date].[Full Date Alternate Key].&[2001-07-01T00:00:00]

    : [Order Date].[Full Date Alternate Key].&[2001-07-10T00:00:00]} ON 1

    FROM dsvAdventureWorksDW

    MeasureGroupMeasures is a function that brings back all the measures in a fact table

    The from and to date are a range on the rows.

    Suggest from yr relational database create a linked server into yr OLAP database then use OPENQUERY to send in the mdx. The mdx string is replaced with the from and to dates with yr sp parameters

    e.g sp_GetFactData @startDate varchar, @EndDate varchar

    DECLARE mdxstring varchar(1000)

    SET mdxString = 'SELECT MeasureGroupMeasures(''Fact Internet Sales'') ON 0,

    {[Order Date].[Full Date Alternate Key].&' + @startDate + 'T00:00:00]

    : [Order Date].[Full Date Alternate Key].&' + @EndDate + 'T00:00:00]} ON 1

    FROM dsvAdventureWorksDW'

    SELECT * into #temp FROM OpenQuery(OLAP, mdxstring)

    with in #temp you can do further jigging if you wish since data is now in a relational table

    finally do a SELECT * from #temp for record set to be sent back to web page

    send in start/end date as yyyy-mm-dd ...you get my drift

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply