Category: PASS

Presentation for the SQL PASS PowerShell Virtual User Group

Today (April 6, 2016) I was pleased to present “High Class PowerShell” to the SQL PASS PowerShell Virtual User Group. In it I covered the creation of custom objects and classes in PowerShell.

The first question everyone asks is “where’s the code”. I’ve pasted the code samples for the main demo at the bottom of this post. You can review it here, or copy – paste into your own ISE.

The closing code on calling classes created in PowerShell was discussed in these two blog posts, so I won’t repeat it here.

During the presentation, I mentioned this code is a small subset of my larger Pluralsight course, Beginning PowerShell Scripting for Developers. You’ll find it and my other courses here:

https://www.pluralsight.com/authors/robert-cain

Don’t have a Pluralsight subscription? No problem, Pluralsight has graciously provided me some trial codes I can share with you. These will allow you a one month, no obligation trial with which you can watch any of my courses, or indeed any course found on Pluralsight. Just email free@arcanetc.com to request your code. (If you didn’t get to see the presentation, no worries, you can still email us for the Pluralsight trial code!)

When the video of the presentation goes live I will have a follow up blog post, meanwhile, and without further ado, here is the code sample. 

<#-----------------------------------------------------------------------------
  High Class PowerShell

  Author: Robert C. Cain | @ArcaneCode | arcanecode@gmail.com
          http://arcanecode.com
 
  This module is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  The code herein is for demonstration purposes. No warranty or guarentee
  is implied or expressly granted. 
  This module may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

#-----------------------------------------------------------------------------#
# Demo 0 -- Object Oriented Terminology
#-----------------------------------------------------------------------------#
<#
                                  class = blueprints
                             properties = describe
                    methods (functions) = actions
                                 object = house
             instantiating a new object = building a new house
each object is an instance of the class = each house is a copy of the blueprint


#>

#-----------------------------------------------------------------------------#
# Demo 1 -- Create a new object 
# This is the most common method of creating objects
#-----------------------------------------------------------------------------#

# The most basic way to create a custom object is to build a hash table with 
# the properties. Using [ordered] will ensure the properties remain in the
# desired order. 
$properties = [ordered]@{ Schema = $Schema
                          Table = $Table
                          Comment = $Comment
                        }

# Start by creating an object of type PSObject
$object = New-Object –TypeName PSObject -Property $properties

# Now you can access it's properties
$object.Schema = 'MySchema'
$object.Table = 'MyTable'
$object.Comment = 'MyComment'
$object


