Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 33
This chapter covers the ADOMD.NET provider. Some of the chapters in this book only have a few pages, essentially a short essay. This chapter has over 60 pages, and therefore I would consider it a small book. Though, developers might feel even this length is too short. Hopefully, the sample code provides some compensation. This blog post is long, and I spent more time because the book comes with a lot of sample code in C#.
Outline:
- Gentle Introduction to ADOMD.NET — Basics about this topic with some web links. This provider is important for Analysis Services and therefore is also important for SQL Server Data Mining.
- Gentle Introduction to PowerShell 2.0 — Command prompt has been around since I first used BASIC on the TRS-80 (in the 1980s). If you are a developer you should not be using the command window anymore. PowerShell does everything the command window can do and more.
- Project by Project Commentary on Sample Code — Coverage of the many C# solutions provided with the book. When the authors wrote a C# console application, I rewrote a PowerShell 2.0 translation of the code. I will provide my PowerShell 2.0 code (which you can compare with the freely downloadable code from the book authors). Though none of the examples explicitly discuss data mining, the PowerShell scripts provide insight on how to use PowerShell with SQL Server Data Mining.
Gentle Introduction to ADOMD.NET
If you are developing with Visual Studio, then you will like this chapter. The text says “you don’t have to use Visual Studio to use ADOMD.NET, but your life as a developer will be easier if you do.” (page 599) I personally have not used anything other than Visual Studio (or Microsoft administrative products, as appropriate) to create Microsoft language compiled code or scripts. On my current computer, I am using Visual Studio 2010 Ultimate, and that software (or a comparable earlier version) enables you to examine and run the included sample solutions that the authors created for this chapter:
The names of the folders in my screenshot (above) refer to the listing number. The last two items, therefore, refer to listing 2 (page 607) and listing 27 (page 653). An alternative way to name the folders would have been <LL>-<NAME>-<CH> where <LL> is the listing number, <NAME> is the friendly name, and <CH> is the chapter number. Or perhaps <CH>-<LL>-<NAME> making sure that each listing number has two digits (so that 2 becomes 02). Using these latter naming schemes would put the folders into sequential order. Changing the names is like adding an index to a table.
I have to comment on the book’s philosophy on how Analysis Services supports analytical applications:
A typical user who works with multidimensional data goes through a process that is different from that used by a user of relational data. From the first view of aggregated data, he is likely to drill down and request more and more detailed data… He rarely asks for a static report; he works in constant communication with the server. Thus, an application for working with multidimensional data requires more flexibility and a higher level of interactivity. The application has to connect to a multidimensional data source, discover the structure of the stored data, and allow the user to query and drill down into the data. (page 602)
I believe this philosophy is not universally supported by the facts. The statement makes claims about flexibility and higher level of interactivity. I take a holistic view of technology and draw a systems circle the same way my mentor W. Edwards Deming did: the circle not only includes the organization itself, but more largely the customers and vendors too. My viewpoint is perhaps larger than the typical computer scientist, but I know that C-level executives making decisions about enterprise systems have that same viewpoint. I would not be confident in presenting Analysis Services as more flexible and designed for a higher level of interactivity to whatever current transactional system people were using (whether the world-class SQL Server, or comparable products which may exist alongside like SAS, DB2, Teradata, Oracle). I logically find it hard to believe that a high-volume transactional enterprise database has a lower level of interactivity compared to Analysis Services.
I make these statements because I believe that historically and currently, developers are using relational systems behind their highly interactive applications because it is the model they know from school or training or experience. To make a statement about interactivity would make me wonder of the world’s population of highly interactive applications (which I would guess in both number and hours of use would include partly or complete web-based development) what back-end technology do those systems connect with. I would prefer that the Analysis Services team make statements about how their technology is strong, and where it is stronger than transactional systems for interactive development. I am not sure that anyone can make a strong case that, for example, Analysis Services is more flexible than SQL Server on all possible comparative dimensions. I believe commenting on this philosophy is important because this chapter was written for developers more than DBAs (in my opinion) and this section is titled “Writing Analytical Applications”. I believe it would be a shame to go as deep as the authors (and I, through PowerShell) will go into ADOMD.NET and have an incomplete understanding about where this technology fits in a larger infrastructure. Decision makers will latch on to statements like the one I quoted and will likely not be energized or interested about the ADOMD.NET details which largely comprise this chapter.
Before examining the sample code, I will list some links for documentation:
- ADOMD.NET“ADOMD.NET is a Microsoft .NET Framework data provider that is designed to communicate with Microsoft SQL Server Analysis Services. ADOMD.NET uses the XML for Analysis protocol to communicate with analytical data sources by using either TCP/IP or HTTP connections to transmit and receive SOAP requests and responses that are compliant with the XML for Analysis specification. Commands can be sent in Multidimensional Expressions (MDX), Data Mining Extensions (DMX), Analysis Services Scripting Language (ASSL), or even a limited syntax of SQL, and may not return a result. Analytical data, key performance indicators (KPIs), and mining models can be queried and manipulated by using the ADOMD.NET object model. By using ADOMD.NET, you can also view and work with metadata either by retrieving OLE DB-compliant schema rowsets or by using the ADOMD.NET object model.” Retrieved from http://msdn.microsoft.com/en-us/library/ms123483.aspx
- Microsoft SQL Server 2008 Feature Pack, April 2009 Download“ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.” Retrieved from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4
The download allows developers to include this provider in redistributed programs. On the link I cited, there are three different versions: x86, x64, and ia64. Microsoft periodically releases feature packs for SQL Server which include the latest versions of redistributable software such as the ADOMD.NET data provider.
Gentle Introduction to PowerShell 2.0
This version comes with Windows 7, and on my computer I am running the 64-bit version. To make the PowerShell code for this blog post I used the ISE (Integrated Scripting Environment) but sometimes I used the regular PowerShell interactive session. I added both to my quick links.
The next screen shot shows the ISE, and this interactive script writer makes *.PS1 files (I do not know where the 1 of PS1 comes from, though PS probably means PostScript to many people). I put a red arrow where the screen allows you to choose the view (since I like the view with the code on the right and the output on the left); this view works good if you have a wide screen monitor. The last view is one window at a time, good if you have a small monitor (or are using say a netbook or mobile device). I will point out the Debug menu item which works similar to how Visual Studio works — you can set break points and step through code. The Add-Ons menu item means that PowerShell can be extended — you can write your own extensions, or see what other extensions people make for free on the open source portal codeplex.com. The Help menu provides a more traditional format for help; by contrast, if you see a majority of PowerShell books and presentations, they will inevitably talk about get-help in the interactive session, which provides not only help but optionally examples. I prefer the interactive session because the scripts are saved (and in my discipline, dated). I like that the interactive session will ask you about saving your work when you press the green run arrow (if you have not already saved it with CTRL-S) because a random script might lock up your computer and you may have to reboot, thus losing whatever you had in memory (not your memory, the computer memory).
The interactive session can run the same code, and if you read a book on PowerShell or see a presentation on this topic, you will typically see more about the interactive session. Some PowerShell geeks try to see what they accomplish in one line of coding (using a pipe feature), and because PowerShell uses objects, a lot can happen in a short amount of code. Being based on the CLR (common language runtime), PowerShell has the ability to reach all parts of .NET (of course, given the proper authentication and authorization). You have the most power when you run either the interactive session or the ISE with Administrator privileges. Did I mention that PowerShell is free? You may already have it installed on your computer.
If you do not have it, I recommend the following link (which is different from where Bing or the Microsoft website will send you first, but feel free to surf around since they have a lot of information about PowerShell in different places): http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx At the end of this post I will recommend three books on PowerShell.
Project by Project Commentary on Sample Code
This long section will look at the sample code one project at a time. I have been sharing code on this blog before, but this time I decided to do something better. I made a .CODE CSS class which has the formatting, and after several failed experiments with the tags (since WordPress decides to add
tags, for example, inside the code blocks), I ended up with
. The PRE HTML tags preserve the original formatting, meaning the spaces, in a monospaced way without adding extraneous and perhaps detrimental HTML tags or substitutions. As a result, the code may not display neatly inside the predefined column sizes (and some percentage of my readers are on mobile devices like the iPhone), but cutting and pasting this code works well from a browser window. I therefore sacrificed looks for functionality. This type of tradeoff shows my developer instincts. If I were only concerned about looks, I would have instead done a screen shot of the code. However, I want this code to be available to the search engines and available for use too. I have seen other Javascript options available, but I’m staying with my method for now.
ADOMDShareSession:
# 3302_AdomdShareSession.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
[Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Create the first connection object
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=Foodmart 2008;"
$con.Open()
# Create a command and send a request that will create a new calculated member on the session
$command = $con.CreateCommand()
$command.CommandText = "create member [Warehouse and Sales].Measures.MyCalcMember as 1"
$command.ExecuteNonQuery()
# Check to make sure that the calculated member was created.
if ($null -ne $con.Cubes["Warehouse and Sales"].Measures.Find("MyCalcMember"))
{
write-host " Measure 'MyCalcMember' is created on the session from the first connection. "
}
else
{
write-host " Measure 'MyCalcMember' is not created on the session from the first connection. "
}
# Create the second connection object
$con2 = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con2.ConnectionString = "Datasource=localhost; Initial Catalog=Foodmart 2008;"
# Set the session identifier of the first connection in the second connection object
$con2.SessionID = $con.SessionID
# Open the second connection
$con2.Open()
# Check to make sure that the calculated member created by first connection is available on the second connection
if ($null -ne $con2.Cubes["Warehouse and Sales"].Measures.Find("MyCalcMember"))
{
write-host " Measure 'MyCalcMember' is awailable on the session from the second connection. "
}
else
{
write-host " Measure 'MyCalcMember' is not awailable on the session from the second connection. "
}
# Close the second connection
$con2.Close()
# Close the first connection
$con.Close()
This code opens two sessions simultaneously. The pound symbol # indicates a comment. PowerShell 2.0 adds the block comments through <# block comment #>. The dollar sign $ indicates a variable, which can be of any object type. PowerShell advances declarative programming in assigning a type without necessarily having an implicit statement (though you always can if you want). I have to snicker when I hear that declarative languages are “new” because that implicit translation is what I know from BASIC programming a long time ago, and largely how many languages like SAS work already.
ADOMDIterateDatabases:
Using a simple form, this solution connects to the Analysis Services instance and lists the databases in a drop-down box.
ADOMDIterateMetadata:
# 3304_AdomdIterateMetadata.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
[Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Connect to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Iterate the objects in the collection of cubes
foreach ($cube in $con.cubes)
{
write-host "Cube Name: " $cube.Name
# Iterate the objects in the dimensions collection
foreach ($dim in $cube.Dimensions)
{
write-host " Dimension Unique Name: " $dim.UniqueName
# Iterate the objects in the hierarchies collection
foreach ($hier in $dim.Hierarchies)
{
write-host " Hierarchy Unique Name: " $hier.UniqueName
# Iterate the objects in the levels collection
foreach ($level in $hier.Levels)
{
write-host " Level Unique Name: " $level.UniqueName
}
}
}
}
$con.Close()
This code iterates through a cube and prints out data. PowerShell can pipe results to a file too, and even make HTML formatted files.
ADOMDDimensionOrdinal:
# 3305_AdomdDimensionOrdinal.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
[Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Connect to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
$cube = $con.cubes["Warehouse and Sales"]
# Iterate over the dimensions collection
foreach ($dim in $cube.Dimensions)
{
write-host "Dimension: '" $dim.Name "' Ordinal= " $dim.Properties["DIMENSION_ORDINAL"].Value.ToString()
}
$con.Close()
This script obtains ordinal values. In my PowerShell translations not everything is identical with how C# 2.0 sees the world. I kept, for example, the ToString() casting because I can, though PowerShell does an implicit casting to string when using write-host. If an object can be displayed as a string, then PowerShell will try to display it using logic which varies by type.
ADOMDGetMembers:
# 3312_AdomdExecute.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
function AdomdExecuteCommandText
{
write-host "AdomdExecuteCommandText"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create the object AdomdCommand
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
$command.Connection = $con;
# Define the text the request
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
# Execute command...
# …
# Close the connection
$con.Close
}
################################################################
function AdomdExecuteCommandStream
{
# This function assumes that you have a file called "Foodmart2008Metadata.xml" and that the "Food Mart 2008" Analysis Services database does NOT exist
write-host "AdomdExecuteCommandStream"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost;"
$con.Open()
# Open a stream for reading a file that contains the XML for creating a cube
$sr = new-object System.IO.StreamReader("..\\..\\Foodmart2008Metadata.xml", $true)
$command = $con.CreateCommand()
$command.CommandStream = $sr.BaseStream
# Execute the command
$command.ExecuteNonQuery()
# Close the stream
$sr.Close()
}
################################################################
function AdomdExecuteCommandCellSet
{
write-host "AdomdExecuteCommandCellSet"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a simple MDX request
$cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText,$con)
# Execute a method, that returns CellSet
$cellset = $command.ExecuteCellSet()
$cellset
# Close the connection
$con.Close()
}
################################################################
function AdomdExecuteCommandDataReader
{
write-host "AdomdExecuteCommandDataReader"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a simple MDX statement
$command = $con.CreateCommand()
[string] $query = "SELECT Measures.Members ON columns FROM [Warehouse and Sales]"
$command.CommandText = $query
# Execute the method that returns IDataReader
$reader = $command.ExecuteReader()
# Read the metadata about the columns in the returned result
$schemaTable = $reader.GetSchemaTable()
write-host "Schema"
for([int] $iSchRow = 0; $iSchRow -lt $schemaTable.Rows.Count; $iSchRow ++)
{
for([int] $iSchCol = 0; $iSchCol -lt $schemaTable.Columns.Count; $iSchCol ++)
{
write-host $schemaTable.Rows[$iSchRow][$iSchCol].ToString() " "
}
}
# Read the result
while($reader.Read())
{
for([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol ++)
{
write-host $reader.GetValue($iCol).ToString()
write-host $reader.GetDataTypeName($iCol)
}
}
# Close DataReader
$reader.Close();
# Close the connection
$con.Close();
}
################################################################
function AdomdExecuteCommandXmlReader
{
write-host "AdomdExecuteCommandXmlReader"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a command
$command = $con.CreateCommand()
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
# Execute the command and get XMLReader
$reader = $command.ExecuteXmlReader()
# Write the XML into a file -- use the results of GetCurrentDirectory to see where the file goes
[IO.Directory]::GetCurrentDirectory()
[string] $xml = $reader.ReadOuterXml()
$writer = new-object System.IO.StreamWriter("cellSetXml.xml", $false)
$writer.Write($xml)
# Close the file
$writer.Close()
# Close XmlReader
$reader.Close()
# Close the connection
$con.Close()
}
################################################################
function AdomdLoadCellSet
{
write-host "AdomdLoadCellSet"
# Open XmlReader from the XML file
$reader = new-object System.XML.XmlTextReader("cellSetXml.xml")
$reader
# Load XML into CellSet object
$cellSet = [Microsoft.AnalysisServices.AdomdClient.CellSet]::LoadXml($reader)
$cellset
# Close XmlReader
$reader.Close()
}
################################################################
function AdomdExecuteCommandNonQuery
{
write-host "AdomdExecuteCommandNonQuery"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create the command that will send a request for a creation of a calculated member
[String] $cmdText = "CREATE member [Warehouse and Sales].Measures.MyCalcMember as '1' "
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)
# Execute the command, without expecting a returned result
$command.ExecuteNonQuery()
# Close the connection
$con.Close()
}
################################################################
function AdomdExecuteCommand
{
write-host "AdomdExecuteCommand"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a command that sends a simple MDX request
[String] $cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)
# Execute a command and return an object of any type.
[Object] $result = $command.Execute()
# Analyze the type of object returned
if ($null -eq $result)
{
# No result
write-host "No result"
}
elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.CellSet])
{
# Work with CellSet
write-host "Work with CellSet"
[Microsoft.AnalysisServices.AdomdClient.CellSet] $cellset = $result
}
elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.AdomdDataReader])
{
# Work with DataReader
write-host "Work with DataReader"
[Microsoft.AnalysisServices.AdomdClient.AdomdDataReader] $dataReader = $result
}
# Close the connection
$con.Close()
}
################################################################
# Main
AdomdExecuteCommandText
#AdomdExecuteCommandStream
AdomdExecuteCommandCellSet
AdomdExecuteCommandDataReader
AdomdExecuteCommandXmlReader
AdomdLoadCellSet
AdomdExecuteCommandNonQuery
AdomdExecuteCommand
This long script introduces PowerShell functions. Also, you can see an example of a try and catch statements. I like this code because the authors are cleanly opening and closing connections in each function, a habit of a disciplined developer. The concept is called encapsulation, in this case encapsulating connections.
ADOMDIterateActions:
# 3310_AdomdIterateActions.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;Safety Options=1"
$con.Open()
#Create a collection of restrictions for GetSchemaDataSet
$restrictions = new-object Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection
$restrictions.Add("CATALOG_NAME", "Foodmart 2008")
$restrictions.Add("CUBE_NAME", "Warehouse And Sales")
$restrictions.Add("COORDINATE", "[Store].[Store City].[Los Angeles]")
[int] $coordinateType = 4 # MDACTION_COORDINATE_MEMBER
$restrictions.Add("COORDINATE_TYPE", $coordinateType<#.ToString()#>)
$restrictions.Add("INVOCATION", 0);
# Call the method GetSchemaDataSet
$ds = $con.GetSchemaDataSet("MDSCHEMA_ACTIONS", $restrictions)
# Iterate all the rows in the first table of the Dataset
# Print the contents of each column.
foreach ($row in $ds.Tables[0].Rows)
{
foreach ($column in $ds.Tables[0].Columns)
{
write-host $column.Caption "=" $row[$column]
}
}
# Close the connection
$con.Close()
This script shows some changes made to the server. This code has some string passing, and experienced developers have concerns about passing strings and having the correct syntax. That topic multiples when we pass connection strings, DMX, MDX, T-SQL and all sorts of other scripts. You should check strings before passing them, and production code of course would have some syntax checking if that approach is important to your application. The ISE is nice (compared with the interactive session) because it provides debugging, and also color codes the script while you are typing. The color coding acts as a form of Intellisense (but not to the degree of Visual Studio). There is an auto-complete feature using the TAB key (of course, my favorite key on the keyboard, for which my family sadly is not receiving royalties) when you use the interactive session. Here is a screen view of the same code with the colors. Can you tell what the colors green, red, blue, and brown represent?
ADOMDNestedRowsetSample:
# 3311_AdomdNestedRowset.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
# The helper function that detects whether the column is a child of the Relation
function IsChildRelationColumn
{
param(
[System.Data.DataRelation] $dataRelation,
[System.Data.DataColumn] $dataColumn
)
foreach ($relationColumn in $dataRelation.ChildColumns)
{
if ($dataColumn -eq $relationColumn)
{
return $true
}
}
return $false
}
################################################################
# The helper function that detects whether a column is a child of a relation
function IsParentRelationColumn
{
param(
[System.Data.DataRelation] $dataRelation,
[System.Data.DataColumn] $dataColumn
)
foreach ($relationColumn in $dataRelation.ParentColumns)
{
if ($dataColumn -eq $relationColumn)
{
return $true
}
}
return $false
}
################################################################
function WriteDiscoverSchemaRowsets {
write-host "WriteDiscoverSchemaRowsets"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a restriction collection that will limit the result to information about the Actions schema rowset
$restrictions = new-object Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection
$restrictions.Add("SchemaName", "MDSCHEMA_ACTIONS")
# Call the method GetSchemaDataSet
$ds = $con.GetSchemaDataSet("DISCOVER_SCHEMA_ROWSETS", $restrictions);
# Get the first table. ADOMD.NET will always return it even if the result is empty.
$dt = $ds.Tables[0]
# To improve performance, we will create an array in which we will store DataRelation for each column that contains DataRelation and null if the column contains data values
$drMap = new-object Object[] $dt.Columns.Count
[int] $iColumn = 0
# Iterate all the columns and relationships in the DataTable
# This is certainly not very convenient. It would be better if the column stored additional information whether it contains reference or data. But unfortunately that’s not the way it works.
foreach($column in $dt.Columns)
{
foreach ($dataRelation in $ds.Relations)
{
if (IsParentRelationColumn $dataRelation $column)
{
$drMap[$iColumn] = $dataRelation
break
}
}
$iColumn ++
}
# Iterate the rows of the first datatable and print their values
foreach ($row in $dt.Rows)
{
$iColumn = 0
foreach($column in $dt.Columns)
{
if ($drMap[$iColumn] -ne $null)
{
write-host $column.ColumnName
# Navigate to the nested table through relationship
foreach($childRow in $row.GetChildRows($drMap[$iColumn]))
{
foreach($childColumn in $childRow.Table.Columns)
{
# If the column is a data column rather than a relationship column, print data value
[bool] $icrc = IsChildRelationColumn $drMap[$iColumn] $childColumn
if (! $icrc)
{
$field = $childRow[$childColumn]
write-host " " $childColumn.Caption "=" $field.ToString()
}
}
}
}
else
{
$field = $row[$column]
write-host $column.Caption "=" $field.ToString()
}
$iColumn++
}
}
# Close the connection
$con.Close()
}
################################################################
# Main
WriteDiscoverSchemaRowsets
This code introduces the PowerShell array, and shows how to initialize an empty array. I will mention the pound symbol line since I often use this type of graphical divider to separate sections of code for easier reading. Sometimes I use several rows when there are thousands of lines (though I do not like programs that long and will attempt to encapsulate portions when possible). I had to introduce the $ICRC variable since PowerShell, explicitly cast as [bool], since PowerShell would not accomplish that code in one line. Finally, I recommend that developers only use the PARAM method for passing parameters, since it is an explicit way to make sure you get what you want. There are tricks to making these passed parameters required, though I did not add any. Some of the tricks can include requiring user input if the variable is not declared, and also specifying a default value.
ADOMDExecuteSample:
# 3312_AdomdExecute.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
function AdomdExecuteCommandText
{
write-host "AdomdExecuteCommandText"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create the object AdomdCommand
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
$command.Connection = $con;
# Define the text the request
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
# Execute command...
# …
# Close the connection
$con.Close
}
################################################################
function AdomdExecuteCommandStream
{
# This function assumes that you have a file called "Foodmart2008Metadata.xml" and that the "Food Mart 2008" Analysis Services database does NOT exist
write-host "AdomdExecuteCommandStream"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost;"
$con.Open()
# Open a stream for reading a file that contains the XML for creating a cube
$sr = new-object System.IO.StreamReader("..\\..\\Foodmart2008Metadata.xml", $true)
$command = $con.CreateCommand()
$command.CommandStream = $sr.BaseStream
# Execute the command
$command.ExecuteNonQuery()
# Close the stream
$sr.Close()
}
################################################################
function AdomdExecuteCommandCellSet
{
write-host "AdomdExecuteCommandCellSet"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a simple MDX request
$cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText,$con)
# Execute a method, that returns CellSet
$cellset = $command.ExecuteCellSet()
$cellset
# Close the connection
$con.Close()
}
################################################################
function AdomdExecuteCommandDataReader
{
write-host "AdomdExecuteCommandDataReader"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a simple MDX statement
$command = $con.CreateCommand()
[string] $query = "SELECT Measures.Members ON columns FROM [Warehouse and Sales]"
$command.CommandText = $query
# Execute the method that returns IDataReader
$reader = $command.ExecuteReader()
# Read the metadata about the columns in the returned result
$schemaTable = $reader.GetSchemaTable()
write-host "Schema"
for([int] $iSchRow = 0; $iSchRow -lt $schemaTable.Rows.Count; $iSchRow ++)
{
for([int] $iSchCol = 0; $iSchCol -lt $schemaTable.Columns.Count; $iSchCol ++)
{
write-host $schemaTable.Rows[$iSchRow][$iSchCol].ToString() " "
}
}
# Read the result
while($reader.Read())
{
for([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol ++)
{
write-host $reader.GetValue($iCol).ToString()
write-host $reader.GetDataTypeName($iCol)
}
}
# Close DataReader
$reader.Close();
# Close the connection
$con.Close();
}
################################################################
function AdomdExecuteCommandXmlReader
{
write-host "AdomdExecuteCommandXmlReader"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a command
$command = $con.CreateCommand()
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
# Execute the command and get XMLReader
$reader = $command.ExecuteXmlReader()
# Write the XML into a file -- use the results of GetCurrentDirectory to see where the file goes
[IO.Directory]::GetCurrentDirectory()
[string] $xml = $reader.ReadOuterXml()
$writer = new-object System.IO.StreamWriter("cellSetXml.xml", $false)
$writer.Write($xml)
# Close the file
$writer.Close()
# Close XmlReader
$reader.Close()
# Close the connection
$con.Close()
}
################################################################
function AdomdLoadCellSet
{
write-host "AdomdLoadCellSet"
# Open XmlReader from the XML file
$reader = new-object System.XML.XmlTextReader("cellSetXml.xml")
$reader
# Load XML into CellSet object
$cellSet = [Microsoft.AnalysisServices.AdomdClient.CellSet]::LoadXml($reader)
$cellset
# Close XmlReader
$reader.Close()
}
################################################################
function AdomdExecuteCommandNonQuery
{
write-host "AdomdExecuteCommandNonQuery"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create the command that will send a request for a creation of a calculated member
[String] $cmdText = "CREATE member [Warehouse and Sales].Measures.MyCalcMember as '1' "
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)
# Execute the command, without expecting a returned result
$command.ExecuteNonQuery()
# Close the connection
$con.Close()
}
################################################################
function AdomdExecuteCommand
{
write-host "AdomdExecuteCommand"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Create a command that sends a simple MDX request
[String] $cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)
# Execute a command and return an object of any type.
[Object] $result = $command.Execute()
# Analyze the type of object returned
if ($null -eq $result)
{
# No result
write-host "No result"
}
elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.CellSet])
{
# Work with CellSet
write-host "Work with CellSet"
[Microsoft.AnalysisServices.AdomdClient.CellSet] $cellset = $result
}
elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.AdomdDataReader])
{
# Work with DataReader
write-host "Work with DataReader"
[Microsoft.AnalysisServices.AdomdClient.AdomdDataReader] $dataReader = $result
}
# Close the connection
$con.Close()
}
################################################################
# Main
AdomdExecuteCommandText
#AdomdExecuteCommandStream
AdomdExecuteCommandCellSet
AdomdExecuteCommandDataReader
AdomdExecuteCommandXmlReader
AdomdLoadCellSet
AdomdExecuteCommandNonQuery
AdomdExecuteCommand
I commented out one of the functions since it requires that you have the downloadable XML file, essentially defining the Food Mart database. I already have the Food Mart database defined, so that XML file would not have an effect on my system. If you want to try this commented out function, then you will need to download the XML file from the authors’ website. Next, I added a GetCurrentDirectory command to one of the functions since you would not necessarily know where the produced XML file will go. The directory location shows in the output window just before making that XML file. Finally, this code includes the -IS comparison operator, which allows PowerShell to check if an object IS of a certain type. Note that I also explictly cast the variables following this comparison checking since these new variables would automatically take the type of $result (and we want to make sure, for example, that $cellset is a CELLSET).
AdomdIterateOlapInfo:
# 3319_AdomdIterateOlapInfo.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
$con.Open()
# Send a simple MDX statement
$command = $con.CreateCommand()
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
$cellset = $command.ExecuteCellSet()
# Iterate the data on the cubes used in the request
# In the current version Microsoft SQL Server Analysis Services, only one cube can be used in the request
foreach ($cubeInfo in $cellset.OlapInfo.CubeInfo.Cubes)
{
write-host "CubeName = " $cubeInfo.CubeName
write-host "Cube was last updated on " $cubeInfo.LastDataUpdate
}
# Iterate the metadata for the axes
foreach ($axisInfo in $cellset.OlapInfo.AxesInfo.Axes)
{
# For each axis, print the name of the axis
write-host "AxisName = " $axisInfo.Name
# Iterate the hierarchies on the current axis.
foreach ($hierarchyInfo in $axisInfo.Hierarchies)
{
write-host "HierarchyName = " $hierarchyInfo.Name
foreach ($hierarchyPropertyInfo in $hierarchyInfo.HierarchyProperties)
{
write-host "HierarchyProperty = " $hierarchyPropertyInfo.Name
}
}
}
# Iterate the cell properties that are used in the request
foreach ($cellPropertyInfo in $cellset.OlapInfo.CellInfo.CellProperties)
{
write-host "CellPropertyName = " $cellPropertyInfo.Name
}
# Close the connection
$con.Close()
This code iterates through the OLAP Cube providing information.
DisplayCellSet:
This solution requires the INView.OCX COM control, freely available at the authors’ website. The project requires adding that OCX control (COM) to the toolbox, which then makes it available to the solution. In my case, I received an error when compiling the solution, and I could not quickly discover what the issue is. (However, if you know, send me a solution and I will give you credit HERE).
ADOMDFetchingProperties:
# 3321_AdomdFetchingProperties.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName( "Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
function FetchingNotMDDatasetProperties1
{
write-host "FetchingNotMDDatasetProperties1"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=localhost; Initial Catalog=Foodmart 2008;")
$con.Open()
# Execute a simple MDX statement
$command = $con.CreateCommand()
$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
# Execute the command and get the CellSet object
$cellSet = $command.ExecuteCellSet()
# Iterate the axes and their hierarchies and get properties of each hierarchy
write-host "Axes Count: " $cellSet.Axes.Count
foreach ($axis in $cellSet.Axes)
{
write-host "Hierarchies Count: " $axis.Set.Hierarchies.Count
foreach ($hier in $axis.Set.Hierarchies)
{
write-host $hier.Caption
write-host $hier.Description
}
}
# Close the connection
$con.Close()
}
################################################################
function FetchingNotMDDatasetProperties2
{
write-host "FetchingNotMDDatasetProperties2"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=localhost; Initial Catalog=Foodmart 2008;")
$con.Open()
# Execute an MDX statement
$command = $con.CreateCommand()
$command.CommandText = "SELECT measures.members ON COLUMNS FROM [Warehouse and Sales]"
$cellSet = $command.ExecuteCellSet()
# Iterate the axes and tuples on each axis
write-host "Axes Count: " $cellSet.Axes.Count
foreach ($axis in $cellSet.Axes)
{
write-host "Tuples Count: " $axis.Set.Tuples.Count
foreach ($tuple in $axis.Set.Tuples)
{
$member = $tuple.Members[0]
try
{
# Try to get the member.Type property; this will cause an exception in C#
write-host $member.Type
}
catch [System.InvalidOperationException]
{
$ex = $_.Exception
write-host $ex.Message
# Send a request to the server to bring all the properties of the current member
$member.FetchAllProperties()
# Successfully print the property member.Type
write-host $member.Type
}
}
}
# Close the connection
$con.Close()
}
################################################################
# Main
FetchingNotMDDatasetProperties1
FetchingNotMDDatasetProperties2
This code illustrates how to fetch properties that are not part of a multidimensional result. The idea here is to save query time by using this approach when you need properties that need not be part of a multidimensional query.
ADOMDDataAdapterSample:
# 3324_AdomdDataAdapter.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
function AdomdUsingDataAdapterToFillDataset1
{
write-host "AdomdUsingDataAdapterToFillDataset1"
[string] $commandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
[string] $connectionString = "Datasource=localhost; Initial Catalog=Foodmart 2008;"
$dataAdapter = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($commandText, $connectionString)
$ds = new-object System.Data.DataSet
$dataAdapter.Fill($ds)
}
################################################################
function AdomdUsingDataAdapterToFillDataset2
{
write-host "AdomdUsingDataAdapterToFillDataset2"
[string] $commandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
[string] $connectionString = "Datasource=localhost; Initial Catalog=Foodmart 2008;"
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection($connectionString)
$con.Open()
$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($commandText, $con)
$dataAdapter = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($command)
$ds = new-object System.Data.DataSet
$dataAdapter.Fill($ds)
$dataAdapter
$con.Close()
}
################################################################
# Main
AdomdUsingDataAdapterToFillDataset1
AdomdUsingDataAdapterToFillDataset2
This code populates the DataAdapter with results from Analysis Services. In this code I added the $DataAdapter line which illustrates how PowerShell will attempt to convert objects to text and display to the output window. A comment from the authors:
The current version of ADOMD.NET supports the DataAdapter and DataSet objects only for reading data from the server, not for writing. If you need your application to change data on the server (to write back and perform what-if analysis), use the MDX command UPDATE. (Page 649)
ADOMDDataReaderSample:
# 3326_AdomdDataReader.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
# Open the connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$con.ConnectionString = "Datasource = localhost;Initial Catalog=Foodmart 2008;"
$con.Open()
# Create and execute an MDX query
$command = $con.CreateCommand()
$command.CommandText = "SELECT {[Measures].[Store Cost], [Measures].[Store Sales]} ON COLUMNS FROM [Warehouse and Sales]"
$reader = $command.ExecuteReader()
# Read the metadata about the columns
$schemaTable = $reader.GetSchemaTable()
write-host "Schema Information about resultset"
foreach ($schRow in $schemaTable.Rows)
{
foreach ($schColumn in $schemaTable.Columns)
{
write-host $schColumn.ColumnName ":"
write-host $schRow[$schColumn].ToString()
}
}
# Another way to access that metadata is to call the method AdomdDataReader.GetName(), but if the result contains no rows, this approach won’t work.
write-host "Name of the columns"
for ([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol++)
{
write-host $reader.GetName($iCol)
}
# Iterate the values
write-host "Values in the resultset"
while ($reader.Read())
{
for ([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol++)
{
write-host "Value: " $reader.GetValue($iCol)
write-host "Type: " $reader.GetDataTypeName($iCol)
}
}
# Close the DataReader
$reader.Close()
# Close the connection
$con.Close()
DataReader is a popular object for developers who want to display (in a read-only situation) data without having the query finish processing. Though, the code executes too quickly even on my laptop to visually detect some type of difference. I repeat an important caution noted by the authors:
There is one very important restriction to remember while using AdomdDataReader: It keeps the connection with the server open the whole time it is reading the data. This means that other commands cannot use the same connection to execute their requests while AdomdDataReader is open. (page 654)
DataGridSample:
This solution populates a DataGrid with Analysis Services data. I will mention here that on my sample projects I like to put both “Cancel” and “Exit” buttons on the interface. In this case, users are supposed to click the “X” box in the upper-right hand corner to quit the application.
ADOMDParametricQuery:
This solution provides an interface with parameters for an MDX query. One might use the same approach for a DMX application. However, when I ran this application, I received an error, due to the TopCount parameter. I tried several fixes, but could not figure out what is wrong with this code. Email me if you have a solution and I will give you credit.
ADOMDQueryCancel:
This solution has a sequence of steps, illustrated by the three buttons. In sequence, they become clickable once each of the three phases completes. I would make the video, but it’s not that exciting:
This solution also gave me problems. However, this time I did fix it. I changed the solution to run against .NET Framework 4.0 and changed the code highlighted below:
ADOMDErrorHandling:
# 3330_AdomdErrorHandling.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]
################################################################
function AdomdCellErrors
{
write-host "AdomdCellErrors"
# Open a connection to the server
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=localhost; Initial Catalog=foodmart 2008;")
$con.Open()
# Create a command that contains a calculated member with an error.
# The Method Max accepts only numerical values as parameters, and we try to pass strings.
$command = $con.CreateCommand()
$command.CommandText = "WITH member measures.x AS Order('string',4) select {x} ON COLUMNS FROM [Warehouse and Sales]"
$cellset = $command.ExecuteCellSet()
# Iterate all the cells and print their values
foreach ($cell in $cellset.Cells)
{
try
{
[int] $test = $cell.value
write-host $cell.Value
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]"
# If there was an error, iterate the error collection and print the error message
write-host " Error in the cell "
foreach ($cellError in $_.Exception.Errors)
{
write-host $cellError.Message
}
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdException]"
write-host $_.Exception.Message
}
}
# Close the connection
$con.Close();
}
################################################################
function AdomdErrorHandling
{
write-host "AdomdErrorHandling"
$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Datasource=Fake_host; Initial Catalog=foodmart 2000;")
try
{
# Open a connection to the server
$con.Open()
# do some operations
# ...
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException]"
write-host $_.Exception.Message
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException]"
# If there was an error, iterate the error collection and print the error message
foreach ($cellError in $_.Exception.Errors)
{
write-host $cellError.Message
}
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException]"
write-host $_.Exception.Message
}
catch [Microsoft.AnalysisServices.AdomdClient.AdomdCacheExpiredException]
{
write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdCacheExpiredException]"
write-host $_.Exception.Message
# refresh the controls
# ...
}
finally
{
write-host 'finally'
# Close the connection
if ($con.State -eq [System.Data.ConnectionState]"Open")
{
$con.Close()
}
}
}
################################################################
# Main
AdomdCellErrors
AdomdErrorHandling
This last example illustrates error handling. Using the catch statement, a PowerShell script can catch errors. When this code runs in C#, the first function throws an error, while the second one was intended as a template for how to code for different exceptions. In my PowerShell translation, the first code did not throw an exception because due to the text-based nature of PowerShell, the script compiler is more forgiving when passing unexpected objects or results. I believe that developers will have to adjust to this type of response when strongly-typed situations might not be required (as with PowerShell). As a developer, you can always check whether an object -IS of a type you expect, and always can check for values (even cross-validated against live data, which I believe is appropriate in many cases). I did change the second connection string so that it would throw an exception, and reading the script, can you just guess which trap scriptblock the code path would follow? You can watch the code execute if you use the PowerShell ISE, put in a breakpoint, and step into each line. The book code had an error in the C# code, which was correct in the first writing, but not in the second use:
The chapter ends with this last example, and actually it ends with the code from the example. I conclude with some major observations applying the content of this chapter to SQL Server Data Mining:
- ADOMD.NET is the way to retrieve results from an Analysis Services database. It can be used to execute MDX and DMX queries.
- PowerShell has completely replaced command prompt for developers. Using the object-oriented interactive interface, many administrative tasks (given the correct authorization and authentication) can be done in one line (even for Analysis Services too). Using the PowerShell ISE, developers can write and debug PowerShell scripts. These scripts can replace most if not all console applications (previously developed with .NET).
- PowerShell scripting follows a syntax similar to other .NET languages. PowerShell implicitly infers types in many cases, and allows explicit type casting to happen. PowerShell output can be piped to files, even in HTML format. In my reference list, I provide three references to books on PowerShell.
References
Deshev, H. (2008). Pro Windows PowerShell. Berkeley, CA: Apress.
Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
Holmes, L. (2008). Windows PowerShell Cookbook. Sebastopol, CA: O’Reilly Media Inc.
Payette, B. (2007). Windows PowerShell in Action. Greenwich, CT: Manning Publications Co.
If you like this post, you may also like:
- Loading Data from a Relational Database [Translate] Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 17 This chapter assumes what I believe is still true...
- Extending MDX with Stored Procedures [Translate] Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 14 This chapter shows how to extend the native commands...
- Conceptual Data Model [Translate] Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 4 I have been happy that Microsoft has chosen to...
- Physical Data Model [Translate] Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 20 This chapter discusses physical models. I consider this topic advanced,...
- Server Architecture and Command Execution [Translate] Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 26 This chapter starts with a comprehensive diagram with the comment,...
