Find last time all cubes were processed

Question

Find last time all cubes were processed

Elizabeth.Block

Hall of Fame

Points: 3516
More actions
June 30, 2015 at 1:03 pm

#148996

Is there an easy well to find out the last time all the cubes on a server were processed? I've got over 20 cubes on one of our SSAS instances and would like to find out the last time the cubes were processed and also the last time the cubes were accessed. I know I can run the code below to look at one cube but I'm looking for a way to do it on all cubes.
SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes
Any help would be greatly appreciated!
Thanks,
Elizabeth

Viewing 15 posts - 1 through 14 (of 14 total)

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Would appear you'd need to create some outer loop (maybe using SSIS to do this?) -you can't join a query of the catalogs and cube_schemas.

Steve.

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 2

SSIS might work but I wouldn't know where to start with that.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Elizabeth.Block (6/30/2015)
Is there an easy well to find out the last time all the cubes on a server were processed? I've got over 20 cubes on one of our SSAS instances and would like to find out the last time the cubes were processed and also the last time the cubes were accessed. I know I can run the code below to look at one cube but I'm looking for a way to do it on all cubes.
SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes
Any help would be greatly appreciated!
Thanks,
Elizabeth

I run the query below on my server, I get one record per cube.

SELECT Cube_Name, LAST_DATA_UPDATE