# Much of the time, the process of creating a custom object is wrapped in a 
# function to make it easier. 
function Create-Object ($Schema, $Table, $Comment)
{
  # Build a hash table with the properties
  $properties = [ordered]@{ Schema = $Schema
                            Table = $Table
                            Comment = $Comment
                          }

  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject -Property $properties

  # Return the newly created object
  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# Display in text. If you try to reference a property in text, it doesn't
# work quite right.
"My Schema = $myObject.Schema"

# Instead, wrap it in $() to access a property. This will force PowerShell to
# evaluate the object.property call as an expression, and return that to the
# string
"My Schema = $($myObject.Schema)"

# Changing properties is easy
$myObject.Schema = "New Schema"
$myObject.Comment = "New Comment"
$myObject


#-----------------------------------------------------------------------------#
# Demo 2 -- Create a new object by adding properties one at a time
# In the previous demo a property hash table was used to generate the object
# Behind the scenes it does the equivalent of what this function does
#-----------------------------------------------------------------------------#
function Create-Object ($Schema, $Table, $Comment)
{
  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject

  # Add-Member by passing in input object
  Add-Member -InputObject $object `
             –MemberType NoteProperty `
             –Name Schema `
             –Value $Schema

  # Alternate syntax, pipe the object as an input to Add-Member
  $object | Add-Member –MemberType NoteProperty `
                       –Name Table `
                       –Value $Table
  
  $object | Add-Member -MemberType NoteProperty `
                       -Name Comment `
                       -Value $Comment

  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# No difference in the way we display values or assign them
"My Schema = $($myObject.Schema)"

$myObject.Schema = "New Schema"
$myObject

# Show this is a PSCustomObject
$myObject.GetType()

# Each property has it's own .NET Data Type. 
# Note this is different from the Member Type
$myObject.Comment.GetType()

# To see the Member Types, pipe to Get-Member
$myObject | Get-Member 


#-----------------------------------------------------------------------------#
# Demo 3 -- Add alias for one of the properties
#-----------------------------------------------------------------------------#
Clear-Host

# Demo 3 -- Add alias so both Comment and Description reference the same thing
Add-Member -InputObject $myObject `
           -MemberType AliasProperty `
           -Name 'Description' `
           -Value 'Comment' `
           -PassThru  # Use passthru to see the new object

"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Change the value
$myObject.Description = 'This is now a description'
"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Demo 3 -- Add script block to object
Clear-Host
$block = { 
           $fqn = $this.Schema + '.' + $this.Table 
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'FullyQualifiedName' `
           -Value $block `
           -PassThru

# Using Get-Member we can see the newly added script method
$myObject | Get-Member 

# When calling our new function, parens are very important, without them 
# PowerShell will just display the function
$myObject.FullyQualifiedName()  


#-----------------------------------------------------------------------------#
# Demo 4 -- Script block with parameters
#-----------------------------------------------------------------------------#
Clear-Host
$block = { 
           param ($DatabaseName)
           $fqn = "$DatabaseName.$($this.Schema).$($this.Table)"
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'DatabaseQualifiedName' `
           -Value $block 

# Place any parameters within the parens
$myObject.DatabaseQualifiedName('MyDBName')  


#-----------------------------------------------------------------------------#
# Demo 5 -- Script Property
#-----------------------------------------------------------------------------#
# These are analogues to properties in C#, with a Getter and Setter function
Clear-Host

# Add a property we can work with
Add-Member -InputObject $myObject `
           –MemberType NoteProperty `
           –Name AuthorName `
           –Value 'No Author Name'

$myObject # Show the added property

# This defines the GET for this property
$getBlock = { return $this.AuthorName }

# This defines the SET. Adding a simple check for the name
$setBlock = { 
              param ( [string]$author )
                            
              if($author.Length -eq 0)
              { $author = 'Robert C. Cain, MVP' }
              
              $this.AuthorName = $author
            }

# Now add the custom Get/Set ScriptProperty to the member
Add-Member -InputObject $myObject `
           -MemberType ScriptProperty `
           -Name Author `
           -Value $getBlock `
           -SecondValue $setBlock

# Demo its use when passing as value
$myObject.Author = 'ArcaneCode'
"`$myObject.Author now equals $($myObject.Author )"

# Now pass in nothing to see the setter functionality kicking in
$myObject.Author = ''
$myObject.Author

# Unfortunately the original property is still available, and thus
# the custom get/set can be bypassed
$myObject.AuthorName = ''
$myObject.Author                       # Author reflects value of AuthorName

$myObject.Author = ''                  # Going thru scriptproperty sets correctly
$myObject.Author


#-----------------------------------------------------------------------------#
# Demo 6 -- Set default properties
# Note: Thanks to Poshoholic for his cool code sample, see it at:
# http://poshoholic.com/2008/07/05/essential-powershell-define-default-properties-for-custom-objects/
#-----------------------------------------------------------------------------#
Clear-Host

# When just running the object, it displays all properties
$myObject

# If you have a lot, this can get overwhelming. Instead you can define a
# default set to display.

# Define the property names in an array
$defaultProperties = 'Schema', 'Table', 'Comment', 'Author'

# Create a property set object and pass in the array 
$defaultPropertiesSet `
  = New-Object System.Management.Automation.PSPropertySet(`
      ‘DefaultDisplayPropertySet’ `
      ,[string[]]$defaultProperties `
      )

# Create a PS Member Info object from the previous property set object
$members `
  = [System.Management.Automation.PSMemberInfo[]]@($defaultPropertiesSet)

# Now add to the object
$myObject | Add-Member MemberSet PSStandardMembers $members

# Now the object will just display the default list in standard output
$myObject

# Little easier to read in a list
$myObject | Format-List

# To display all properties, pipe through format-list with wild card for property
$myObject | Format-List -Property *


#-----------------------------------------------------------------------------#
# Demo 7 - Create a class from .Net Code and call a static method
#-----------------------------------------------------------------------------#

# Load the contents of the file into a variable
$code = @"
using System;

public class MyObjectStatic
{   public static string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public static void composeFullName
} // class MyObjectStatic

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectStatic]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 8 - Instantiate an object from .Net Code in embedded code
#-----------------------------------------------------------------------------#

$code = @"
using System;

public class MyObjectEmbedded
{   public string SomeStringName;   public string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public string composeFullName
} // class MyObjectEmbedded

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Instantiate a new version of the object
$result = New-Object -TypeName MyObjectEmbedded

# Set and display the property
$result.SomeStringName = "Temp"
$result.SomeStringName

# Call the method
$result.composeFullName($mySchema, $myTable)


#-----------------------------------------------------------------------------#
# Demo 9 - Create object from .Net Code in an external file
#-----------------------------------------------------------------------------#

# Path and File Name
$file = 'C:\PS\Classes and Modules\HighClassPowerShell.cs'

# Open the file in the ise to look at it
psedit $file

# Load the contents of the file into a variable
$code = Get-Content $file | Out-String

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectExternal]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 10 - Add to an existing object
#-----------------------------------------------------------------------------#

# Going to add a script method and note property to the System.IO.FileInfo 
# objects returned by Get-ChildItem

# Define the custom script method
$script = { 
            $retValue = 'Unknown'

            switch ($this.Extension)
            {
              '.ps1'  { $retValue = 'Script' }
              '.psm1' { $retValue = 'Module' }
              '.psd1' { $retValue = 'Module Declration' }
              '.cs'   { $retValue = 'C# File'}
              default { $retValue = 'No Clue. Seriously stumped here.'}
            }

            return $retValue
          }

# Load a variable with a collection of file objects
Set-Location 'C:\PS\Classes and Modules'
$items = Get-ChildItem

$itemCount = 0
foreach($item in $items)
{
  # Add a note property, setting it to the current item counter
  $itemCount++
  $item | Add-Member –MemberType NoteProperty `
                     –Name ItemNumber `
                     –Value $itemCount

  # Add script property to the individual file object
  Add-Member -InputObject $item `
             -MemberType ScriptMethod `
             -Name 'ScriptType' `
             -Value $script 

  "$($item.ItemNumber): $($item.Name) = $($item.ScriptType())"
}

