Tag Archives: AMO

PowerShell with SQL Server Data Mining

My paper on “PowerShell and SQL Server Data Mining” has just been published in Solid Quality Journal April 2011 edition.  I cofounded this journal, and many hands work to produce monthly issues and contribute to the articles. I have been impressed by the graphics layout team, and they make my work look great.

This PowerShell topic has been popular on this blog, since PowerShell is still relatively new technology.  I believe PowerShell is an important part of complete enterprise-level management. I will provide my abstract, and then link to the article.

Continue reading “PowerShell with SQL Server Data Mining” »

Programming SQL Server Data Mining with PowerShell 2.0

51oz6dERfFL. SL160  Programming SQL Server Data Mining with PowerShell 2.0Data Mining with Microsoft SQL Server 2008 Review Chapter 16

Programmers: Who are you?

I first had “programming” work in the 1980s. This chapter mentions programming in the title, and in looking for MSDN web references, I find the term developer more common. I decided to see what the difference is between programmer and developer. Wikipedia was no help, only to say that programming has been met with more comparative derision, while developer implies engineering (I deliberately provide no reference since Wikipedia is an ever-changing reference source). The Wikipedia definition does not include a strong statement on science, and that omission stands out to me.

I personally believe that programmer and developer are equivalent terms, but perhaps my perception reveals my age and experience with specific respected people who use these terms to refer to themselves. More important than either of these terms to me are the words engineering and science. In the MarkTab lexicon, engineers build things, and scientists apply the scientific method. I do not believe these terms are essentially independent — they might be, but not necessarily.

On this blog, I believe all data model builders are engineers, and may be scientists too. Someone applying a model (an analyst performing a prediction query) need not build a model, and applying models to data could be engineering but also could be science too. Analysts who apply models might be called a data mining scientist or data mining engineer. Some people might not feel like they are a scientist, but when we apply the scientific method, we are practicing science. In the history of science, you might be surprised at what simple tasks are considered science.

Some might believe data mining scentist should apply to someone who creates algorithms. I disagree with that application, and I have other terms for that person: Continue reading “Programming SQL Server Data Mining with PowerShell 2.0” »

Analysis Management Objects (AMO) and Powershell 2.0

51s1nMjautL. SL160  Analysis Management Objects (AMO) and Powershell 2.0Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 34

This chapter discusses the AMO managed assembly, which describes a hierarchical model of major objects and minor (dependent) objects for administering Analysis Services. The diagram located at http://msdn.microsoft.com/en-us/library/ms345083.aspx is more comprehensive than the one the book has on page 671. The Microsoft Developer Network (msdn.microsoft.com) website has full developer resources, and for this topic see http://msdn.microsoft.com/en-us/library/ms124924.aspx.

The authors provided extensive C# code for this chapter. As with Chapter 33, I decided to translate the console applications into PowerShell 2.0. This blog post will go through the solutions provided by the author, and show the PowerShell code (I provided a PowerShell introduction in the Chapter 33 review, and therefore I skip that preliminary and necessary step for this blog post). Continue reading “Analysis Management Objects (AMO) and Powershell 2.0” »

Client/Server Architecture and Data Access

510cHjGCsUL. SL160  Client/Server Architecture and Data AccessMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 31

This chapter begins a new four-chapter section on accessing data in Analysis Services. This chapter has four basic messages, which I will summarize since this first chapter is a summary chapter.

Message one: XML/A is at the top of the Analysis Services protocol stack (page 570), and is the subject of chapter 32:

  • XML for Analysis (XML/A) = application layer
  • SOAP (Simple Object Access Protocol) = presentation protocol layer
  • TCP (Transmission Control Protocol) = transport protocol layer
  • IPv4 or IPv6 (IP = Internet Protocol) = network protocol layer

Message two: HTTP provides alternative access to Analysis Services through IIS

  • The protocol is well-known
  • It provides an alternative to NT integrated security
  • It allows full decoupling of the client and the server

Message three: Users have offline access to data

  • The key to offline data is Microsoft Office (in general) and Excel (in particular)
  • This option has been extended with the introduction (since this book’s publication) of PowerPivot
  • The options will increase as this technology becomes available through SharePoint

Message four: Four libraries ship with Analysis Services:

  • Query Management, Native Code: OLE DB for OLAP/ADOMD/ADO
  • Query Management, Managed Code: ADOMD.NET
  • Administration, Native Code: DSO
  • Administration, Managed Code: AMO

The two managed code options are the subjects of chapters 33 and 34. The native code options remain as a legacy support for the many applications written in those languages. Pragmatically though, .NET is about 10 years old, and many people have moved on to the managed code alternatives for the advantages given to developers during development, and for other advantages during code maintenance and auditing. I personally would recommend people to take the managed code route unless an extremely sound case for native code could be made (a strong case would include both a preexisting native code base along with developers already experienced in native code development, who would not only develop new code but would be there to maintain it).

Query management differs from administration in the goals. Query management aims to provide libraries to acquire data from Analysis Services. Administration libraries allow developers to create, modify, and delete objects within an analysis services database. Separating the libraries allows for better role management when allowing users to access Analysis Services. Focusing on data mining: the typical data miner creates and modifies data mining structures and models, which is an administrative activity (since it alters the analysis services database). Data mining modelers are, in my opinion, doing similar if not identical work to business intelligence architects who build and maintain OLAP cubes. I like class structure pictures, and you can see this administration story on the MSDN documentation, Introducing AMO Classes.

A good general reference is the MSDN topic, Analysis Services Data Access Interfaces (Analysis Services – Multidimensional Data).

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6