Open Query OLAP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Open Query OLAP

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
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)
thanks Its the actual MDX query to get the up to date date I’m really after p
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]
]]>