SQL Server Performance

Open Query OLAP

Discussion in 'Analysis Services/Data Warehousing' started by piripi13, Mar 1, 2007.

  1. piripi13 New Member

    HI there,

    Ive been tasked with trying to monitor whether a number of Cubes have up to date data in them each day.

    Basically I just need the latest dates in cubes given back to me each day.

    Writting sprocs, and knowledge of Cubes and MDX etc are not my speciality to say the least. From what Ive read so far i'm thinking a possible way would be to create a stored proc that does an open query olap pointing to the cube in question and use a time selection parameter that would be built somewhere with parameters including

    Year presented as yyyy and uses the year part of getdate
    Month presented as the full month name
    Day presented as the integer got from getdate-1

    Has anyone out there done something similar or does anyone have an eample which i could work from by chance?

    Any help would be appreciated

    thanks
    p
  2. ranjitjain New Member

    Hi,
    Yes you can fire OPENQUERY to fire MDX on AS server and get the result.
    For that firstly you need to add ASserver in Linked server using sp_addlinkedserver.
    After adding it in linked server,
    Build your MDX query and then you can execute:

    SELECT * from OPENQUERY(Linkedservername,@MDXQuery)

  3. piripi13 New Member

    thanks

    Its the actual MDX query to get the up to date date I'm really after

    p
  4. ranjitjain New Member

    If you just need to populate the most recent date from cube using mdx then check this:

    select TAIL([Time Dim].[Day Begin].[Day Begin].members) on 0
    from [Cube Name]

Share This Page