Exporting Data from SQL Server to CSV Files for Import to MongoDB Using PowerShell

I’ve been exploring other database systems, in order to determine how to import data from them using SQL Server Integration Services (SSIS). My first step though was to create some test data. I wanted something familiar, so I decided to export the Adventure Works Data Warehouse sample database and import into MongoDB. While I had many options I decided the simplest way was to first export the data to CSV files, then use the MongoDB utility mongimport. Naturally I turned to PowerShell to create an automated, reusable process.

First, if you need the Adventure Works DW database, you’ll find it at http://msftdbprodsamples.codeplex.com/. Second, I did my export from a special version of Adventure Works DW I created called AdventureWorksDW2014. This is optional, but if you want to have a version of Adventure Works DW updated with current dates, see my post at http://arcanecode.com/2013/12/08/updating-adventureworksdw2012-for-2014/. Third, I assume you are familiar with MongoDB, but if you want to learn more go to http://www.mongodb.org/.

Below is the PowerShell 3 script I created. The script is broken into four regions. The first, User Settings, contains the variables that you the user might need to change to get the script to run. It has things like the name of the SQL Server database, the path to MongoDB, etc.

The second region, Common, establishes variables that are used by the remaining two regions. You shouldn’t need to change or alter these. The third region accesses SQL Server and exports each table in the selected database to a CSV format file.

The final region, “Generate MongoDB import commands”, creates a batch (.BAT) file which has all the commands needed to run mongoimport for each CSV file. I decided not to have the PowerShell script execute the .BAT file so it could be reviewed before it is run. There might be some tables you don’t want to import, etc.

It is also quite easy to adapt this script to just create CSV files from SQL Server without using the MongoDB piece. Simply remove the fourth and final region, then in the Common and User Settings regions remove any variables what begin with the prefix “mongo”.

As the comments do a good job of explaining what happens I’ll let you review the included documentation for step by step instructions.

#==================================================================================================
# SQLtoCSVtoMongoDb.ps1
# Robert C. Cain | @ArcaneCode |
http://arcanecode.com
#
# If you need a simple way to export data from SQL Server to MongoDb, here is one way to do it.
# The script starts by setting up some variables to the server environment (see the User Settings
# region)
#
# Next, it exports data from each table in the selected database to individual CSV files.
# Finally, it generates a batch file which executes mongoimport for each csv file to import
# into MongoDb.
#
# I broke this into four regions so if all that is desired is a simple export of data to CSVs,
# you can simply omit the final region along with any variables that begin with "mongo".
#
# While I could have gone ahead and run the batch file at the end, I chose not to in order to
# give you time to review the output prior to running the batch file.
#==================================================================================================

Clear-Host

#region User Settings

  # In this section, set the variables so they are appropriate for your project / environment
 
  # This is the spot where you want to store the generated CSVs.
  # Make sure it does NOT end in a \
  $csvPath = "C:\mongodb"

  # If you are running this on a computer other than the server, set the name of the server below
  $sqlServer = $env:COMPUTERNAME

  # If you have a named instance be sure replace "default" with the name of the instance
  $sqlInstance = "\default"

  # Enter the name of the database to export below
  $sqlDatabaseName = "AdventureWorksDW2014"

  # The settings below only apply to the MongoDB code generation
  # Assemble path to mongodb. This assumes utlities are stored in the default bin folder
  $mongoPath = "C:\mongodb"
  $mongoImport = "$mongoPath\bin\mongoimport"

  # Set the server name and port
  $mongoHost = "localhost"   # Leave blank to default to localhost
  $mongoPort = ""            # Leave blank to default to 27107
 
  # Set the user name and password, leave blank if it isn’t needed
  $mongoUser = ""
  $mongoPW = ""

  # Enter the name of the database to import to.
  $mongoDatabaseName = "AdventureWorksDW2014"

  # Upserts are REALLY slow, especially on large datasets. Setting this to $true will turn off
  # the upsert option. If set to true, you are responsible for either deleting all documents
  # in the collection before hand, or allowing the risk of duplicates.
  #
  # Setting to false will enable the upsert option for mongoimport, and attempt to determine the
  # keys and (if found) add them to the final mongoimport command.
  $mongoNoUpsert = $true

#endregion

#region Common ————————————————————————————
 
  # This section sets variables used by both regions below. There is no need to alter anything
  # in this region.

  # Import the SQLPS provider (if it’s not already loaded)
  if (-not (Get-PSProvider SqlServer))
    { Import-Module SQLPS -DisableNameChecking }

  # Assemble the full servername \ instance
  $sqlServerInstance = "$sqlServer\$sqlInstance"

  # Assemble the full path for the SQL Provider to get to the database
  $sqlDatabaseLocation = "SQLSERVER:\sql\$sqlServerInstance\databases\$sqlDatabaseName"

  # Now tack on the Tables ‘folder’ to the SQL Provider path, the move there
  $sqlTablesLocation = $sqlDatabaseLocation + "\Tables"
  Set-Location $sqlTablesLocation

  # Get a list of tables in this database
  $sqlTables = Get-ChildItem