# Show our new note and script having been added to the FileInfo type
$items[0] | Get-Member


#-----------------------------------------------------------------------------#
# Demo 11 - Serializing an Object
#-----------------------------------------------------------------------------#

  # Create a simple object
  $mySiteProperties = [ordered]@{ 
                                  WebSite = 'ArcaneCode'
                                  URL = 'http://arcanecode.com'
                                  Twitter = '@ArcaneCode'
                                }

  # Convert it to an object
  $mySite = New-Object –TypeName PSObject -Property $mySiteProperties
  
  # Show the object
  $mySite
  
  # Save the object to a file and 
  $savedDataFile = 'C:\PS\Classes and Modules\mySite.xml'
  $mySite | Export-Clixml $savedDataFile
  psedit $savedDataFile 

  # Now grab the saved object and recreate in a different variable
  $newMySite = Import-Clixml $savedDataFile
  $newMySite


#region Enum

#-----------------------------------------------------------------------------#
# Show Enums in PS
#-----------------------------------------------------------------------------#

# Define the valid values for the enum
Enum MyTwitters
{
  ArcaneTC
  ArcaneCode
  N4IXT
}

# Note when typing the last : will trigger intellisense!
$tweet = [MyTwitters]::ArcaneCode 
$tweet

# See if they picked something valid
[enum]::IsDefined(([MyTwitters]), $tweet)

# Set it to something invalid and see if it passes as an enum
$tweet = 'Invalid'
[enum]::IsDefined(([MyTwitters]), $tweet)

#endregion Enum


#region Basic Class

#-----------------------------------------------------------------------------#
# Basic Class
#-----------------------------------------------------------------------------#
  
Class Twitterer
{
  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

}

$twit = [Twitterer]::new()
$twit.GetType()

$twit.TwitterHandle = 'ArcaneCode'
$twit.TwitterHandle
  
# See default property value
$twit.Name

# Override default value
$twit.Name = 'Robert Cain'
$twit.Name

$myTwitter = $twit.TwitterURL()
$myTwitter

$twit.OpenTwitter()

#endregion Basic Class


#region Advanced Class

#-----------------------------------------------------------------------------#
# Advanced Class
#   Constructors
#   Overloaded Methods
#-----------------------------------------------------------------------------#

