tim laqua dot com Thoughts and Code from Tim Laqua

23Jan/132

Hiding SSAS Attributes With Powershell

The challenge here was that we have a cube with some great data as well as some low level detail (customer detail, think PII) that we want to expose to a larger audience. The problem is that the low level detail is not something that the new audience needs (or in some cases, is allowed) to have access to. The obvious answer here is Dimension security - why not use that? Two reasons. First, Dimension security is slow. Second, even if it wasn't slow, to hide all the data in many (30+) attributes is tedious to setup, and when new attributes were added we would have to make sure and disable access to those as well. To be clear, we're not just hiding attributes here, we're creating an entire copy of the existing SSAS database and hiding attributes in the copy.

# Import Required Libraries
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
 
# Connect to server
$server = New-Object Microsoft.AnalysisServices.Server
$server.Connect('localhost')
 
# Make a metadata copy of the existing cube
$newdb = $server.Databases.GetByName('POS Data').Clone()
 
# Change ID and Name of SSAS DB
$newdb.Name = 'POS Data - Limited'
$newdb.ID = 'POS Data - Limited'
 
# Drop Existing SSAS DB if it exists
$server.Databases['POS Data - Limited'].Drop()
 
# Add the new copy to the server
$server.Databases.Add($newdb)
 
# Sync our copy of the new database with the server's copy
$newdb.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
 
# Grab the cube we want to work with from the new database
$cube = $newdb.Cubes.GetByName('POS Data')
 
# Hide the Location Dimension
$cube.Dimensions.GetByName('Location').Visible = $false
 
# Hide all attributes and hierarchies in the Customer dimension
$cube.Dimensions.GetByName('Customer').Attributes |  %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}
$cube.Dimensions.GetByName('Customer').Hierarchies | %{$_.Visible=$false; $_.Enabled=$false;}
 
# Enable the key attribute in the customer dimension - it won't work if the key isn't enabled
$cube.Dimensions.GetByName('Customer').Attributes['Dim Customer'].AttributeHierarchyEnabled=$true
 
# Enable the Market attribute in the customer dimension
$cube.Dimensions.GetByName('Customer').Attributes['Market'] | %{$_.AttributeHierarchyEnabled=$true; $_.AttributeHierarchyVisible=$true;}
 
# Hide the Location Database attribute in the Product dimension
$cube.Dimensions.GetByName('Product').Attributes['Location Database'] | %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}
 
# Add a new member to the role granting Read Only permissions in the cube
$newMember = new-object Microsoft.AnalysisServices.RoleMember("domain\tlaqua")
$newdb.Roles['Role 1'].Members.Add($newMember)
 
# Push our updates to the server
$newdb.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
 
# Process the new database
$newdb.Process([Microsoft.AnalysisServices.ProcessType]::ProcessFull)
 
# Disconnect from the server
$server.Disconnect()

This approach has two notable downfalls. First, you have to think up a different database name because the original db with the low level detail still exists on the server. Second, you have to ProcessFull the clone that you made. It doesn't close the data, it clones the metadata. All in all, works great for us, this particular cube is only processed once a week and the 20 minutes we lose processing data in to the clone is more than acceptable (looks really clean to the users as well).

19Oct/111

Monitoring and Starting Data Collector Sets with Powershell

I figure it's about time I posted an update to my previous post on Monitoring, Starting, and Stopping Perfmon Performance Counter Logs using VBScript. In the years since that post, a few operating systems came out and many much more interesting languages happened by. So this update is in Powershell. You could do it in pretty much any language, but Powershell seems to be my weapon of choice lately.

The basic premise here is that we want our Data Collector Sets to always be running. Seems like a reasonable request, but for various reasons, Data Collector Sets just... stop. This script should be run regularly (every 5 minutes?) to ensure that the sets keep collecting data. In the event that the script finds some work to do, it will send you an email.

#======= START Config ========
$emailServer = "1.2.3.4";
$fromAddress = "you@yourcompany.com";
$toAddress = "you@yourcompany.com";
$ignorePattern = '^(RandomCollectorSet|OSTest)$';
#======== END Config =========
 
$ErrorActionPreference = "SilentlyContinue"
$serverName = Get-Content env:computername;
$emailBody = ""
 
