The Data Queen – Page 2 – Explorations in Business Intelligence with Microsoft

Compare Data and Database Schemas

I was thrilled to discover the functionality to compare database data and schemas is available in Microsoft Visual Studio. It not only allows you to compare two databases, but it will update the target database or write an update script for you to allow you to update the target database.

Most of this functionality has existed in Visual Studio Team System Database Edition since 2005. Team System Database edition is used by database developers and administrators and includes advanced tools for database change management and testing. Database schema comparison is now available in Visual Studio Premium and Visual Studio Ultimate since 2010. Unfortunately it’s not in Professional or Express.

Having the database schema compare functionality in Visual Studio is awesome for developers who straddle several worlds. It’s nice to be able to build an application that affects or relies on a database, and also be able to compare the database schemas among environments, even if building the database doesn’t fall within your purview, all within one tool.

The comparison is really easy to do. Look in Tools->SQL Server

Select your source and target database

Click Compare and it will compare the databases. Here I have compared the schemas of two databases.

The tool highlights the differences. In this case a new field called [newfield] exists in the table [Threads] in the source database but not the destination database.

Click the script icon to generate a SQL script for the changes to the destination database.

Or click the Update button to actually do the update of the destination database.

How sweet is that? I know I’ll be using this regularly in all kinds of development.

Report Manager Subscription Last Day of Month

I have a client who wants to create report subscriptions which run on the last day of each month. I am surprised to discover that this doesn’t come out-of-the-box with Report Manager.

The subscription schedule allows a number of options by hour, day, week, month. The Monthly Schedule option allows for Last week of month, but the user then has to choose the day of the week or calendar day. And when I insert 31 as the calendar day it gives an error message, presumably because some months do not have a 31st day. There is no option for last day of month.

Here is one workarounds to this: One option would be to run the subscription on the first day of the month, defaulting the report to the previous month. For this to be an option you would need : 1. A data parameter in the report and 2a. The report already defaults to the previous month or 2b. An edition of SQL server which supports data driven subscriptions in order to override the default parameter to last month. This is not an option for my client.

The solution that worked was a little different. You need access to be able to create a Shared Schedule on the Report Manager site, and access to the ReportServer database and SQL Server Agent on the report server. What you will do is create a Shared Schedule, which in turn creates a SQL Server Agent job. And then you will edit that job in SSMS to run on the last day of the month. Any report subscriptions can then use that Shared Schedule to run on the last day of the month. You could do something similar for each individual subscription, but why would you, when you can do it only once in a Shared Schedule.

Here are the steps:

1. Create a Shared Schedule in Report Manager

In Report Manager, go to Site Settings and select Schedules. You will need the right permissions to be able to access this. Click on New Schedule. Create a schedule. It doesn’t really matter what you set it to, since you will be changing it on the back end. Note that the front end Schedules list will reflect the changes that you make to the back end, but if you try to edit the schedule your original values will still be there. Give the schedule a descriptive name to identify it. I called it "Last Day of Month".

2. Find out the ScheduleID of the Shared Schedule you have created

Run this query in the ReportServer database to find out the ScheduleID.

SELECT ScheduleID FROM Schedule WHERE Name = 'Last Day of Month'

3. Find the SQL Server Agent Job for that ScheduleID

In SSMS navigate to SQL Server Agent and find the corresponding job.

4. Edit the properties of that job to run on the last day of the month

Make sure the schedule is Enabled.

The Shared Schedule will reflect Last Run and Next Run values correctly in the Schedules pane. Note that the details of the schedule will NOT reflect this, since it is not an available option in the Report Manager interface.

Now a user can choose this schedule when setting up a subscription.

I found this blog by Andreas Halleraker really helpful in finding a good solution.

Format row delimited values into comma delimited

If you just want a quick and dirty way to format row delimited data into comma delimited for consumption in a query, you can use Word. First you have to turn off the Autocorrect for Smart Quotes, otherwise you will get smart quotes instead of straight quotes in your query. Depending on your version of Word you will find AutoCorrect under Options in the Proofing section or under Tools.

Then you can Find and Replace any carriage returns with ‘,’ using Find and Replace to find ^p and replace it with ‘,’

Your results will look like this:
Name1′,’Name2′,’Name3′,’

You then just need to add a single quote at the beginning and remove the last comma and quote.

How to Automate SSAS Cube Partitioning in SSIS

I have a client whose data is large enough that their cube needs to be partitioned monthly. This process needed to be automated. I would like to thank Vidas Matelis for posting the scripts he used to do this, here. I started with his scripts and changed a number of things to get it to work in my environment. This is done in SQL 2008. Here is how it works.

1. Create schema and tables to hold the partition definitions

USE [yourdatabase]
GO

CREATE SCHEMA [config] AUTHORIZATION [dbo]
GO

CREATE TABLE [config].[SSASPartitions](
    [CubeName] [varchar](100) NOT NULL,
    [MeasureGroup] [varchar](100) NOT NULL,
    [Partition] [varchar](100) NOT NULL,
    [SQL] [varchar](3000) NOT NULL,
    [MinDateKey] [int] NOT NULL,
    [MaxDateKey] [int] NOT NULL,
    [NeedsReProcessing] [bit] NOT NULL,
    [LastProcessDate] [smalldatetime] NULL,
 CONSTRAINT [PK_SSASPartitions] PRIMARY KEY CLUSTERED 
(
    [CubeName] ASC,
    [MeasureGroup] ASC,
    [Partition] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [config].[SSASPartitionsBase](
    [CubeName] [varchar](100) NOT NULL,
    [MeasureGroup] [varchar](100) NOT NULL,
    [FactTableName] [varchar](3000) NOT NULL,
    [PartitionFieldName] [varchar](100) NULL,
    [TableStartDateKey] [int] NULL,
    [LoadType1] [varchar](20) NULL,
    [LoadType2] [varchar](20) NULL,
    [LoadType3] [varchar](20) NULL,
 CONSTRAINT [PK_SSASPartitionsBase] PRIMARY KEY CLUSTERED 
(
    [CubeName] ASC,
    [MeasureGroup] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2. Add Execute SQL tasks to populate the tables with Measure Group and Partition definitions

This is the first container in the package, which simply populates the tables created in step 1.

Add these Execute SQL Tasks to your SSIS package.

2a) Delete FROM SSASPartitionsBase

Delete FROM config.SSASPartitionsBase

2b) Insert Measure Groups

Below you will find some examples of rows to be inserted into the SSASPartitionsBase table. This table is used as the base for building out the partition definitions. You will need one record for each measure group in your cube, regardless of one or many partitions. If you will require multiple partitions for a measure group, you must populate the PartitionFieldName and the TableStartDateKey so the script will know what SQL to write to define the partitions.

If you are pulling data into your cube using a query rather then directly from a table you must enter it here.

You may want to use LoadType if you will be defining different partitions for different data sets. I am not using this because any of my measure groups that need multiple partitions will use the same periods for the partitions. This will become clearer when we look at the Stored Procedure which populates the SSASPartitions table.

INSERT INTO config.SSASPartitionsBase
(
CubeName, 
MeasureGroup, 
FactTableName, 
PartitionFieldName, 
TableStartDateKey, 
LoadType1)

-----------------------------------------
--Single partition does not require PartitionFieldName or TableStartDateKey
-----------------------------------------

--FACT table with single partition
-----------------------------------------
SELECT 'Sales and Inventory' as CubeName, 
'Planning' as MeasureGroup, 
'FACT_Planning' as FactTableName, 
NULL as PartitionFieldName,
NULL as TableStartDateKey, 
'Planning' as LoadType1

UNION

--FACT table with multiple partitions
-----------------------------------------
SELECT 'Sales and Inventory' as CubeName, 
'Inv OH' as MeasureGroup, 
'FACT_Inv_OH' as FactTableName, 
'Date' as PartitionFieldName, --Required
'20070101' as TableStartDateKey, --Required
'Inventory' as LoadType1

UNION


--View on Fact Tables with multiple partitions
-----------------------------------------
SELECT 'Sales and Inventory' as CubeName, 
'Sales' as MeasureGroup, 
'vwFACT_Sales' as FactTableName, --Required
'Transaction_Date' as PartitionFieldName, --Required
'20070101' as TableStartDateKey, 
'Sales' as LoadType1

UNION

--Query on a FACT table with single partition
--no PartitionFieldName or TableStartDateKey
-----------------------------------------
SELECT 'Sales and Inventory' as CubeName, 
'Purchase Orders' as MeasureGroup, 
'(SELECT Expected_Receipt_Date, Product_ID, 
  SUM(Due_In_Quantity) AS Open_PO_DueTW_Quantity
  FROM FACT_PO AS PO WHERE (Due_In_Quantity > 0) 
  AND (Expected_Receipt_Date <= GETDATE()) 
  GROUP BY Expected_Receipt_Date, Product_ID) X' 
     as FactTableName,  
NULL as PartitionFieldName,
NULL as TableStartDateKey, 
'PO' as LoadType1

2c) Delete Current Open Period partitions

Because I have other SSIS packages that process the Open Period during the week, I need to have one partition in each multi-partition measure group that has a consistent name. I have a naming convention like “Sales Current Open Period” or “Inv OH Current Open Period”. The definition of the Current Open Period changes periodically, as a new monthly partition is created. For example I might have a Current Open Period of Nov 1 to current day. Let’s on November 30 a partition is saved off and the Current Open Period becomes Dec 1 to current day. The Current Open Period partition needs to be dropped and recreated each time the script is run, to be sure that any new definition will be captured and processed. Dropping it from the SSASPartitions table ensure that it will be reinserted with the updated definition.

Delete
  FROM [config].[SSASPartitions]
  WHERE Partition like '%Open Period'

2d) Update Process status of all partitions to be processed

Any single partition measure groups need to be reprocessed every time the script is run. We can identify these measure groups as those where MinDateKey=0. This task flags these single partition measure groups to be processed.

  UPDATE config.SSASPartitions 
  SET NeedsReProcessing = 1 
  WHERE MinDateKey=0

2e) Execute sp_Execute_CalculateSSASPartitions to add any new partitions to the SSASPartitions table

This stored procedure kicks off a stored procedure which adds any new partitions to the SSASPartitions table.

EXECUTE sp_CalculateSSASPartitions

Stored Procedure

This is the stored procedure which uses the SSASPartitionsBase table to populate the SSASPartitions table.

USE [yourdatabase]
GO