Class TwittererRedux
{
  # Default Constructor
  TwittererRedux ()
  {
  }

  # Constructor passing in Twitter Handle
  TwittererRedux ([string]$TwitterHandle)
  {
    $this.TwitterHandle = $TwitterHandle
  }

  # Constructor passing in Twitter Handle and Name
  TwittererRedux ([string]$TwitterHandle, [string]$Name)
  {
    $this.TwitterHandle = $TwitterHandle
    $this.Name = $Name
  }

  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Static Properties
  static [string] $Version = "2016.04.06.001"

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Overloaded Function that returns a string
  [string] TwitterURL($twitterHandle)
  {
    $url = "http://twitter.com/$($twitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

  # Can launch a twitter page without instantiating the class
  static [void] OpenTwitterPage([string] $TwitterHandle)
  {
    $url = "http://twitter.com/$($TwitterHandle)"
    Start-Process $url
  }

}

# Create a class using default constructor
$twitDefault = [TwittererRedux]::new()

# Display without assigning
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Now assign and display again
$twitDefault.TwitterHandle = 'ArcaneTC'
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Show version one of TwitterURL
"URL = $($twitDefault.TwitterURL())"

# Show overloaded version
"URL = $($twitDefault.TwitterURL('ArcaneCode'))"

# Create a new instance using the second constructor
$twitAdvanced = [TwittererRedux]::new('N4IXT')

# Display without assigning - Should have what was passed in constructor
"TwitterHandle = $($twitAdvanced.TwitterHandle)"

# Create yet another instance using the third constructor
$twitAdvanced2 = [TwittererRedux]::new('ArcaneCode', 'R Cain')
$twitAdvanced2.TwitterHandle
$twitAdvanced2.Name

# Static Value - Can be called without initializing the class
[TwittererRedux]::Version

# Use the static method
[TwittererRedux]::OpenTwitterPage('ArcaneTC')


#endregion Advanced Class
Advertisement

Zero to Hero with PowerShell and SQL Server at 24 Hours of Pass

On September 9th I am co-presenting “Zero to Here with SQL Server and PowerShell” for the 24 Hours of PASS. If you’ve not heard of 24 Hours of PASS, it is 24 straight hours of online presentations. This time the sessions are a preview of the SQL PASS Summit in Seattle, WA in November.

At the PASS Summit I, along with two co-workers, am presenting a full day Pre-Con entitled Zero to Hero with PowerShell and SQL Server. I’m also doing a regular session, Make SQL Server POP with PowerShell.

The session for 24 Hours of PASS will take place at 00:00 GMT on September 10th, or for those of us in the states, September the 9th, 8 PM Eastern, 7 PM Central, 6 Mountain, or 5 Pacific. The session is titled the same as the precon, Zero to Hero with PowerShell and SQL Server. Through the preceding link you can see more about the session, get to the full schedule, and most importantly register!

Be sure to check out my co-presenters too, Bradley Ball (@SqlBalls | http://sqlballs.com ) and Jason Strate (@StrateSQL | http://www.jasonstrate.com )

PASS Summit 2011 Community Choice–I need your help!

Throwing out  call for help from all my friends in the tech community. My “Project Juneau” (next version of “Data Dude") session is up for a community choice slot at this falls SQL PASS summit. Five of the twenty sessions in the list will be picked for slots at this falls PASS Summit.

I would appreciate your help in getting selected! I know there are a lot of great names on the list, and so it’s a tough choice, but if you’ve seen my sessions on Data Dude (Visual Studio Database Projects) you’ll know what a powerful tool it is, and how the changes in the next version will make it even more so.

While you are there, you should also consider sending a vote for a few of my friends and co-workers. Mike Davis and Adam Jorgenson are doing a “SSIS vs T-SQL: Loading a Data Warehouse” session, and  Jorge Segarra (the infamous SQLChicken) is up for “Policy-Based Management in a Nutshell”.

You can vote at: http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx 

Deadline for votes is July 20th, so as the old saying goes “day don’t delay, vote today!”

If you are already a PASS website member voting only takes a few seconds. My session is easy to find, it’s the very first one on the list.

If you’re not a PASS member it’s quick and easy to join. Totally 100% free, just fill in the form and not only will you be able to vote for sweet little old me, but have access to great training videos and other information. And just because you may be a .Net developer don’t overlook the usefulness of good SQL Server resources. Understanding the power of SQL Server can make a huge difference in the performance of your application.

And if all that wasn’t enough to convince you, then I’ll resort to a shameless plea. It just so happens today is my birthday, and a vote for my session would make a great gift. You don’t even have to wrap it!

SQL Saturday 29 Birmingham

We just finished up our SQL Saturday here in Birmingham Alabama. It was number 29 in the list of SQL Saturdays. First off let me cover some supporting material for the two sessions I gave. The first was an introduction to Microsoft’s new self service BI tool, PowerPivot. Attendees can download my slides here: PowerPivot Slides  You can also see all my posts so far on PowerPivot at https://arcanecode.com/category/powerpivot/ or by picking PowerPivot from the drop down over on the right side of this blog.

My second session of the day was an introduction to SSIS. Step by step instructions, the sample project, and the slide deck can all be found on my Code Gallery site. At the end we got a bit rushed for time, hopefully some of your questions can be answered from some of my past posts on SSIS. If not feel free to send me an e-mail (rcain at comframe.com or arcanecode at gmail.com) and I’ll see what I can do to help.

Attendees of both sessions may also find my Introduction to Data Warehousing/Business Intelligence slide deck helpful to clarify some BI terminology.

In my roles as speaker, volunteer, and event planner I had little time to take pictures, but I did grab a few at the very end of the day, I thought I’d share them here:

IMAGE_152 

Some lucky winners of books looking over the remaining stack to pick out their prize.

John Baldwin, our fearless leader is in the grey shirt all the way on the right.

IMAGE_153

Obligatory crowd shot. Dividers broke this big room down to 4 rooms where we had our sessions.

IMAGE_154

One more crowd shot, showing some of the higher end swag including a Wii, a Garmin GPS, multiple

copies of Office and Windows 7, and two copies of the SQL Server MVP Deep Dives book I coauthored.

My role in event planning was acting as the speaker coordinator. Finding quality people willing to travel to Birmingham, on their own time and expense,  to give presentations. Thanks to Sven Aelterman, Kevin Boles, Louis Davidson, Janis Griffin, Kevin Grohoske, Geoff Hiten, Rodney Landrum, Vincent Mayfield, Aaron Nelson, Barry Ralston, Joe Webb and Jim Wooley. It was their presentations that helped us draw the big crowd we did.

Speaking of the crowd, much thanks to all of you who attended. The folks in my sessions were very attentive, asked many good questions, and kept the discussion lively and interesting. I’m glad all of you came and hope to see you all next year.

I also would be remiss if I didn’t thank all of our sponsors. Without their donations we would not have been able to put on the event. Microsoft, Teksouth, Bit Wizards, Confio, Redgate, Attunity, Telerik, Intellinet, CozyRoc, Wrox, TekSystems and O’Reilly Publishing and to the SQL PASS organization.

I should also give personal note of thanks to my employer COMFRAME, for putting up with my extended lunches and letting me juggle my schedule to run errands and do other planning activities and presentation prep time.

Finally a few last thanks and kudos are definitely in order. The first should go to Vito Amato and his merry band of volunteers. They kept everyone in cold drinks, helped the speakers with their needs, answered attendees questions, checked folks in at the door, and in general did everything that needed to be done to keep the event running smoothly.

A big thanks and congratulations to John Baldwin, our fearless leader, and his right hand man Morgan Smith for taking the leadership to plan and organize the event. They worked long and hard to make the event the success it was.

If you want to keep the fun and education continuing, we’d love to have you join us at our monthly user group meetings, http://www.steelcitysql.org/. Thanks for a great SQL Saturday, and I can’t wait for next year’s!

Introducing Microsoft PowerPivot

What is PowerPivot? Well according to Microsoft:

“PowerPivot is Microsoft Self-Service Business Intelligence”

I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.

Business Intelligence

Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.

In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.

In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.

IntroEx01

In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.

IntroEx02

In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.

Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.

By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.

So remind me, why do we need PowerPivot?

As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.

Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.

A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.

Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!

PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.

PowerPivot for Microsoft Excel 2010

PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.

PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.

Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.

Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.

PowerPivot for SharePoint 2010

PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.

Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.

Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.

Limitations

First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.

To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.

One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.

One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.

What does PowerPivot mean for business users?

For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.

No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.

What does PowerPivot mean for Business Intelligence IT Pros?

The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.

As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.

Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.

I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.

BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.

PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.

Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.

I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.

Where can I learn more?

Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)

There’s also the PowerPivot site itself:

I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:

Summary

Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.

Populating a Kimball Date Dimension

I’m a big fan of the Kimball method of Data Warehousing. A common task most of us setting up a new Data Warehouse face is creating a Date Dimension. In their book, “The Microsoft Data Warehouse Toolkit With SQL Server 2005 and the Microsoft Business Intelligence Toolset”, they have an example of a good date dimension table in their books sample code. My complaint though was not so much with the layout itself, I liked it and found it fairly complete. Instead it was the method they chose to load it. They used an Excel spreadsheet, then a SQL Server Integration Services package to read the Excel file and load the date dimension table.

To me this approach has a couple of drawbacks. First, if you are doing all the loading on the server itself, you may not have Excel loaded. Thus you may be faced with the headache of creating the sheet then figuring out how to get it to a location the server can read. Second, when you go to add more dates in the future, you have to go into the spreadsheet and reset everything, removing what was there before. It can also be quite a headache to go back several years from know and find both SSIS packages and that Excel spreadsheet. Plus after that time changes may be made to both Excel and SSIS that make that solution no longer workable. Finally quite often it’s a DBA setting up the warehouse, and I’ve found there are still a few DBAs who are uncomfortable relying on SSIS, although I’m happy to say that number continues to shrink.

A T-SQL solution was clearly, to me anyway, the superior answer for both ease of use and long term stability. I assumed that as popular as the Kimball method is, someone would have already created a routine to load their style of date dimension, but some Binging and Googling around proved fruitless. I did find some code for loading some very simple date dimensions, but nothing as complete as the Kimball design. So, relishing a good coding challenge, I rolled up my sleeves and went to work. Below is the fruit of my labor, a script for loading a Kimball like date dimension. All you have to do is set the begin and end dates, indicate the offset for your fiscal year, and let ‘er rip. You can easily go back and add more dates by just adjusting the begin and end times.

A few things you should note. First, I did make a few slight modifications to the standard Kimball date dimension table as found in the previously mentioned book. They have a column titled “DateName” which holds the date as a string in YYYY/MM/DD format. As long as I was putting the date in, I decided to add string versions of the date for the US and Europe. These are in MM/DD/YYYY and DD/MM/YYYY formats and the columns are named “DateNameUS” and “DateNameEU” (for European Union) respectively.

Their table also had an audit key, used presumably by the SSIS package. I didn’t really see the need for an audit key for a date table, so I changed it to an identity column so I could have a secondary surrogate key if I needed it, just something to count the number of date rows easily and track the order they were inserted in.

One final, but very important distinction. I was in a post conference session taught by Erik Veerman at SQL PASS 2009. In it he mentioned using Dim and Fact schemas, thus you’d have [Dim].[Date] instead of [dbo].[DimDate]. I liked the idea as it was something I’d been considering myself, so in this version that is what I did. If you use the more traditional naming format of dbo.DimDate you’ll need to tweak the code.

Below is the code to load the Date Dimension table, which is my creation. Under it I placed my modified version of the Kimball Date Dimension table. It’s core code came from the sample code mentioned in the first paragraph then was modified by me. I include it for completeness.

Update: A few readers aptly pointed out I’d missed replacing a static date field when I worked the final version of the code. Made the change to replace the static date with @DateCounter.

Code Sample 1 – Script to load a date dimension.

/*---------------------------------------------------------------------------*/
/* Loads a Date Dimension                                                    */
/*---------------------------------------------------------------------------*/

-- A few notes, this code does nothing to the existing table, no deletes
-- are triggered before hand. Because the DateKey is uniquely indexed,
-- it will simply produce errors if you attempt to insert duplicates.
-- You can however adjust the Begin/End dates and rerun to safely add
-- new dates to the table every year.
--
-- If the begin date is after the end date, no errors occur but nothing
-- happens as the while loop never executes.

SET NOCOUNT ON -- turn off all the 1 row inserted messages

-- Hold our dates
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

-- Holds a flag so we can determine if the date is the last day of month
DECLARE @LastDayOfMon CHAR(1)

-- Number of months to add to the date to get the current Fiscal date
DECLARE @FiscalYearMonthsOffset INT   

-- These two counters are used in our loop.
DECLARE @DateCounter DATETIME    --Current date in loop
DECLARE @FiscalCounter DATETIME  --Fiscal Year Date in loop

-- Set the date to start populating and end populating
SET @BeginDate = '01/01/2008'
SET @EndDate = '12/31/2010' 

-- Set this to the number of months to add to the current date to get
-- the beginning of the Fiscal year. For example, if the Fiscal year
-- begins July 1, put a 6 there.
-- Negative values are also allowed, thus if your 2010 Fiscal year
-- begins in July of 2009, put a -6.
SET @FiscalYearMonthsOffset = 6

-- Start the counter at the begin date
SET @DateCounter = @BeginDate

WHILE @DateCounter <= @EndDate
      BEGIN
            -- Calculate the current Fiscal date as an offset of
            -- the current date in the loop
            SET @FiscalCounter = DATEADD(m, @FiscalYearMonthsOffset, @DateCounter)

            -- Set value for IsLastDayOfMonth
            IF MONTH(@DateCounter) = MONTH(DATEADD(d, 1, @DateCounter))
               SET @LastDayOfMon = 'N'
            ELSE
               SET @LastDayOfMon = 'Y'  

            -- add a record into the date dimension table for this date
            INSERT  INTO [Dim].[Date]
                    (
                      [DateKey]
                    , [FullDate]
                    , [DateName]
                    , [DateNameUS]
                    , [DateNameEU]
                    , [DayOfWeek]
                    , [DayNameOfWeek]
                    , [DayOfMonth]
                    , [DayOfYear]
                    , [WeekdayWeekend]
                    , [WeekOfYear]
                    , [MonthName]
                    , [MonthOfYear]
                    , [IsLastDayOfMonth]
                    , [CalendarQuarter]
                    , [CalendarYear]
                    , [CalendarYearMonth]
                    , [CalendarYearQtr]
                    , [FiscalMonthOfYear]
                    , [FiscalQuarter]
                    , [FiscalYear]
                    , [FiscalYearMonth]
                    , [FiscalYearQtr]
                    )
            VALUES  (
                      ( YEAR(@DateCounter) * 10000 ) + ( MONTH(@DateCounter)
                                                         * 100 )
                      + DAY(@DateCounter)  --DateKey
                    , @DateCounter -- FullDate
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) --DateName
                    , RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2)  + '/'
                      + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName
                    , RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) + '/'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2)  + '/'
                      + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName
                    , DATEPART(dw, @DateCounter) --DayOfWeek
                    , DATENAME(dw, @DateCounter) --DayNameOfWeek
                    , DATENAME(dd, @DateCounter) --DayOfMonth
                    , DATENAME(dy, @DateCounter) --DayOfYear
                    , CASE DATENAME(dw, @DateCounter)
                        WHEN 'Saturday' THEN 'Weekend'
                        WHEN 'Sunday' THEN 'Weekend'
                        ELSE 'Weekday'
                      END --WeekdayWeekend
                    , DATENAME(ww, @DateCounter) --WeekOfYear
                    , DATENAME(mm, @DateCounter) --MonthName
                    , MONTH(@DateCounter) --MonthOfYear
                    , @LastDayOfMon --IsLastDayOfMonth
                    , DATENAME(qq, @DateCounter) --CalendarQuarter
                    , YEAR(@DateCounter) --CalendarYear
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + '-'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) --CalendarYearMonth
                    , CAST(YEAR(@DateCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @DateCounter) --CalendarYearQtr
                    , MONTH(@FiscalCounter) --[FiscalMonthOfYear]
                    , DATENAME(qq, @FiscalCounter) --[FiscalQuarter]
                    , YEAR(@FiscalCounter) --[FiscalYear]
                    , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + '-'
                      + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @FiscalCounter) AS CHAR(2))), 2) --[FiscalYearMonth]
                    , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @FiscalCounter) --[FiscalYearQtr]
                    )

            -- Increment the date counter for next pass thru the loop
            SET @DateCounter = DATEADD(d, 1, @DateCounter)
      END