FROM OPENQUERY(SSAS_METADATA, 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES')

WHERE CUBE_SOURCE = 1

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

I have a linked server on every sql instance that also has SSAS installed on the same box.

i can query the linked server for some of those attributes.

--#################################################################################################

--Create our Standard Linked Server to our local SSAS instance if it does not exist.

--#################################################################################################

IF NOT EXISTS(SELECT * FROM master.sys.servers WHERE name = 'SSASLocal')

BEGIN

EXEC master.dbo.sp_addlinkedserver @server = N'SSASLocal', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSASLocal',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'remote proc transaction promotion', @optvalue=N'true'

END

exec sp_tables_ex [SSASLocal]

SELECT *

FROM Openquery([SSASLocal], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') AS a

SELECT Last_Schema_Update As lst,*

FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.mdschema_cubes') AS a

WHERE LEFT(convert(varchar(max), cube_name),1) <> '$'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 5

Alvin,

I tried running this on both the DB engine and the SSAS instance. The SSAS instance said The syntax for 'OPENQUERY' is incorrect.

The DB engine returned this error: Could not find server 'SSAS_METADATA' in sys.servers.

Alvin Ramard (6/30/2015)
Elizabeth.Block (6/30/2015)
Is there an easy well to find out the last time all the cubes on a server were processed? I've got over 20 cubes on one of our SSAS instances and would like to find out the last time the cubes were processed and also the last time the cubes were accessed. I know I can run the code below to look at one cube but I'm looking for a way to do it on all cubes.
SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes
Any help would be greatly appreciated!
Thanks,
Elizabeth
I run the query below on my server, I get one record per cube.
SELECT Cube_Name, LAST_DATA_UPDATE
FROM OPENQUERY(SSAS_METADATA, 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES')
WHERE CUBE_SOURCE = 1

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 6

Lowell,

I think this is really close to what I need. I created the linked server, which is helpful. The result of the second query is the information I need but still is only returning one row(for one cube) instead of returning 21 rows.

Lowell (6/30/2015)
I have a linked server on every sql instance that also has SSAS installed on the same box.
i can query the linked server for some of those attributes.
--#################################################################################################
--Create our Standard Linked Server to our local SSAS instance if it does not exist.
--#################################################################################################
IF NOT EXISTS(SELECT * FROM master.sys.servers WHERE name = 'SSASLocal')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'SSASLocal', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSASLocal',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'remote proc transaction promotion', @optvalue=N'true'
END
exec sp_tables_ex [SSASLocal]
SELECT *
FROM Openquery([SSASLocal], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') AS a
SELECT Last_Schema_Update As lst,*
FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.mdschema_cubes') AS a
WHERE LEFT(convert(varchar(max), cube_name),1) <> '$'

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Elizabeth.Block (6/30/2015)
Alvin,
I tried running this on both the DB engine and the SSAS instance. The SSAS instance said The syntax for 'OPENQUERY' is incorrect.
The DB engine returned this error: Could not find server 'SSAS_METADATA' in sys.servers.
Alvin Ramard (6/30/2015)
Elizabeth.Block (6/30/2015)
Is there an easy well to find out the last time all the cubes on a server were processed? I've got over 20 cubes on one of our SSAS instances and would like to find out the last time the cubes were processed and also the last time the cubes were accessed. I know I can run the code below to look at one cube but I'm looking for a way to do it on all cubes.
SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes
Any help would be greatly appreciated!
Thanks,
Elizabeth
I run the query below on my server, I get one record per cube.
SELECT Cube_Name, LAST_DATA_UPDATE
FROM OPENQUERY(SSAS_METADATA, 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES')
WHERE CUBE_SOURCE = 1

I have added the SSAS Server as a linked server using the name SSAS_METADATA

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 8

Alvin,

Thanks, I've created the linked server. When I run that query in master on the DB instance I still just get one row for one cube instead of 21 rows for 21 cubes.

Alvin Ramard (6/30/2015)
Elizabeth.Block (6/30/2015)
Alvin,
I tried running this on both the DB engine and the SSAS instance. The SSAS instance said The syntax for 'OPENQUERY' is incorrect.
The DB engine returned this error: Could not find server 'SSAS_METADATA' in sys.servers.
Alvin Ramard (6/30/2015)
Elizabeth.Block (6/30/2015)
Is there an easy well to find out the last time all the cubes on a server were processed? I've got over 20 cubes on one of our SSAS instances and would like to find out the last time the cubes were processed and also the last time the cubes were accessed. I know I can run the code below to look at one cube but I'm looking for a way to do it on all cubes.
SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes
Any help would be greatly appreciated!
Thanks,
Elizabeth
I run the query below on my server, I get one record per cube.
SELECT Cube_Name, LAST_DATA_UPDATE
FROM OPENQUERY(SSAS_METADATA, 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES')
WHERE CUBE_SOURCE = 1
I have added the SSAS Server as a linked server using the name SSAS_METADATA

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 9

I didn't mention that in SSAS I have 21 cube databases. Each cube database has 1 cube associated with it. I don't know if that makes a difference.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Elizabeth.Block (6/30/2015)
I didn't mention that in SSAS I have 21 cube databases. Each cube database has 1 cube associated with it. I don't know if that makes a difference.

That's why you're only seeing 1 cube. The query is only querying one database.

You need to query each database separately. You could do a union of all the queries.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 11

Ah, that's it. I'm having trouble getting the syntax right for this. I created a temp table for the catalog names and then tried to get the last processed date.

CREATE TABLE #TempCatalogs(

Catalog varchar(50)

)

insert into #TempCatalogs

SELECT *

FROM Openquery(SSASLocalInstance, 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS')

--Find last process date for specific row with value BI_PSoft_TimeCard_Cube

SELECT Last_Schema_Update As lst,*

FROM Openquery(SSASLocalInstance, 'SELECT * FROM $SYSTEM.mdschema_cubes') AS a

WHERE convert(varchar(50),cube_name) = 'BI_PSoft_TimeCard_Cube'

This returns no results.

Alvin Ramard (6/30/2015)
That's why you're only seeing 1 cube. The query is only querying one database.
You need to query each database separately. You could do a union of all the queries.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 12

Elizabeth.Block (6/30/2015)
Ah, that's it. I'm having trouble getting the syntax right for this. I created a temp table for the catalog names and then tried to get the last processed date.
CREATE TABLE #TempCatalogs(
Catalog varchar(50)
)
insert into #TempCatalogs
SELECT *
FROM Openquery(SSASLocalInstance, 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS')
--Find last process date for specific row with value BI_PSoft_TimeCard_Cube
SELECT Last_Schema_Update As lst,*
FROM Openquery(SSASLocalInstance, 'SELECT * FROM $SYSTEM.mdschema_cubes') AS a
WHERE convert(varchar(50),cube_name) = 'BI_PSoft_TimeCard_Cube'
This returns no results.
Alvin Ramard (6/30/2015)
That's why you're only seeing 1 cube. The query is only querying one database.
You need to query each database separately. You could do a union of all the queries.

Can you log onto Analysis Services using SSMS and create a new DMX query:

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

What output do you see here?

Raunak J

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 13

Can you log onto Analysis Services using SSMS and create a new DMX query:
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS
What output do you see here?

Raunak,

This is perfect! I see all of the cubes on the server. Thanks so much!

Elizabeth

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 14

OK, one more question. The query I ran shows the last processed date if you right click on the cube database properties on SSAS, which is not the same date if you drill down into the cube properties. What is the difference between those 2 properties?