#endregion

#region Export SQL Data —————————————————————————
  # In this section we will export data from each table in the database to a CSV file.
  # WARNING: If the CSV file exists, it will be overwritten.

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # Iterate over each table in the database
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name   

    # I’ll grant you the next little bit of formatting for the progress messages is a bit
    # OCD on my part, but I like my output formatted and easy to read.
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator

    if( $sqlTableName.Length -gt 50 )
      { $padTable = " " }
    else
      { $padTable = " " * (50 – $sqlTableName.Length) }

    Write-Host -ForegroundColor White -NoNewline "Processing Table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName $padTable"
   
    # If the instance is "default", we have to exclude it when we use Invoke-SqlCmd
    if($sqlInstance.ToLower() -eq "\default")
      { $sqlSI = $sqlServer }
    else
      { $sqlSI = $sqlServerInstance }

    # Load an object with all the data in the table
    # Note if you have especially large tables you may need to modify this
    # section to break things into smaller chunks.
    $sqlCmd = "SELECT * FROM " + $sqlTableName
    $sqlData = Invoke-Sqlcmd -Query $sqlCmd `
                             -ServerInstance $sqlSI `
                             -SuppressProviderContextWarning `

    # Now write the data out.
    # Note utf8 encoding is important, as it is all mongoimport understands
    # Also need to omit the Type Info header PowerShell wants to write out
    Write-Host -ForegroundColor Yellow "    Writing to table $sqlTableName.csv"
    $sqlData | Export-Csv -NoTypeInformation -Encoding "utf8" -Path "$csvPath\$sqlTableName.csv"

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

#region Generate MongoDB import commands ———————————————————-

  # In this region we will generage the commands to import our newly exported data
  # into an existing database in MongoDB. This is an example of our desired output (wrapped
  # onto multiple lines for readability, in the output it will be a single line):

  #  C:\mongodb>bin\mongoimport –host localhost -port 27107
  #                             –db AdventureWorksDW2014 –collection DimSalesReason
  #                             –username Me –password mySuperSecureP@ssW0rd!
  #                             –type csv –headerline –file DimSalesReason.csv
  #                             –upsert –upsertFields SalesReasonKey

  # Note several of these parameters are optional, and could use defaults, or be potentially
  # omitted from the final output, based on the choices at the very beginning of this script

  # Feel free to alter the $mongoCommand as needed for other circumstances

  # Final warning, the database must already exist in MongoDb in order to import the data. This
  # script will not generate the database for you.

  # Create the name for the batch file we will generate
  $mongoBat = $csvPath + "\Import_SQL_" + $sqlDatabaseName + "_to_MongoDb_" + $mongoDatabaseName + ".bat"

  # See if file exists, if so delete it
  if (Test-Path $mongoBat)
    { Remove-Item $mongoBat }

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # mongoimport allows us to do upserts, helping to eliminate duplicate rows on import.
  #
  # To make an upsert work there has to be a key column to match up on. Fortunately,
  # most tables in the SQL Server world have Primary Keys, so we can find out what
  # columns those are and add it to the command. Note if there is no PK in SQL Server,
  # no upsert will be attempted.
  #
  # Note though that upserts are REALLY slow, so the option to skip them is
  # built into the script and set at the top (mongoNoUpsert). The generated batch file
  # assumes that either a) you have deleted all data from the collection ahead of time,
  # or b) you are OK with the risk of duplicate data.

  # Iterate over each table in the database to build the mongoimport command
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name

    # A bit more OCD progress messages
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator
    Write-Host -ForegroundColor Green "Building mongoimport command for table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName"

    # Begin building the command
    $mongoCommand = "$mongoImport "
   
    if ($mongoHost.Length -ne 0)
      { $mongoCommand += "–host $mongoHost " }

    if ($mongoPort.Length -ne 0)
      { $mongoCommand += "–port $mongoPort " }

    $mongoCommand += "–db $mongoDatabaseName –collection $sqlTableName "

    if ($mongoUser.Length -ne 0)
      { $mongoCommand += " –username $mongoUser –password $mongoPW " }

    $mongoCommand += " –type csv –headerline –file $csvPath\$sqlTableName.csv "
       
    # Build the upsert clause, if the user has elected to use it.
    if ($mongoNoUpsert -eq $false)
    {
      $mongoPKs = ""
      foreach($sqlIndex in $sqlTable.Indexes)
      {
        if($sqlIndex.IndexKeyType -eq ‘DriPrimaryKey’)
        {
          foreach($sqlCol in $sqlIndex.IndexedColumns) #$sqlPKColumns)
          {
            if ($mongoPKs.Length -ne 0)
              { $mongoPKs += "," }
            # Note column names are returned with [ ] around them, and must be removed
            # Have to use -replace instead of .Replace() because $sqlCol is an column not a string
            $mongoPKs += ($sqlCol -replace "\[", "") -replace "\]", ""
          }
               
          $mongoCommand += " –upsert –upsertFields $mongoPKs"
        }           
      }
    }

    # Append the command to the batch file
    $mongoCommand | Out-File -FilePath $mongoBat -Encoding utf8 -Append

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

 

Updating AdventureWorksDW2012 for 2014

A while back I did a post that contained a script to update the AdventureWorksDW2012 database to have dates for the 2013 time period. This will allow folks to demo date related queries and be able to simply use things like GETDATE or NOW without having to do funky math tricks to take into account the pitifully out of date offering.

I’ve now updated the script for 2014, thought I’d pass along the updated version. Note some browsers don’t seem to render the script using the mono-spaced font I intend, but just ignore. Copy and paste into SQL Server Management Studio and it should work fine.

/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2012 for Today                                                         */
/*                                                                                               */
/* Robert C. Cain, http://arcanecode.com @ArcaneCode                                             */
/*                                                                                               */
/* Script Copyright (c) 2013 by Robert C. Cain                                                   */
/* AdventureWorks database Copyright (c) Microsoft.                                              */
/*                                                                                               */
/* This script will make a backup of the AdventureWorks2012DW database, then copy and restore it */
/* as AdventureWorksDW2014. It will then update it for current dates. 2008 now becomes 2014,     */
/* 2007 is now 2012, and so forth. This script is dependent on the AdventureWorks2012DW sample   */
/* database already being installed. It won't change AdventureWorksDW2012 in anyway.             */
/*                                                                                               */
/* Be warned, if AdventureWorksDW2014 exists, it will be deleted as part of this process.        */
/*                                                                                               */
/*-----------------------------------------------------------------------------------------------*/

PRINT 'Updating AdventureWorks2012 for Today - Starting'
GO

/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2012 and restore as AdventureWorksDW2014              */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON

USE [master]

-- Step 1.1. Make a backup of AdventureWorksDW2012 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2012'
GO

BACKUP DATABASE [AdventureWorksDW2012] 
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
  WITH NOFORMAT, 
       INIT,  
       NAME = N'AdventureWorksDW2012-Full Database Backup', 
       SKIP, 
       NOREWIND, 
       NOUNLOAD,  
       STATS = 10
GO


-- Step 1.2. Delete the database AdventureWorksDW2014 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2014, if it exists'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2014' )
   )
   EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2014'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2014' )
   )
   DROP DATABASE [AdventureWorksDW2014]
GO

-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2012 to AdventureWorksDW2014'
GO

RESTORE DATABASE [AdventureWorksDW2014] 
   FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
   WITH  FILE = 1,  
   MOVE N'AdventureWorksDW2012_Data' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Data.mdf',  
   MOVE N'AdventureWorksDW2012_Log' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_log.ldf',  
        NOUNLOAD,  STATS = 5

GO

PRINT 'Done Creating AdventureWorksDW2014'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id.               */
/*-----------------------------------------------------------------------------------------------*/

USE [AdventureWorksDW2014]
GO

IF EXISTS (SELECT [name] FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

CREATE FUNCTION [dbo].[DateToDateId]
(
  @Date DATETIME
)
RETURNS INT
AS
BEGIN

  DECLARE @DateId  AS INT
  DECLARE @TodayId AS INT

  SET @TodayId = YEAR(GETDATE()) * 10000
               + MONTH(GETDATE()) * 100
               + DAY(GETDATE())         

  -- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
  -- Else convert the date to an integer
  IF @Date IS NULL OR @Date = '1900-01-01' OR @Date = -1
    SET @DateId = -1  
  ELSE
    BEGIN
      SET @DateId = YEAR(@Date) * 10000
                  + MONTH(@Date) * 100
                  + DAY(@Date)         
    END  
  
  -- If there's any data prior to 2000 it was incorrectly entered, mark it as missing
  IF @DateId BETWEEN 0 AND 19991231 
    SET @DateId = -1

  -- Commented out for this project as future dates are OK
  -- If the date is in the future, don't allow it, change to missing
  -- IF @DateId > @TodayId 
  --   SET @DateId = -1

  RETURN @DateId

END

GO




/*-----------------------------------------------------------------------------------------------*/
/* Step 3. Add new dates to the dbo.DimDate table.                                               */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Adding new dates to dbo.DimDate'
GO

SET NOCOUNT ON

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to 
-- join the day and month name memory variable tables, but need to have something to join to. 
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.
DECLARE @BogusTable TABLE
  ( PK TINYINT)

INSERT INTO @BogusTable SELECT 1;


-- Create a table variable to hold the days of the week with their various language versions
DECLARE @DayNameTable TABLE
  ( [DayNumberOFWeek]      TINYINT
  , [EnglishDayNameOfWeek] NVARCHAR(10)
  , [SpanishDayNameOfWeek] NVARCHAR(10)
  , [FrenchDayNameOfWeek]  NVARCHAR(10)
  )

INSERT INTO @DayNameTable
SELECT DISTINCT 
       [DayNumberOFWeek]      
         , [EnglishDayNameOfWeek] 
         , [SpanishDayNameOfWeek] 
         , [FrenchDayNameOfWeek]  
  FROM dbo.DimDate

-- Create a month table to hold the months and their language versions.
DECLARE @MonthNameTable TABLE
  ( [MonthNumberOfYear] TINYINT
  , [EnglishMonthName]  NVARCHAR(10)
  , [SpanishMonthName]  NVARCHAR(10)
  , [FrenchMonthName]   NVARCHAR(10)
  )

INSERT INTO @MonthNameTable
SELECT DISTINCT
       [MonthNumberOfYear] 
     , [EnglishMonthName]  
     , [SpanishMonthName]  
     , [FrenchMonthName]   
  FROM dbo.DimDate

-- This is the start and end date ranges to use to populate the 
-- dbo.DimDate dimension. Change if it's 2014 and you run across this script.
DECLARE @FromDate AS DATE = '2011-01-01'
DECLARE @ThruDate AS DATE = '2015-12-31'

-- CurrentDate will be incremented each time through the loop below.
DECLARE @CurrentDate AS DATE
SET @CurrentDate = @FromDate

-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate  AS DATE

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

  SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

  INSERT INTO dbo.DimDate
  SELECT [dbo].[DateToDateId](@CurrentDate)
       , @CurrentDate
       , DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
       , d.EnglishDayNameOfWeek
       , d.SpanishDayNameOfWeek
       , d.FrenchDayNameOfWeek
       , DAY(@CurrentDate) AS DayNumberOfMonth
       , DATEPART(dy, @CurrentDate) AS DayNumberOfYear
       , DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
       , m.EnglishMonthName
       , m.SpanishMonthName
       , m.FrenchMonthName
       , MONTH(@CurrentDate) AS MonthNumberOfYear
       , DATEPART(q, @CurrentDate) AS CalendarQuarter
       , YEAR(@CurrentDate) AS CalendarYear
       , IIF(MONTH(@CurrentDate) < 7, 1, 2) AS CalendarSemester
       , DATEPART(q, @FiscalDate) AS FiscalQuarter
       , YEAR(@FiscalDate) AS FiscalYear
       , IIF(MONTH(@FiscalDate) < 7, 1, 2) AS FiscalSemester
    FROM @BogusTable
    JOIN @DayNameTable d
      ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
    JOIN @MonthNameTable m
      ON MONTH(@CurrentDate) = m.MonthNumberOfYear

  SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END
GO

-- If you want to verify you can uncomment this line.
-- SELECT * FROM dbo.DimDate WHERE DateKey > 20110000

PRINT 'Done adding new dates to dbo.DimDate'
GO





/*-----------------------------------------------------------------------------------------------*/
/* Step 4. Update the Fact Tables with the new dates.                                            */
/*-----------------------------------------------------------------------------------------------*/


PRINT 'Update Fact Tables'
GO

SET NOCOUNT ON

-- To move forward five years, we simply add 50,000 to the date key

-- 4.1 FactFinance ------------------------------------------------------------------------------
PRINT '  FactFinance'
GO

UPDATE [dbo].[FactFinance]
   SET [DateKey] = [DateKey] + 60000;


-- 4.2 FactInternetSales ------------------------------------------------------------------------
PRINT '  FactInternetSales'
GO

-- There are a few rows where the due date is on leap year. Update these to back off a day 
-- so the date add works OK
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the rest of the days. 
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = [OrderDateKey] + 60000
     , [DueDateKey] = [DueDateKey] + 60000
     , [ShipDateKey] = [ShipDateKey] + 60000
     , [OrderDate] = DATEADD(yy, 6, [OrderDate])
     , [DueDate] = DATEADD(yy, 6, [DueDate])
     , [ShipDate] = DATEADD(yy, 6, [ShipDate])


-- 4.3 FactResellerSales ------------------------------------------------------------------------
PRINT '  FactResellerSales'
GO

-- As with Internet Sales, there are rows where the due date is on leap year. 
-- Update these to back off a day so the date add works OK
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the table
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = [OrderDateKey] + 60000
     , [DueDateKey] = [DueDateKey] + 60000
     , [ShipDateKey] = [ShipDateKey] + 60000
     , [OrderDate] = DATEADD(yy, 6, [OrderDate])
     , [DueDate] = DATEADD(yy, 6, [DueDate])
     , [ShipDate] = DATEADD(yy, 6, [ShipDate])

-- 4.4 FactSalesQuota ---------------------------------------------------------------------------
PRINT '  FactSalesQuota'
GO

UPDATE [dbo].[FactSalesQuota] 
   SET [DateKey] = [DateKey] + 60000

-- 4.5 FactSurveyResponse -----------------------------------------------------------------------
PRINT '  FactSurveyResponse'
GO

UPDATE [dbo].[FactSurveyResponse]
   SET [DateKey] = [DateKey] + 60000

-- 4.6 FactCallCenter ---------------------------------------------------------------------------
PRINT '  FactCallCenter'
GO

-- All the rows in call center have a 2010 date, just add 3 years to make these 2014
UPDATE [dbo].[FactCallCenter]
   SET [DateKey] = [DateKey] + 40000


-- 4.7 FactCurrencyRate -------------------------------------------------------------------------
PRINT '  FactCurrencyRate'
GO

-- Because the DateKey is part of the PK, we have to drop the key before we can update it
ALTER TABLE [dbo].[FactCurrencyRate] DROP CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = [DateKey] + 60000
 WHERE [DateKey] <> 20120229

-- Add the PK back
ALTER TABLE [dbo].[FactCurrencyRate] 
  ADD CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey] PRIMARY KEY CLUSTERED 
      ( [CurrencyKey] ASC,
          [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO


-- 4.8 FactProductInventory ---------------------------------------------------------------------
PRINT '  FactProductInventory'
GO

-- As with the previous step, the date is part of the primary key, so we need to drop it first.
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = [DateKey] + 60000
 WHERE [DateKey] <> 20120229
 
-- Add the PK back
ALTER TABLE [dbo].[FactProductInventory] 
  ADD CONSTRAINT [PK_FactProductInventory] PRIMARY KEY CLUSTERED 
      (    [ProductKey] ASC
      , [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO

PRINT 'Done updating the Fact tables'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 5. Cleanup, remove the helper function we added earlier.                                 */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Removing Helper Function'
GO

IF EXISTS (SELECT 1 FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

/*-----------------------------------------------------------------------------------------------*/
/* All done!                                                                                     */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorks2012 for Today - Completed'
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

CISCO VPN Error 442 and Windows 8.1

After upgrading to Windows 8.1 I had issues running Cisco VPN software. When attempting to run I got an error 442. (Note this applies to Cisco VPN, not Cisco AnyConnect.) As a first step in troubleshooting I ensured that I was on the latest version, 5.0.07.0440.

As I was already on the latest version, I began to do some web searching. Likely you, as I did, found many blog posts referring to a fix for the registry. In case you haven’t seen it, the basic instructions are:

1. Open RegEdit.

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA

3. If the DisplayName does not already read:

Cisco Systems VPN Adapter for 64 bit Windows

Change it so matches what you see above. Some installs have some “gibberish” on the front, this should be removed. In my case it was already set to what you see above, so it was on to the next step. After some more searching and experimentation, I finally came upon a solution that worked for me.

Open Windows Explorer, and navigate to C:\Program Files (x86)\Cisco Systems\VPN Client. Right click on the first .exe you find, in my case cisco_cert_mgr.exe and pick Properties from the menu. Switch to the Compatibility page.

Check on “Run this program in compatibility mode for:” and pick Windows 7. Then at the bottom, check on “Run this program as an administrator. Then click on OK.

image

 

Repeat this for every exe in the folder.

image

Unfortunately you can’t apply these in mass, you have to do these one at a time. There’s only a handful though so it shouldn’t take long.

After you are done, reboot. Before you run the VPN software, verify the Cisco network connector is not active by going to Control Panel, Network and Internet, Network Connections and validate the Cisco Systems VPN Adapter for 64 bit Windows is Disabled.

Note after you connect this will become Enabled, you just want to be sure it is disabled before you connect. If you see multiple connections for the Cisco adapter, you will need to delete the excess ones, I’ve also seen suggestions to uninstall the Cisco VPN, make sure all the Cisco connections are deleted, then reinstall. I didn’t have to go through this though, so your mileage may vary.

Also note that after you reboot, you may see a prompt asking if you want to run the Cisco service as Administrator, naturally you’ll say Yes. Then go run the Cisco VPN client again, confirming you want to run as an Admin, and you should be good to go!

Atlanta Code Camp 2013

On Saturday August 24, 2013 I’m presenting “What’s New In PowerShell v3… and 4!” at the Atlanta Code Camp. If you would like a copy of the demos, you can e-mail me either arcanecode at gmail.com or rcain at pragmaticworks.com.

Or, if you have BitTorrent Sync, you can use this secret key to get a copy of all my demos: BYEHZZ5K2DQ2AEBJDVSS4UUHZEGG646GZ  (Note this is the read-only key.)

Dealing with the Date Dimension Deployment Dilemma

For those of you who have routines that load up a date dimension, you know that it can be a little slow to run. There are times when I am deploying my database project (hey, you are using SSDT SQL Server Database Tools SQL Server Projects aren’t you???) and I want to recreate the database from scratch. While it is a great opportunity to grab another cup of coffee, tea, or read through a fantastic blog like this one, after doing this a few times you wind up with a bad case of caffeine jitters.

I had a date dimension to load today that was particularly challenging. They legitimately needed a couple hundred years worth of dates, plus there were some special calculated columns that needed to be populated. All total it took about 25 minutes to populate, on a reasonably decent sized server. Ouch. So I came up with an alternate solution that I thought I’d share with you, my adoring public (at least is seems like it from the spam comments that appear as love letters lol).

I created a second database project as part of my same solution. You can name it something generic, like “DateData”, or if you are using a date routine specific to each project, you can name it after your project database with something like “Dates” or “DateData” at the end.

This new project has two files. One is the create table script to create the only table, DimDate. The second is a post deployment script, in which I just copied over the original date dimension population script from the original project. I was able to deploy this, waiting the 25 minutes or so for it to populate my date dimension.

Back in the main database project, I deleted the code in the DimDate population post deployment script, and simply put in an INSERT statement to insert the rows in the DateData’s DimDate into the main projects DimDate. Load time was maybe 50 seconds, although it may have been shorter as I looked away for a moment.

The beauty of this is I only had to do a real load of DimDate once, since the date dimension isn’t likely to change during development, or generally at any point. Once your project is complete and on-line, you can even delete the DateData database. Should you need it again you can simply recreate it from the DateData project that was part of your solution. I can now do a full drop and create deployment (aka Publish in SSDT) as often as I want without risking caffeine poisoning or being condemned to staying awake all night doing a Phineas and Ferb marathon (which actually sounds kinda fun, now I wish I hadn’t fixed this issue. Oh well.)

Updating AdventureWorksDW2008R2 for Today

Back in May I did a post called “Updating AdventureWorksDW2012 for Today”. In that post I included a script that would take the AdventureWorksDW2012 sample database, available on CodePlex, and convert it to use current dates.

As part of my job at PragmaticWorks I teach classes on SQL Server Analysis Services. My upcoming class is using SQL Server 2008R2, and as such I wanted to update the 2008R2 version of AdventureWorksDW. I did find some subtle differences between the 2008R2 and 2012 versions of the Adventure Works Data Warehouse that I wanted to post an updated version of the script to use with 2008R2.

If you don’t have the AdventureWorksDW2008R2 data warehouse database, you can obtain it from http://msftdbprodsamples.codeplex.com/releases/view/59211 or go to CodePlex and search for SQL Server 2008 Sample Databases. Note there are several versions of Adventure Works here. The file for the data warehouse is either “AdventureWorks 2008R2 DW Script”, which is a T-SQL script that creates and populates the 2008R2 data warehouse, or “AdventureWorksDW2008R2 Data File” which is a database file you’ll need to reattach.

The structure of the script is basically the same as the one in the first post, so I’ll let you refer back to it for the explanations. Without further ado, here is the script for AdventureWorksDW2008R2, which will create a new AdventureWorksDW2013R2 database.

 

/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2008R2 for Today */
/* */
/* Robert C. Cain, http://arcanecode.com @ArcaneCode */
/* */
/* Script Copyright (c) 2013 by Robert C. Cain */
/* AdventureWorks database Copyright (c) Microsoft. */
/* */
/* This script will make a backup of the AdventureWorks2012DW database, then copy and restore it */
/* as AdventureWorksDW2013. It will then update it for current dates. 2008 now becomes 2013, */
/* 2007 is now 2012, and so forth. This script is dependent on the AdventureWorks2008R2DW sample */
/* database already being installed. It won't change AdventureWorksDW2008R2 in anyway. */
/* */
/* Be warned, if AdventureWorksDW2013R2 exists, it will be deleted as part of this process. */
/* */
/*-----------------------------------------------------------------------------------------------*/

PRINT 'Updating AdventureWorksDW2008R2 for Today - Starting'
GO

/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2008R2 and restore as AdventureWorksDW2013 */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON

USE [master]

-- Step 1.1. Make a backup of AdventureWorksDW2008R2 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2008R2'
GO

BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH NOFORMAT,
INIT,
NAME = N'AdventureWorksDW2008R2-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO


-- Step 1.2. Delete the database AdventureWorksDW2013 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2013R2, if it exists'
GO

IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2013R2'
GO

IF (EXISTS (SELECT 1
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2013R2' )
)
DROP DATABASE [AdventureWorksDW2013R2]
GO

-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2008R2 to AdventureWorksDW2013R2'
GO

RESTORE DATABASE [AdventureWorksDW2013R2]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\AdventureWorksDW2008R2.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW2008R2_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_Data.mdf',
MOVE N'AdventureWorksDW2008R2_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorksDW2013R2_log.ldf',
NOUNLOAD, STATS = 5

GO

PRINT 'Done Creating AdventureWorksDW2013R2'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id. */
/*-----------------------------------------------------------------------------------------------*/

USE [AdventureWorksDW2013R2]
GO

IF EXISTS (SELECT [name] FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
DROP FUNCTION [dbo].[DateToDateId];
GO

CREATE FUNCTION [dbo].[DateToDateId]
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @DateId AS INT
DECLARE @TodayId AS INT

SET @TodayId = YEAR(GETDATE()) * 10000
+ MONTH(GETDATE()) * 100
+ DAY(GETDATE())

-- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
-- Else convert the date to an integer
IF @Date IS NULL OR @Date = '1900-01-01' OR @Date = -1
SET @DateId = -1
ELSE
BEGIN
SET @DateId = YEAR(@Date) * 10000
+ MONTH(@Date) * 100
+ DAY(@Date)
END

-- If there's any data prior to 2000 it was incorrectly entered, mark it as missing
IF @DateId BETWEEN 0 AND 19991231
SET @DateId = -1

-- Commented out for this project as future dates are OK
-- If the date is in the future, don't allow it, change to missing
-- IF @DateId > @TodayId
-- SET @DateId = -1

RETURN @DateId

END

GO




/*-----------------------------------------------------------------------------------------------*/
/* Step 3. Add new dates to the dbo.DimDate table. */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Adding new dates to dbo.DimDate'
GO

SET NOCOUNT ON

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to
-- join the day and month name memory variable tables, but need to have something to join to.
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.
DECLARE @BogusTable TABLE
( PK TINYINT)

INSERT INTO @BogusTable SELECT 1;


-- Create a table variable to hold the days of the week with their various language versions
DECLARE @DayNameTable TABLE
( [DayNumberOFWeek] TINYINT
, [EnglishDayNameOfWeek] NVARCHAR(10)
, [SpanishDayNameOfWeek] NVARCHAR(10)
, [FrenchDayNameOfWeek] NVARCHAR(10)
)

INSERT INTO @DayNameTable
SELECT DISTINCT
[DayNumberOFWeek]
, [EnglishDayNameOfWeek]
, [SpanishDayNameOfWeek]
, [FrenchDayNameOfWeek]
FROM dbo.DimDate

-- Create a month table to hold the months and their language versions.
DECLARE @MonthNameTable TABLE
( [MonthNumberOfYear] TINYINT
, [EnglishMonthName] NVARCHAR(10)
, [SpanishMonthName] NVARCHAR(10)
, [FrenchMonthName] NVARCHAR(10)
)

INSERT INTO @MonthNameTable
SELECT DISTINCT
[MonthNumberOfYear]
, [EnglishMonthName]
, [SpanishMonthName]
, [FrenchMonthName]
FROM dbo.DimDate


-- Some dates for 2010 are already there so we have to work around it in two passes
-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate AS DATE

-- Pass 1
DECLARE @FromDate AS DATE = '2010-01-01'
DECLARE @ThruDate AS DATE = '2010-10-31'
DECLARE @CurrentDate AS DATE

-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

INSERT INTO dbo.DimDate
SELECT [dbo].[DateToDateId](@CurrentDate)
, @CurrentDate
, DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
, d.EnglishDayNameOfWeek
, d.SpanishDayNameOfWeek
, d.FrenchDayNameOfWeek
, DAY(@CurrentDate) AS DayNumberOfMonth
, DATEPART(dy, @CurrentDate) AS DayNumberOfYear
, DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
, m.EnglishMonthName
, m.SpanishMonthName
, m.FrenchMonthName
, MONTH(@CurrentDate) AS MonthNumberOfYear
, DATEPART(q, @CurrentDate) AS CalendarQuarter
, YEAR(@CurrentDate) AS CalendarYear
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END AS FiscalSemester
FROM @BogusTable
JOIN @DayNameTable d
ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
JOIN @MonthNameTable m
ON MONTH(@CurrentDate) = m.MonthNumberOfYear

SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END

-- Pass 2
-- This is the start and end date ranges to use to populate the
-- dbo.DimDate dimension. Change if it's 2014 and you run across this script.
SET @FromDate = '2010-12-01'
SET @ThruDate = '2013-12-31'

-- CurrentDate will be incremented each time through the loop below.
SET @CurrentDate = @FromDate

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

INSERT INTO dbo.DimDate
SELECT [dbo].[DateToDateId](@CurrentDate)
, @CurrentDate
, DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
, d.EnglishDayNameOfWeek
, d.SpanishDayNameOfWeek
, d.FrenchDayNameOfWeek
, DAY(@CurrentDate) AS DayNumberOfMonth
, DATEPART(dy, @CurrentDate) AS DayNumberOfYear
, DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
, m.EnglishMonthName
, m.SpanishMonthName
, m.FrenchMonthName
, MONTH(@CurrentDate) AS MonthNumberOfYear
, DATEPART(q, @CurrentDate) AS CalendarQuarter
, YEAR(@CurrentDate) AS CalendarYear
, CASE WHEN MONTH(@CurrentDate) < 7
THEN 1
ELSE 2
END AS CalendarSemester
, DATEPART(q, @FiscalDate) AS FiscalQuarter
, YEAR(@FiscalDate) AS FiscalYear
, CASE WHEN MONTH(@FiscalDate) < 7
THEN 1
ELSE 2
END AS FiscalSemester
FROM @BogusTable
JOIN @DayNameTable d
ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
JOIN @MonthNameTable m
ON MONTH(@CurrentDate) = m.MonthNumberOfYear

SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END
GO

-- If you want to verify you can uncomment this line.
-- SELECT * FROM dbo.DimDate WHERE DateKey > 20110000

PRINT 'Done adding new dates to dbo.DimDate'
GO


/*-----------------------------------------------------------------------------------------------*/
/* Step 4. Update the Fact Tables with the new dates. */
/*-----------------------------------------------------------------------------------------------*/


PRINT 'Update Fact Tables'
GO

SET NOCOUNT ON

-- To move forward five years, we simply add 50,000 to the date key

-- 4.1 FactFinance ------------------------------------------------------------------------------
PRINT ' FactFinance'
GO

UPDATE [dbo].[FactFinance]
SET [DateKey] = [DateKey] + 50000;


-- 4.2 FactInternetSales ------------------------------------------------------------------------
PRINT ' FactInternetSales'
GO

-- There are a few rows where the due date is on leap year. Update these to back off a day
-- so the date add works OK
UPDATE [dbo].[FactInternetSales]
SET [OrderDateKey] = 20080228
WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229

-- Now update the rest of the days.
UPDATE [dbo].[FactInternetSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000


-- 4.3 FactResellerSales ------------------------------------------------------------------------
PRINT ' FactResellerSales'
GO

-- As with Internet Sales, there are rows where the due date is on leap year.
-- Update these to back off a day so the date add works OK
UPDATE [dbo].[FactResellerSales]
SET [OrderDateKey] = 20080228
WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
SET [DueDateKey] = 20080228
WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
SET [ShipDateKey] = 20080228
WHERE [ShipDateKey] = 20080229

-- Now update the table
UPDATE [dbo].[FactResellerSales]
SET [OrderDateKey] = [OrderDateKey] + 50000
, [DueDateKey] = [DueDateKey] + 50000
, [ShipDateKey] = [ShipDateKey] + 50000

-- 4.4 FactSalesQuota ---------------------------------------------------------------------------
PRINT ' FactSalesQuota'
GO

UPDATE [dbo].[FactSalesQuota]
SET [DateKey] = [DateKey] + 50000

-- 4.5 FactSurveyResponse -----------------------------------------------------------------------
PRINT ' FactSurveyResponse'
GO

UPDATE [dbo].[FactSurveyResponse]
SET [DateKey] = [DateKey] + 50000

-- 4.6 FactCallCenter ---------------------------------------------------------------------------
PRINT ' FactCallCenter'
GO

-- All the rows in call center have a 2010 date, just add 3 years to make these 2013
UPDATE [dbo].[FactCallCenter]
SET [DateKey] = [DateKey] + 30000


-- 4.7 FactCurrencyRate -------------------------------------------------------------------------
PRINT ' FactCurrencyRate'
GO

-- Because the DateKey is part of the PK, we have to drop the key before we can update it
ALTER TABLE [dbo].[FactCurrencyRate] DROP CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactCurrencyRate]
SET [DateKey] = 20120229
WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactCurrencyRate]
SET [DateKey] = [DateKey] + 50000
WHERE [DateKey] <> 20120229

-- Add the PK back
ALTER TABLE [dbo].[FactCurrencyRate]
ADD CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey] PRIMARY KEY CLUSTERED
( [CurrencyKey] ASC,
[DateKey] ASC
)
WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO


PRINT 'Done updating the Fact tables'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 5. Cleanup, remove the helper function we added earlier. */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Removing Helper Function'
GO

IF EXISTS (SELECT 1 FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
DROP FUNCTION [dbo].[DateToDateId];
GO

/*-----------------------------------------------------------------------------------------------*/
/* All done! */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorksDW2008R2 for Today - Completed'
GO

Make Your SSAS Data Source View Pretty

Anyone who works with SSAS (SQL Server Analysis Services) knows the DSV (Data Source View) is the key to the project. It is through the DSV that everything else is built on. Unfortunately, in most projects I’ve worked on, it is generally the biggest mess.

Take this simple example cube based on the Adventure Works data warehouse.

image

What a mess! Fortunately there is a very simple way to clean it up.

Go up to the toolbar area. Right click to bring up a list of available toolbars, and pick the Layout.

image

Now you see a new toolbar appear:

image

Hover your mouse over each item, you’ll see tool tips such as Left Alight, Right Alight, Align Tops, and more. Note that in the menus there is a menu named Format. The same items on the toolbar also appear in the menu. I find it a little easier to use the toolbar, but do what you are comfortable with.

image

OK, now that we have our tools ready, we can start cleaning up that messy DSV. There are two ways to select the tables (or views) that we want fix up. First, you can simply click in an empty area of the design surface and drag the mouse. A little dotted line outline will appear showing you which tables will be in the selection.

image

The other option is to click on the first table, what is known as the “reference”. You’ll know the reference because it has white border handles. Then CTRL+Click on the other tables you wish to align, or make the same size as, the reference table. You’ll know these because they have a thick black square on the sides and border.

image

Now go to the layout bar or the menu, and find the button for align lefts. Click, then click the button for make same width. Repeat the process for the other tables in the DSV. When you are done it could look this pretty:

image

With just a few minutes work your DSV is now organized into neat rows and columns of uniform width. This makes it much easier to read. Your eye is not distracted by the jagged alignment and the uneven widths. Instead, you can much more easily focus on the text inside the boxes, which is after all the important part.

One last tip, if you wish to move the selected table (or tables) a bit, hold down the CTRL key, then use the arrows to move everything in tiny steps to the position you want.

I did the above example using SQL Server 2008R2 BIDS, this technique also works with the SQL Server Data Tools that shipped with SQL Server 2012 (SSDT, aka Visual Studio 2010) and with the newer SSDT for Visual Studio 2012.

Follow

Get every new post delivered to your Inbox.

Join 103 other followers