SET NOCOUNT ON -- turn the annoying messages back on

-- Select all rows inserted for the final year as a sanity check
SELECT  *
FROM    [Dim].[Date]
WHERE DateKey > (YEAR(@EndDate) * 10000)

Code Sample 2 – Modified Kimball code to create a Date dimension.

/* Make sure the Dim schema exists */
IF SCHEMA_ID('Dim') IS NULL
   EXECUTE('CREATE SCHEMA [Dim] AUTHORIZATION [dbo]')
GO

/* Drop table DimDate */
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[Dim].[Date]')
                    AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
   DROP TABLE [Dim].[Date]
GO

/* Create table DimDate */
CREATE TABLE [Dim].[Date]
       ( [DateKey] BIGINT NOT NULL
       , [FullDate] DATETIME NULL
       , [DateName] CHAR(11) NULL
       , [DateNameUS] CHAR(11) NULL   --US Date FORMAT, MM/DD/YYYY
       , [DateNameEU] CHAR(11) NULL   --European Union Date Format DD/MM/YYYY
       , [DayOfWeek] TINYINT NULL
       , [DayNameOfWeek] CHAR(10) NULL
       , [DayOfMonth] TINYINT NULL
       , [DayOfYear] SMALLINT NULL
       , [WeekdayWeekend] CHAR(7) NULL
       , [WeekOfYear] TINYINT NULL
       , [MonthName] CHAR(10) NULL
       , [MonthOfYear] TINYINT NULL
       , [IsLastDayOfMonth] CHAR(1) NULL
       , [CalendarQuarter] TINYINT NULL
       , [CalendarYear] SMALLINT NULL
       , [CalendarYearMonth] CHAR(7) NULL
       , [CalendarYearQtr] CHAR(7) NULL
       , [FiscalMonthOfYear] TINYINT NULL
       , [FiscalQuarter] TINYINT NULL
       , [FiscalYear] INT NULL
       , [FiscalYearMonth] CHAR(9) NULL
       , [FiscalYearQtr] CHAR(8) NULL
       , [AuditKey] BIGINT IDENTITY NOT NULL
       , CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateKey] )
       )
ON     [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name = N'Table Type', @value = N'Dimension',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'View Name', @value = N'Date',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Date dimension contains one row for every day, beginning at 1/1/2000. There may also be rows for "hasn''t happened yet."',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'
EXEC sys.sp_addextendedproperty @name = N'Used in schemas',
  @value = N'Sales (3 roles); Finance; Currency Rates; Sales Quota (2 roles; one at Cal Qtr level)',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date'

GO

INSERT  INTO [Dim].[Date]
        ( DateKey
        , FullDate
        , [DateName]
        , [DateNameUS]
        , [DateNameEU]
        , [DayOfWeek]
        , DayNameOfWeek
        , [DayOfMonth]
        , [DayOfYear]
        , WeekdayWeekend
        , WeekOfYear
        , [MonthName]
        , MonthOfYear
        , IsLastDayOfMonth
        , CalendarQuarter
        , CalendarYear
        , CalendarYearMonth
        , CalendarYearQtr
        , FiscalMonthOfYear
        , FiscalQuarter
        , FiscalYear
        , FiscalYearMonth
        , FiscalYearQtr
        )
VALUES  ( -1
        , NULL
        , 'Unknown'
        , 'Unknown'
        , 'Unknown'
        , NULL
        , 'Unknown'
        , NULL
        , NULL
        , 'Unknown'
        , NULL
        , 'Unknown'
        , NULL
        , 'N'
        , NULL
        , NULL
        , 'Unknown'
        , 'Unknown'
        , NULL
        , NULL
        , NULL
        , 'Unknown'
        , 'Unknown'
        )
GO

EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Surrogate primary key', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Full date as a SQL date (time=00:00:00)', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard Date Format of YYYY/MM/DD', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard US Date Format of MM/DD/YYYY', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateNameUS' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Standard European Union Date Format of DD/MM/YYYY', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateNameEU' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day of week; Sunday = 1', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Day name of week', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day in the month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Number of the day in the year', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Is today a weekday or a weekend', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Week of year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Month name',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Month of year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Is this the last day of the calendar month?',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar quarter', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year and month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Calendar year and quarter', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal month of year (1..12). FY starts in July',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal quarter', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year. Fiscal year begins in July.',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year and month', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'Fiscal year and quarter', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Description',
  @value = N'What process loaded this row?', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'FK To',
  @value = N'DimAudit.AuditKey', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'20041123', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'11/23/2004', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'23-Nov-2004', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..7',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Sunday',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..31',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..365',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'Weekday, Weekend', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1..52 or 53', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'November', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Y, N',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004-01',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004Q1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'FY2004-01', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Example Values',
  @value = N'FY2004Q1', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'SCD  Type', @value = N'1',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FullDate' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'DayNameOfWeek' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'WeekdayWeekend' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'IsLastDayOfMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'CalendarYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalMonthOfYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalQuarter' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearMonth' ;
EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived',
  @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE',
  @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'FiscalYearQtr' ;
EXEC sys.sp_addextendedproperty @name = N'Source System',
  @value = N'Derived in ETL', @level0type = N'SCHEMA', @level0name = N'Dim',
  @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN',
  @level2name = N'AuditKey' ;
EXEC sys.sp_addextendedproperty @name = N'Comments',
  @value = N'In the form: yyyymmdd', @level0type = N'SCHEMA',
  @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date',
  @level2type = N'COLUMN', @level2name = N'DateKey' ;
GO