/****** Object:  StoredProcedure [dbo].[sp_CalculateSSASPartitions]    Script Date: 05/12/2014 15:45:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_CalculateSSASPartitions]
WITH RECOMPILE 
AS 
BEGIN

SET NOCOUNT ON
PRINT 'Executing SP: CalculateSSASPartitions'

-- ---------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------
--Create temp table
IF OBJECT_ID('tempdb..#tmpPart') IS NOT NULL DROP TABLE #tmpPart
CREATE TABLE #tmpPart (
CubeName varchar(100) NOT NULL
, MeasureGroup varchar(100) NOT NULL
, [Partition] varchar(200) NOT NULL
, [SQL] varchar(3000) NOT NULL
, MinDateKey int NOT NULL
, MaxDateKey int NOT NULL
)

-----------Partitions using Archive tables-----------
INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
SELECT 
P.CubeName, 
P.MeasureGroup,
P.MeasureGroup + ' ' + D.PartitionName AS Partition,
'SELECT * FROM ' + 
    CASE WHEN D.PartitionName = 'Current Open Period' THEN P.FactTableName ELSE P.FactTableName  + '_Archive' END
    + ' WHERE ' + P.PartitionFieldName
    + CASE WHEN MinDateKey = MaxDateKey THEN '=' + CONVERT(varchar, MinDateKey)
    ELSE ' BETWEEN ''' + CONVERT(varchar, D.MinDateKey) + ''' AND ''' + CONVERT(varchar, D.MaxDateKey) + ''''
    END
AS SQL,
CASE WHEN D.MinDateKey < P.TableStartDateKey THEN P.TableStartDateKey ELSE D.MinDateKey END AS MinDateKey,
D.MaxDateKey
FROM config.SSASPartitionsBase P
JOIN (
    --Add FiscalYear/Month partitions up to Closed Period
    SELECT CAST(FiscalMonthID as varchar) AS PartitionName, MIN(DateID) AS MinDateKey, MAX(DateID) AS MaxDateKey
        FROM DIM_Date
        WHERE FiscalYearID >= (SELECT DISTINCT FiscalYearID FROM DIM_Date WHERE TheDate = CAST(DATEADD(year, -3, GETDATE()) as Date))
        AND TheDate <= (SELECT MAX(ClosedDate) FROM DIM_Closed_Period WHERE DataArchived = 1)
        GROUP BY FiscalMonthID
    UNION ALL
    --Current Open Period
    SELECT 'Current Open Period' AS PartitionName, MIN(DateID) AS MinDateKey, MAX(DateID) AS MaxDateKey
        FROM DIM_Date
        WHERE TheDate > (SELECT MAX(ClosedDate) FROM DIM_Closed_Period WHERE DataArchived = 1)
        AND TheDate <= (SELECT  MAX(ClosedDate) as MaxClosedDate FROM DIM_Closed_Period)
) D ON D.MaxDateKey >= P.TableStartDateKey

WHERE P.MeasureGroup IN ('Inv OH Orig', 'Inv OH','Sales','Sales Customer','Sales Transaction','Sales Cost')
ORDER BY D.PartitionName

-- -------------------------------------------------
-- Add measure groups that will not be partitioned
INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
SELECT P.CubeName, P.MeasureGroup, P.MeasureGroup AS Partition
, 'SELECT * FROM ' + P.FactTableName AS SQL
, 0 AS MinDateKey, 999999 AS MaxDateKey
FROM config.SSASPartitionsBase P
WHERE ISNULL(PartitionFieldName,'') = ''

-- Safety. If we miss any measure groups, add records as if they were not partitioned.
INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
SELECT CubeName, MeasureGroup, MeasureGroup AS [Partition], 'SELECT * FROM ' + FactTableName AS [SQL]
, 0 MinDateKey, 999999 MaxDateKey
FROM config.SSASPartitionsBase B
WHERE NOT EXISTS(SELECT * FROM #tmpPart T WHERE T.MeasureGroup = B.MeasureGroup)
-- ----------------------------------------------------------------------------------------------------------------------------------

DELETE FROM config.SSASPartitions
FROM config.SSASPartitions P
JOIN config.SSASPartitionsBase B ON B.CubeName = P.CubeName AND B.MeasureGroup = P.MeasureGroup
WHERE
-- (B.LoadType1 = @Action OR B.LoadType2 = @Action OR B.LoadType3 = @Action) AND
NOT EXISTS(SELECT * FROM #tmpPart T WHERE T.CubeName = P.CubeName AND T.MeasureGroup = P.MeasureGroup
AND T.Partition = P.Partition)

PRINT 'Deleted partitions that do not exists in new list. Record count: ' + CONVERT(varchar, @@ROWCOUNT)

INSERT INTO config.SSASPartitions(CubeName, MeasureGroup, [Partition], [SQL]
, MinDateKey, MaxDateKey, NeedsReProcessing)
SELECT CubeName, MeasureGroup, [Partition], [SQL]
, MinDateKey, MaxDateKey, 1 AS NeedsReProcessing
FROM #tmpPart T
WHERE NOT EXISTS(SELECT * FROM config.SSASPartitions P 
WHERE P.CubeName = T.CubeName AND P.MeasureGroup = T.MeasureGroup
AND P.Partition = T.Partition
)

PRINT 'Inserted partitions that do not exists. Record count: ' + CONVERT(varchar, @@ROWCOUNT)
END

GO

3. Create Two Variables in your package

4.Add tasks to Drop, Create and Process Partitions

This is the second and final container that uses the information created in the first steps to actually drop, create and process any partitions which are new or which need to be reprocessed.

4a) Get DB Partitions List

SELECT CubeName,MeasureGroup, Partition, SQL, NeedsReProcessing 
FROM config.SSASPartitions 
ORDER BY 1,2,3

4b) Drop Older Partitions and Current Open Partitions

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb
Imports Microsoft.AnalysisServices

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Help, press F1.

    Public Sub Main()
        '--------------------------------------------------------------------------------------
        'THIS PACKAGE WILL DROP ANY MISSING PARTITIONS AND ANY "CURRENT OPEN PERIOD" PARTITIONS
        '--------------------------------------------------------------------------------------

        Dim ExecutionSuccessfull As Boolean = True ' If true, package executed without errors
        Dim PartListInDM(999, 2) As String ' expecting no more than a 1000 partitions.
        Dim DropPartCount As Integer = 0

        ' These objects capture execution results
        Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
        Dim oResult As Microsoft.AnalysisServices.XmlaResult
        Dim oMessage As Microsoft.AnalysisServices.XmlaMessage
        Dim dataBytes(0) As Byte

        ' These objects capture your partitions table
        Dim oOleDB As New OleDbDataAdapter
        Dim oTable As New DataTable
        Dim oRow As DataRow
        oOleDB.Fill(oTable, Dts.Variables("PartitionListInDW").Value)

        'Connect to SSAS database
        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections("Cube")
        Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
        Dim sDatabase As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

        If oDB Is Nothing Then
            Dts.Log("Did not find expected database: " & sDatabase, 0, dataBytes) 'You need to setup custom  package logging to see this
            ExecutionSuccessfull = False
            GoTo Done
        End If

        'Cube attributes
        Dim oCube As Microsoft.AnalysisServices.Cube
        Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
        Dim oPartition As Microsoft.AnalysisServices.Partition
        Dim bNeedToDropPartition As Boolean

        Dim strCube As String           'Cube - Cube Name
        Dim strMeasureGroup As String   'Cube - Measure Group
        Dim strPartition As String      'Cube - Partition
        Dim strRowCube As String        'Table - Cube Name
        Dim strRowMeasureGroup As String 'Table - Measure Group
        Dim strRowPartition As String   'Table - Partition
        Dim bMatch As Boolean           'String match

        'Iterate through each partition in the cube
        For Each oCube In oDB.Cubes
            strCube = oCube.Name
            For Each oMeasureGroup In oCube.MeasureGroups
                strMeasureGroup = oMeasureGroup.Name
                For Each oPartition In oMeasureGroup.Partitions
                    strPartition = oPartition.Name
                    bNeedToDropPartition = True
                    'Sets bMatch to True or False.  If Partition name like '%Current Open Period' then bMatch = True
                    bMatch = strPartition Like "*Current Open Period*"
                    'Iterate through each partition in the table and check if it exists in the cube already. 
                    For Each oRow In oTable.Rows
                        strRowCube = oRow("CubeName").ToString
                        strRowMeasureGroup = oRow("MeasureGroup").ToString
                        strRowPartition = oRow("Partition").ToString

                        'If the partition exists then don't drop it, except "Current Open Period" partitions
                        If strRowCube = strCube And strRowMeasureGroup = strMeasureGroup _
                        And strRowPartition = strPartition And bMatch = "False" Then
                            bNeedToDropPartition = False
                            Dts.Log("PARTITION FOUND - don't drop" & strRowPartition & "  Partition : " & strPartition, 0, dataBytes)
                            Exit For
                        End If

                    Next


                    'If the Partition does need to be dropped, then add the Partition details to the PartListInDM string object
                    If bNeedToDropPartition Then
                        PartListInDM(DropPartCount, 0) = oCube.Name
                        PartListInDM(DropPartCount, 1) = oMeasureGroup.Name
                        PartListInDM(DropPartCount, 2) = oPartition.Name
                        DropPartCount = DropPartCount + 1
                        Dts.Log("Found partition that needs to be dropped: " & oPartition.Name, 0, dataBytes) ' You need to setup package logging to see this
                    End If
                Next
            Next
        Next

        'Write the XML based on the PartListInDM string object to drop the Partitions
        '-------------------
        'START CAPTURING XML
        '-------------------
        oServer.CaptureXml() = True
        Dim i As Integer = 0
        For i = 0 To DropPartCount - 1
            oCube = oDB.Cubes.FindByName(PartListInDM(i, 0))
            oMeasureGroup = oCube.MeasureGroups.FindByName(PartListInDM(i, 1))
            oPartition = oMeasureGroup.Partitions.FindByName(PartListInDM(i, 2))
            oPartition.Drop()
        Next i
        oServer.CaptureXml() = False
        '-------------------
        'STOP CAPTURING XML
        '-------------------

        'Execute the log that contains the XMLA commands and drop the Partitions
        oResults = oServer.ExecuteCaptureLog(True, False) 'Execute within transaction=True, Execute in parallel=False

        'Log errors and warnings
        For Each oResult In oResults
            For Each oMessage In oResult.Messages
                If oMessage.GetType.Name = "XmlaError" Then
                    'Errors
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom  package logging to see this
                    ExecutionSuccessfull = False 'Fail the Scrip Task
                Else
                    'Warnings
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom  package logging to see this
                    ExecutionSuccessfull = True  'Don't fail the Scrip Task
                End If
            Next oMessage
        Next oResult
        ' -------------------------------------------------------------------------------------------------------------------------

Done:
        oServer.Disconnect()

        If ExecutionSuccessfull Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If


    End Sub

End Class

4c) Check if this is Full Reprocessing

This will check to see if the full cube will be reprocessed, or just some of the partitions.

SELECT CONVERT(bit, CASE WHEN PartCount = 0 THEN 1 ELSE 0 END) AS NeedsFullReprocessing
FROM (SELECT COUNT(*) AS PartCount FROM config.SSASPartitions WHERE NeedsReProcessing <> 1) B

4d) Process All Dims

This will always process all dimensions.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Help, press F1.

    Public Sub Main()
        '
        Dim ExecutionSuccessfull As Boolean = True ' If true, package executed without errors
        Dim dataBytes(0) As Byte
        ' Dim sProcessType As String = "ProcessFull"

        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections("Cube")
        Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
        Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabaseID)

        Dim oThisIsFullReprocessing As Variable = Dts.Variables("ThisIsFullReprocessing") ' From variable
        Dim ThisIsFullReprocessing As Boolean = CType(oThisIsFullReprocessing.Value, Boolean)

        Dim ProcessType As Microsoft.AnalysisServices.ProcessType

        If ThisIsFullReprocessing Then
            ProcessType = Microsoft.AnalysisServices.ProcessType.ProcessFull
            Dts.Log("Will be doing FULL dimension processing", 0, dataBytes) ' You need to setup proper package loging to see this!
        Else
            ProcessType = Microsoft.AnalysisServices.ProcessType.ProcessUpdate
            Dts.Log("Will be doing UPDATE dimension processing", 0, dataBytes) ' You need to setup proper package loging to see this!
        End If

        If oDB Is Nothing Then
            ExecutionSuccessfull = False
            GoTo Done
        Else
            Dim oDim As Microsoft.AnalysisServices.Dimension

            oServer.CaptureXml() = True ' Start capturing XML. 
            For Each oDim In oDB.Dimensions
                ' This will generate XMLA, but because CaptureXML is True, will not execute it!
                If (oDim.MiningModel Is Nothing) Then
                    oDim.Process(ProcessType)
                End If

            Next
            oServer.CaptureXml() = False ' Stop capturing XML
            ' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected 
            ' These are very important parameters!

            Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
            Dim oResult As Microsoft.AnalysisServices.XmlaResult

            oResults = oServer.ExecuteCaptureLog(True, True)
            'oResults = oServer.ExecuteCaptureLog(False, False)

            Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

            Dim bt(0) As Byte
            'Log the errors and warnings
            For Each oResult In oResults
                For Each oMessage In oResult.Messages
                    If oMessage.GetType.Name = "XmlaError" Then
                        Dts.Log(oMessage.Description, 0, bt)

                        'The processing failed
                        ExecutionSuccessfull = False
                    Else
                        'It's just a warning. 
                        Dts.Log(oMessage.Description, 0, bt)
                        ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
                    End If
                Next oMessage
            Next oResult

        End If
Done:
        oServer.Disconnect() ' disconnect from the server -- we are done

        If ExecutionSuccessfull Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If

        '
    End Sub

End Class

4e) Get DB Partitions List

SELECT CubeName,MeasureGroup, Partition, SQL, NeedsReProcessing 
FROM config.SSASPartitions 
ORDER BY 1,2,3

4f) Create Partitions that don’t exist in the cube

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Help, press F1.

    Public Sub Main()
        '------------------------------------------------------------------------
        'THIS SCRIPT TASK WILL CREATE ANY PARTITIONS IN THE LIST THAT DON'T EXIST
        '------------------------------------------------------------------------

        Dim ExecutionSuccessfull As Boolean = True ' If true, package executed without errors

        ' These objects capture execution results
        Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
        Dim oResult As Microsoft.AnalysisServices.XmlaResult
        Dim oMessage As Microsoft.AnalysisServices.XmlaMessage
        Dim dataBytes(0) As Byte

        ' These objects capture partitions table columns
        Dim oOleDB As New OleDbDataAdapter
        Dim oTable As New DataTable
        Dim oRow As DataRow
        oOleDB.Fill(oTable, Dts.Variables("PartitionListInDW").Value)

        'Connect to SSAS Database
        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections("Cube")
        Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
        Dim sDatabase As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

        If oDB Is Nothing Then
            Dts.Log("Did not find expected database: " & sDatabase, 0, dataBytes) 'You need to setup custom package logging to see this
            ExecutionSuccessfull = False
            GoTo Done
        End If

        'Cube attributes
        Dim oCube As Microsoft.AnalysisServices.Cube
        Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
        Dim oPartition As Microsoft.AnalysisServices.Partition
        Dim strRowCube As String
        Dim strRowMeasureGroup As String
        Dim strRowPartition As String

        '-------------------
        'START CAPTURING XML
        '-------------------
        oServer.CaptureXml() = True

        For Each oRow In oTable.Rows
            strRowCube = oRow("CubeName").ToString
            oCube = oDB.Cubes.FindByName(strRowCube)

            If oCube Is Nothing Then
                Dts.Log("Did not find cube: " & strRowCube, 0, dataBytes) 'You need to setup custom package logging to see this
                Continue For
            End If

            strRowMeasureGroup = oRow("MeasureGroup").ToString
            oMeasureGroup = oCube.MeasureGroups.FindByName(strRowMeasureGroup)

            If oMeasureGroup Is Nothing Then
                Dts.Log("Did not find measure group: " & strRowMeasureGroup, 0, dataBytes) 'You need to setup custom package logging to see this
                Continue For
            End If

            strRowPartition = oRow("Partition").ToString
            oPartition = oMeasureGroup.Partitions.FindByName(strRowPartition)

            'If the Partition doesn't exist in the cube, then capture create it
            If (oPartition Is Nothing) Then
                Dts.Log("Need to create partition: " & strRowPartition, 0, dataBytes) 'You need to setup custom package logging to see this
                oPartition = oMeasureGroup.Partitions.Add(strRowPartition)
                oPartition.StorageMode = StorageMode.Molap
                oPartition.Source = New QueryBinding(oDB.DataSources(0).ID, oRow("SQL").ToString)

                If oMeasureGroup.AggregationDesigns.Count > 0 Then ' 
                    oPartition.AggregationDesignID = oMeasureGroup.AggregationDesigns(0).ID ' Take first available aggregation design
                End If

                'Capture XML to add the Partition
                oPartition.Update(UpdateOptions.ExpandFull)
                Dts.Log("Partition created: " & strRowPartition, 0, dataBytes) 'You need to setup custom package logging to see this
            End If
        Next

        oServer.CaptureXml() = False
        '-------------------
        'STOP CAPTURING XML
        '-------------------


        'Execute the log that contains the XMLA commands and drop the Partitions
        oResults = oServer.ExecuteCaptureLog(True, False) 'Execute within transaction=True, Execute in parallel=False

        'Log the errors and warnings
        For Each oResult In oResults
            For Each oMessage In oResult.Messages
                If oMessage.GetType.Name = "XmlaError" Then
                    'Errors
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom package logging to see this
                    ExecutionSuccessfull = False 'Fail the Script Task
                Else
                    'Warnings
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom package logging to see this
                    ExecutionSuccessfull = True 'Don't fail the Script Task
                End If
            Next oMessage
        Next oResult
        ' -------------------------------------------------------------------------------------------------------------------------

Done:
        oServer.Disconnect()

        If ExecutionSuccessfull Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If


    End Sub

End Class

4g) Get DB Partitions List

SELECT CubeName,MeasureGroup, Partition, SQL, NeedsReProcessing 
FROM config.SSASPartitions 
ORDER BY 1,2,3

4h) Process Newly Created Partitions

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Help, press F1.

    Public Sub Main()
        '
        '------------------------------------------------------------------------
        'THIS SCRIPT TASK WILL PROCESS ANY NEWLY CREATED PARTITIONS IN THE LIST 
        '------------------------------------------------------------------------
        Dim ExecutionSuccessfull As Boolean = True ' If true, package executed without errors

        ' These objects capture execution results
        Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
        Dim oResult As Microsoft.AnalysisServices.XmlaResult
        Dim dataBytes(0) As Byte
        Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

        ' These objects capture partitions table columns
        Dim oOleDB As New OleDbDataAdapter
        Dim oTable As New DataTable
        Dim oRow As DataRow
        oOleDB.Fill(oTable, Dts.Variables("PartitionListInDW").Value)

        'Connect to SSAS Database
        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections("Cube")
        Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
        Dim sDatabase As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

        If oDB Is Nothing Then
            Dts.Log("Did not find expected database: " & sDatabase, 0, dataBytes) 'You need to setup custom package logging to see this
            ExecutionSuccessfull = False
            GoTo Done
        End If

        'Cube attributes
        Dim oCube As Microsoft.AnalysisServices.Cube
        Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
        Dim oPartition As Microsoft.AnalysisServices.Partition


        '-------------------
        'START CAPTURING XML
        '-------------------
        oServer.CaptureXml() = True
        'For each row in the table

        For Each oRow In oTable.Rows
            If LCase(oRow("NeedsReProcessing").ToString) = LCase("False") Then
                Continue For
            End If

            'Find the cube
            oCube = oDB.Cubes.FindByName(oRow("CubeName").ToString)
            If oCube Is Nothing Then
                Dts.Log("Did not find cube: " & oRow("CubeName").ToString, 0, dataBytes) 'You need to setup custom package logging to see this
                Continue For
            End If

            'Find the MeasureGroup in the cube
            oMeasureGroup = oCube.MeasureGroups.FindByName(oRow("MeasureGroup").ToString)
            If oMeasureGroup Is Nothing Then
                Dts.Log("Did not find measure group: " & oRow("MeasureGroup").ToString, 0, dataBytes) 'You need to setup custom package logging to see this
                Continue For
            End If

            'Find the Partition in the Measure Group
            oPartition = oMeasureGroup.Partitions.FindByName(oRow("Partition").ToString)
            If (oPartition Is Nothing) Then
                Dts.Log("Partition does not exists: " & oRow("Partition").ToString, 0, dataBytes) 'You need to setup custom package logging to see this
                Continue For
            End If

            'Capture XML to Process the Partition
            oPartition.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)
        Next

        ' If the Measure Group is linked, then process the entire Measure Group
        For Each oCube In oDB.Cubes
            For Each oMeasureGroup In oCube.MeasureGroups
                If oMeasureGroup.IsLinked Then
                    oMeasureGroup.Process(Microsoft.AnalysisServices.ProcessType.ProcessDefault)
                End If
            Next
        Next

        oServer.CaptureXml() = False
        '-------------------
        'STOP CAPTURING XML
        '-------------------

        'Execute the log that contains the XMLA commands and drop the Partitions
        oResults = oServer.ExecuteCaptureLog(True, True) 'Execute within transaction=True, Execute in parallel=True

        'Log the errors and warnings
        For Each oResult In oResults
            For Each oMessage In oResult.Messages
                If oMessage.GetType.Name = "XmlaError" Then
                    'Errors
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom package logging to see this
                    ExecutionSuccessfull = False 'Fail the Script Task
                Else
                    'Warnings
                    Dts.Log(oMessage.Description, 0, dataBytes) 'You need to setup custom package logging to see this
                    ExecutionSuccessfull = True 'Do not fail the Script Task
                End If
            Next oMessage
        Next oResult
        ' -------------------------------------------------------------------------------------------------------------------------

Done:
        oServer.Disconnect()

        If ExecutionSuccessfull Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If


    End Sub

End Class

4i) Update Process Status

Indicate that all the partitions have been processed.

UPDATE config.SSASPartitions 
SET NeedsReProcessing = 0
,LastProcessDate = GETDATE()
WHERE NeedsReProcessing = 1

Thanks again to Vidas Matelis. His scripts, here, were invaluable.

GP Historical MultiCurrency Receivables Query

This is really specific, but I had to hunt around to find out how to report on historical receivables in Great Plains. This will get the multicurrency receivables, but you can modify it to get all receivables.

I’ve written a stored procedure which goes after a cascade of other stored procedures created for the Great Plains reporting. I chose to use a stored procedure so I can pull the data as it is provided by the GP stored proc using the variables that that stored proc requires, and then adjust the data to meet the needs of my report.

USE [yourdatabase]
GO

/****** Object:  StoredProcedure [dbo].[x_report_HistoricalReceivables_MultiCurrency]    Script Date: 5/12/2014 1:24:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[x_report_HistoricalReceivables_MultiCurrency]
@EndDate date
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @RC int
DECLARE @I_dAgingDate datetime
DECLARE @I_cStartCustomerNumber char(15)
DECLARE @I_cEndCustomerNumber char(15)
DECLARE @I_cStartCustomerName char(65)
DECLARE @I_cEndCustomerName char(65)
DECLARE @I_cStartClassID char(15)
DECLARE @I_cEndClassID char(15)
DECLARE @I_cStartSalesPersonID char(15)
DECLARE @I_cEndSalesPersonID char(15)
DECLARE @I_cStartSalesTerritory char(15)
DECLARE @I_cEndSalesTerritory char(15)
DECLARE @I_cStartShortName char(15)
DECLARE @I_cEndShortName char(15)
DECLARE @I_cStartState char(5)
DECLARE @I_cEndState char(5)
DECLARE @I_cStartZipCode char(11)
DECLARE @I_cEndZipCode char(11)
DECLARE @I_cStartPhoneNumber char(21)
DECLARE @I_cEndPhoneNumber char(21)
DECLARE @I_cStartUserDefined char(15)
DECLARE @I_cEndUserDefined char(15)
DECLARE @I_tUsingDocumentDate tinyint
DECLARE @I_dStartDate datetime
DECLARE @I_dEndDate datetime
DECLARE @I_sIncludeBalanceTypes smallint
DECLARE @I_tExcludeNoActivity tinyint
DECLARE @I_tExcludeMultiCurrency tinyint
DECLARE @I_tExcludeZeroBalanceCustomer tinyint
DECLARE @I_tExcludeFullyPaidTrxs tinyint
DECLARE @I_tExcludeCreditBalance tinyint
DECLARE @I_tExcludeUnpostedAppldCrDocs tinyint
DECLARE @I_tConsolidateNAActivity tinyint

--TODO: Set parameter values here.
Set @I_dAgingDate=  @EndDate
Set @I_cStartCustomerNumber=''
Set @I_cEndCustomerNumber='ZZZZZZZZ'
Set @I_cStartCustomerName=''
Set @I_cEndCustomerName='ZZZZZZZZ'
Set @I_cStartClassID=''
Set @I_cEndClassID='ZZZZZZZZ'
Set @I_cStartSalesPersonID=''
Set @I_cEndSalesPersonID='ZZZZZZZZ'
Set @I_cStartSalesTerritory=''
Set @I_cEndSalesTerritory='ZZZZZZZZ'
Set @I_cStartShortName=''
Set @I_cEndShortName='ZZZZZZZZ'
Set @I_cStartState=''
Set @I_cEndState='ZZZZZZZZ'
Set @I_cStartZipCode=''
Set @I_cEndZipCode='ZZZZZZZZ'
Set @I_cStartPhoneNumber=''
Set @I_cEndPhoneNumber='ZZZZZZZZ'
Set @I_cStartUserDefined=''
Set @I_cEndUserDefined='ZZZZZZZZ'
Set @I_tUsingDocumentDate=0
Set @I_dStartDate='1/1/1900'
Set @I_dEndDate=  @EndDate
Set @I_sIncludeBalanceTypes=0
Set @I_tExcludeNoActivity=1
Set @I_tExcludeMultiCurrency=0
Set @I_tExcludeZeroBalanceCustomer=1
Set @I_tExcludeFullyPaidTrxs=1
Set @I_tExcludeCreditBalance=0
Set @I_tExcludeUnpostedAppldCrDocs=1
Set @I_tConsolidateNAActivity=0

CREATE TABLE #AgedReceivables
(
APPLY_AMOUNT NUMERIC(19,5),
AGING_AMOUNT NUMERIC(19,5),
CUSTNMBR char(15),
CUSTNAME char(100),
BALNCTYP int,
USERDEF1 char(50),
CNTCPRSN char(50),
PHONE1 char(50),
SLPRSNID char(50),
SALSTERR char(50),
PYMTRMID char(50),
CRLMTAMT NUMERIC(19,5),
CRLMTPER NUMERIC(19,5),
CRLMTPAM NUMERIC(19,5),
CRLMTTYP int,
CUSTCLAS char(50),
SHRTNAME char(50),
ZIP char(50),
STATE char(50),
CUDSCRIPTN char(50),
AGNGDATE datetime,
CHCUMNUM char(50),
DOCNUMBR char(50),
RMDTYPAL char(50),
DSCRIPTN char(50),
DCURNCYID char(50),
ORTRXAMT NUMERIC(19,5),
CURTRXAM NUMERIC(19,5),
AGNGBUKT char(50),
CASHAMNT NUMERIC(19,5),
COMDLRAM char(50),
SLSAMNT NUMERIC(19,5),
COSTAMNT NUMERIC(19,5),
FRTAMNT NUMERIC(19,5),
MISCAMNT NUMERIC(19,5),
TAXAMNT NUMERIC(19,5),
DISAVAMT NUMERIC(19,5),
DDISTKNAM NUMERIC(19,5),
DWROFAMNT NUMERIC(19,5),
TRXDSCRN char(50),
DOCABREV char(50),
CHEKNMBR char(50),
DOCDATE datetime,
DUEDATE datetime,
GLPOSTDT datetime,
DISCDATE datetime,
POSTDATE datetime,
DINVPDOF datetime,
DCURRNIDX char(50),
DXCHGRATE NUMERIC(19,5),
ORCASAMT NUMERIC(19,5),
ORSLSAMT NUMERIC(19,5),
ORCSTAMT NUMERIC(19,5),
ORDAVAMT NUMERIC(19,5),
ORFRTAMT NUMERIC(19,5),
ORMISCAMT NUMERIC(19,5),
ORTAXAMT NUMERIC(19,5),
ORCTRXAM NUMERIC(19,5),
ORORGTRX NUMERIC(19,5),
DORDISTKN NUMERIC(19,5),
DORWROFAM NUMERIC(19,5),
DDENXRATE NUMERIC(19,5),
DMCTRXSTT NUMERIC(19,5),
Aging_Period_Amount NUMERIC(19,5),
APFRDCNM char(50),
APFRDCTY char(50),
FROMCURR char(50),
APTODCNM char(50),
APTODCTY char(50),
APPTOAMT NUMERIC(19,5),
ACURNCYID char(50),
DATE1 datetime,
POSTED  char(50),
ADISTKNAM  NUMERIC(19,5),
AWROFAMNT NUMERIC(19,5),
PPSAMDED NUMERIC(19,5),
GSTDSAMT NUMERIC(19,5),
ACURRNIDX char(50),
AXCHGRATE  NUMERIC(19,5),
RLGANLOS NUMERIC(19,5),
ORAPTOAM NUMERIC(19,5),
AORDISTKN NUMERIC(19,5),
AORWROFAM NUMERIC(19,5),
ADENXRATE NUMERIC(19,5),
AMCTRXSTT NUMERIC(19,5),
)

INSERT INTO #AgedReceivables
EXECUTE @RC = [dbo].[seermHATBSRSWrapper] 
   @I_dAgingDate
  ,@I_cStartCustomerNumber
  ,@I_cEndCustomerNumber
  ,@I_cStartCustomerName
  ,@I_cEndCustomerName
  ,@I_cStartClassID
  ,@I_cEndClassID
  ,@I_cStartSalesPersonID
  ,@I_cEndSalesPersonID
  ,@I_cStartSalesTerritory
  ,@I_cEndSalesTerritory
  ,@I_cStartShortName
  ,@I_cEndShortName
  ,@I_cStartState
  ,@I_cEndState
  ,@I_cStartZipCode
  ,@I_cEndZipCode
  ,@I_cStartPhoneNumber
  ,@I_cEndPhoneNumber
  ,@I_cStartUserDefined
  ,@I_cEndUserDefined
  ,@I_tUsingDocumentDate
  ,@I_dStartDate
  ,@I_dEndDate
  ,@I_sIncludeBalanceTypes
  ,@I_tExcludeNoActivity
  ,@I_tExcludeMultiCurrency
  ,@I_tExcludeZeroBalanceCustomer
  ,@I_tExcludeFullyPaidTrxs
  ,@I_tExcludeCreditBalance
  ,@I_tExcludeUnpostedAppldCrDocs
  ,@I_tConsolidateNAActivity

SELECT CUSTNMBR, DCURNCYID as CURNCYID, SUM(ORCTRXAM) as ACCTBAL, SUM(CURTRXAM) AS CADEQUIV
FROM #AgedReceivables 
WHERE 
CURTRXAM <> 0 AND
CASE WHEN DCURNCYID = '' THEN 'CAD' ELSE DCURNCYID END <> 'CAD' 
GROUP BY CUSTNMBR, DCURNCYID

DROP TABLE  #AgedReceivables
END

GO

Thanks to Mark Polino for pointing the way in his blog post – http://mpolino.com/gp/weekly-dynamic-ar-hatb-via-SQL/

Generate a Series of Months (or Numbers) in SQL

This little trick has helped me keep SQL code clean when generating data. There are many times where I need to generate a series of months in a sql query. Very often it is needed in order to join two incomplete sets of data by date, or to cross join a set of data to spread it across a series of months. This is a very simple solution which can be adapted for many other needs.

You can start by using a ‘with’ statement to generate the values you need. In this example I will generate the numbers 1 to 12 to represent 12 months.

;with Months AS
(SELECT 1 AS MonthNum
UNION ALL
SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12)
SELECT * FROM Months

This is the result set:

Let’s say you have a rates table with one record per year for each Hour Type ID. You want to duplicate this information across months. You start with a transaction like this:

and end up with a dataset like this:

You can do this with dates as well. For example:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate=DATEADD(month,-6, CONVERT(CHAR(10),GETDATE(),121))
SET @EndDate = GETDATE()

;with AllDates AS
(
SELECT @StartDate AS Dates
UNION ALL
SELECT DateAdd(month,1,Dates)
FROM AllDates
WHERE 
Dates<=@EndDate
)

SELECT * FROM AllDates

will result in a series of dates like this:

I hope this helps you.

How to Access a Specific RDP Session

I have run into this issues several times before, where I am VPN’d into a client and have an RDP session open, and then my VPN gets cut off. When I sign back in sometimes I am unable to connect to the existing open RDP session. Today it was mission critical as I was almost at the end of a very long running process and I had to connect to the same session. After much searching I came up with the solution. It is a combination of two tactics that does the trick.

1. Use a command line prompt to connect to the console without disconnecting your existing session. In the command line on your desktop type the following:

mstsc –v:<yourservername> /F -console

2. Open up Task Manager on your server in this new session, right click on the session you want to reconnect to, and click connect.

Super easy once you know how!

I found the following two articles helpful in the writing of this post.

http://windows.microsoft.com/en-CA/windows-vista/Use-command-line-parameters-with-Remote-Desktop-Connection

http://www.coolitguys.com/windows/cannot-connect-to-existing-rdp-session/

Batch GeoCoding in SSIS

I’ve had to do some geocoding of addresses. It used to be free to process a lot of addresses, but now that this is a more mainstream activity you generally have to purchase an Enterprise License. This can cost upwards of $10,000. If you have a very small number of addresses you can use a free service like Bing Maps, which currently allows you to process 50 records at a time, and run 5 jobs per 24 hours. This means a maximum of 250 per day. If you want to check out how this works, here’s how.

Install the free Codeplex SSIS Batch Geocoder into your Visual Studio environment

http://ssisbatchgeocoder.codeplex.com/releases/view/66866

Get a Bing Maps Key

You need to have or set up a Microsoft account to get a key. Follow the instructions to get a Basic Key. If you have a large number of addresses you will want to look into purchasing an Enterprise License.

http://www.microsoft.com/maps/

Set up an SSIS project

Create a Visual Studio SSIS project. If you can’t see the toolbox, in the Visual Studio Menu choose View – Other Windows – SSIS Toolbox. If you can’t see the Variables window, in the Visual Studio Menu choose View – Other Windows – Variables.

Add these two string variables to your project: BingMapsKey and JobDescription. Set the value of the BingMapsKey variable to the key you obtain from Bing Maps. Set the JobDescription variable to “Geolookup from address”

Add a Data Flow Task. Open up the Data Flow Task. On the Data Flow Task add these four items:

Configure the OLE DB Source to connect to the table that contains the addresses.

Configure the Derived Columns like this:

Configure the SSIS Batch Geocoder like this, using the two variables as the Bing Maps Key and the Job Description, and mapping any other relevant columns from your data.

Configure the OLE DB Destination to wherever you want your output to reside. Choose whichever output fields meet your needs. The latitude and longitude output can be stored in a field of SQL data type “Geography”.

In preparing this post I found these articles helpful.

http://ssisbatchgeocoder.codeplex.com/documentation

http://blog.programmableweb.com/2012/06/21/7-free-geocoding-apis-google-bing-yahoo-and-mapquest/

How to use a Delimited String in a Multi-valued Parameter

I’ve seen this issue a lot lately. There is a need to feed a comma delimited string of values into a multi-value parameter in an SSRS report. There is a simple way to do this.

SET UP YOUR MAIN REPORT QUERY

Your main report query should be set up to expect a string of values in the parameter. For example:

SELECT Product_ID
      ,Item_No
      ,Item_Description
  FROM DIM_Product
  WHERE Item_No IN (@ITEM)

SET UP THE PARAMETER

The parameter should be set up as text, but NOT allow multiple values.

You can set this up as you wish to meet your purpose, but for demonstration I will set up two groups of comma delimited strings as available values.

I have set up a second data set to feed these default values to the parameter.

SELECT '11000B,2200,17000' AS ITEMNMBR

SET UP THE PARAMETER ON THE MAIN REPORT DATASET

Now comes the magic. On the Dataset Properties for your main report dataset, on the Parameters tab, edit the expression for the Parameter Value.

Write this in the parameter expression: =split(Parameters!Item.Value,",")

This will take the comma delimited string as input, split it into individual values and it will get used in the IN clause of the main query that we set up at the beginning of this post.

WHERE Item_No IN (@ITEM)

Viola, the report filters on a string of values.