$collectors = New-Object -COM Pla.DataCollectorSetCollection;
$collectors.GetDataCollectorSets($null, "Service\*");
$collectors._NewEnum | ?{$_.Status -ne 1 -and $_.Name -notmatch $ignorePattern} | %{
	$_.Start($true);
	if($_.Status -eq 1) {
		$emailBody += "Started $($_.Name)`n"
	} else {
		$emailBody += "Failed to start $($_.Name)`n"
	}
};
 
if($emailBody -ne "") {
  $smtp = new-object Net.Mail.SmtpClient($emailServer);
  $smtp.Send(
    "$serverName <$fromAddress>", 
    $toAddress, 
    "Started Data Collector Set(s)", 
    $emailBody);
}

If you want the script to keep any of the non-user created collectors running, modify the namespace parameter of the GetDataCollectorSets method call.

Tagged as: 1 Comment
8Jun/1128

Revisiting Embedded Text Qualifiers and the SSIS Flat File Connection Manager

To quickly summarize the problem at hand, the Flat File Connection Manager doesn't support embedded Text Qualifiers. The reason this is a big issue is because the most commonly used text qualifier is a double quote (") and that's also a commonly used punctuation mark. That means if you have a CSV to import and lines that looks like this:

"one","1","two","2","th""r""ee","3","asdf"
"""left","right""","mid""dle","w"",tf","w,""tf",",""omg","omg"","

You would expect to import

one 1 two 2 th"r"ee 3 asdf
"left right" mid"dle w",tf w,"tf ,"omg omg",

That's not what you get, you get an error. I've seen a few different approaches to working around this known issue:

What I'd like to explore in this post is using regular expressions to transform the entire file in to something that can be natively consumed by SSIS. There are two options for dealing with this:

  • Tokenizing the quotation marks found within the cells
  • Change the text qualifier

I chose to change the text qualifier because SSIS natively supports multiple character text qualifiers. This will allow me to pick something ridiculous that is incredibly unlikely to appear in the data naturally. Changing the text qualifier also means we won't have to add any extra transformations as we would if we tokenized the embeded quotation marks (to turn them back in to quotation marks).

15Apr/111

Restarting Automatic Services using Powershell

What? Why would you need to restart an automatic service? Because they die sometimes - there's probably some very good reasons for this, but I don't know them. What I do know is they die from time to time and don't come back. Sometimes they never get around to starting after a reboot.

I run the following Powershell script every 5 minutes to restart the ones that got tired:

#======= START Config ========
$emailServer = "1.2.3.4";
$fromAddress = "you@yourcompany.com";
$toAddress = "you@yourcompany.com";
$ignorePattern = '^(ShellHWDetection|MMCSS|sppsvc|clr_optimization.*)$';
#======== END Config =========
 
Get-WmiObject Win32_Service | 
? { 
  $_.StartMode -eq 'Auto' `
  -and $_.State -ne 'Running' `
  -and $_.Name -notmatch $ignorePattern
} | 
% { 
  $svcName = $_.Name; 
  $serverName = Get-Content env:computername;
  Start-Service -Name $_.Name -EA SilentlyContinue; 
  $smtp = new-object Net.Mail.SmtpClient($emailServer);
  $smtp.Send(
    "$serverName <$fromAddress>", 
    $toAddress, 
    "Started service ""$svcName""", 
    "Started service ""$svcName"" on $serverName.")
}

$ignorePattern is a regular expression for identifying what services to ignore (so services that you don't care to try and restart).

Tagged as: 1 Comment
15Apr/112

Dealing With Long Running SSAS Queries using Powershell

So, your Analysis Services instance keeps eating all the memory on your server despite proper memory configuration? This happens when queries get too big for their britches - SSAS memory limits are basically soft limits. Whatever an active query requires in terms of resources, SSAS will attempt to provide it. Even if it has to page the entire operating system out to disk to do it. You can mitigate this with good cube design, better aggregations, user training, etc - but it's bound to happen. When your cube gets big enough, one of your users will blow the entire server up.

11Feb/100

VersionOne Nag: Reminding Us To Burn Our Points

You'll forget - we all do. After a hard day of work, the last thing that pops in to your head is "hey, I should go update this story in VersionOne before I go home." Oh no, the only thing you're thinking about is how bad traffic will be, what you're having for dinner, your trip to Seattle, which bar has the best drink specials tonight, etc. So accepting that we forget to burn points now and then, why don't we just make a script to remind us? While you're at it, might as well add a reminder for when you accidentally close a story without setting the ToDo points to zero.

Luckily, since you obviously use VersionOne (because it's the gold standard), there's an API for that: