Processing Cubes After a Data Refresh: Batching Processing Using AMO

Recently I needed to update some cubes in a test environment after the underlying data warehouse had been refreshed. Typically I’d sync the cubes from prod, but as sync causes locking, and I needed to get this done during the day, I needed to reprocess all of the cubes in the test environment, and there were a lot of them, all ranging in sizes from hundreds of gb to a few mb. The simplest way to update the cubes was to run a process default on the dimensions and then run a process default on the measure groups. Some of the measure groups were partitioned, so I could’ve got clever and just processed fully any unprocessed partitions, but I felt a default would do most of the hard work for me. Continue reading

Don’t Be A Jerk, Use Approved Verbs in Powershell

Today I’m going on a mini rant about people abusing Powershell, specifically to do with naming Powershell functions. If you’re going to take the trouble to write Powershell functions and add them to a module for others to have access to, please, don’t be a jerk, use a verb from the approved verbs list that is freely and readily available on the MSDN Developers site, or even by typing “get-verb” in the console.

Continue reading

Update to vw_all_temp_db_usage View

Last month I posted a view that looked at TempDB usage. I use it primarily as a monitor for TempDB usage on instances that have databases that have Read Committed Snapshot isolation enabled.

Recently I made a change to the view; it includes the snapshot isolation level of the session. If it is using snapshots then would expect TempDB to be greater for this session/task.

if exists (select * from sys.sysobjects where name = 'vw_all_temp_db_usage')
drop view vw_all_temp_db_usage
GO
CREATE VIEW vw_all_temp_db_usage
AS
 SELECT
 u.session_id
 AS [spid],
 DB_NAME (s.database_id)
 AS [database],
 s.original_login_name
 AS [login],
 s.host_name
 AS [origin],
 u.internal_objects_alloc_page_count/128.0
 AS [allocated tempDb Session Object Space],
 u.user_objects_alloc_page_count/128.0
 AS [ allocated tempDb Task User Space],
 u.user_objects_dealloc_page_count/128.0
 AS [deallocated tempDb Task User Space],
 tsu.internal_objects_alloc_page_count/128.0
 AS [tempDb Task Object Space],
 tsu.user_objects_alloc_page_count/128.0
 AS [tempDb Task User Space],
 tsu.user_objects_dealloc_page_count/128.0
 AS [deallocated Task tempDb User Space],
 tsk.task_state
 AS [state],
 t.text,
 CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'ReadUncomitted'
 WHEN 2 THEN 'ReadCommitted'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END
 AS [transaction Isolation Level],
 qp.query_plan
 FROM sys.dm_db_session_space_usage u
 INNER JOIN sys.dm_exec_sessions s
 ON u.session_id = s.session_id
 INNER JOIN sys.dm_db_task_space_usage tsu
 ON s.session_id = tsu.session_id
 INNER JOIN sys.dm_exec_connections c
 ON c.session_id = s.session_id
 INNER JOIN sys.dm_os_tasks tsk
 on tsk.session_id = s.session_id
 INNER JOIN sys.dm_exec_requests req
 on u.session_id = req.session_id
 CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) t
 CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) qp
GO

--select * from all_temp_db_usage

Once you have run the above SQL you can execute as follows:


select * from vw_all_temp_db_usage

Automate SSAS Syncing Part 5: Syncing from Start to Finish

Hello! And welcome to the final part of this series. This script combines the first 4 parts of this series to create an unabridged process of synchronising a ssas database from one server to another. Even though I’ve used this script fora while, it still gets tweaks and I’d still consider it a work in progress, however it’s as complete now as it ever has been. No doubt when I make some more changes I’ll post an update.

The script so far includes:

  1. checking if sync role exists, and creates it if it does not
  2. checking the permissions on the role to ensure that the sync role has administer permissions on the database to be sync’d, and will add them if they do not
  3. checks that the service account of the target server is a member of the sync role (a requirement of syncing) and adds it if not a member
  4. checks if the source database exists on the target server. It will copy it in most cases (see 6) however if it does not exist it will disregard point 7
  5. checks that there is sufficient space on the disk to contain two copies of the database (see below for a full explanation), and contains the option to drop the target database before syncing.
  6. if there is no target database and there is not enough space to sync this database, it will skip syncing
  7. If the target database is to be dropped before syncing, then the target roles/permissions/data source are stored and will replace the updated database values (assuming it existed on target server prior to syncing)
  8. This assumes that you are not copying over the permissions from the target cube. The idea here being that these cubes are used for a different environment (like test for example) that have their own datasource/roles/members.

Continue reading

Automate SSAS Syncing Part 3: Using PowerShell to Check for Disk Space on SSAS Instances

Hello, and welcome to part 3 of the series on syncing databases across servers. The first two parts focused on ensuring that the role exists on the source server with the appropriate permissions to sync. We then check and add the service account of the target instance to ensure that the sync can occur.

Continue reading

Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

Part two of this series expands upon yesterdays post, which was creating a database role and assigning permissions, by adding a member to that role. The script adds just one member to the role, but if you need more than one member you can either create a domain group and add all the users required into that group, then use the group name in place of the user name, or do something clever with arrays.

Tomorrow’s post will look at some disk checks that are included to ensure there is enough space to sync cubes.

Here are the links for the other parts of the series
Automate SSAS Syncing Part 1: Create SSAS Database Role
Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

Continue reading

Automate SSAS Syncing Part 1: Create SSAS Database Role

Hello!

Every day this week I am going to be posting part of a script that I recently wrote, but broken down into separate components that are useful in their own right. The full script, which I will be sharing at the end of the week, automates the synchronisation of SSAS cubes from one instance to another.

So to make this an automated process that was robust to any deviation I had to make sure that the destination service account has full admin access to the source database, that the destination disk could stand the anticipated growth as well as the copy of the sync’d cube, and that the datasource’s connection string on the destination database was reset to it’s original value. Typically the datasource does not need updating, but the destination databases are used in a different environment with their own datasource. And as the sync also syncs the datasource it is necessary to update the datasource back to its original value Continue reading

Get CubeInfo

Hello!

Here’s a simple Powershell script here to get the top level info of the cubes on an instance. This is a good script to understand how to use the AMO namespace to get info on the cubes via Powershell. You can add many other properties to this list to expand the info that you want. I’ve often mused about moving a lot of the logic that I constantly put in these scripts into higher tier functions in Powershell, or even create a ssas library, but time is always a premium… Continue reading