Using SSIS to Consume Data from NetSuite's OpenAir SOAP API

     Lately, I've been working on incorporating data from NetSuite's resource management utility (Open Air) into a data warehouse, and thought I'd write a post on how to go about doing this using their SOAP API. 

    When going through their API documentation there are a number of commands available you can call to view and manipulate data. The only one we are concerned with here is the "Read" command, since all we want to do is view the data. Now we need to decide what piece of data we want to look at. There are a number of data types you can choose from, for this example we want to retrieve a list of Customer attributes. So we need to specify "Customer" as our type. 

     After choosing a data type, we need to further specify what data we want by selecting a "Method". These can select all records returned, records that equal a certain value/condition, not equal to a certain value/condition, etc. For this example, we are just going to pull back all the records. In order to limit these records we want to set a filter. These can be business oriented like approved-timesheets or rejected-timesheets or date range based such as newer-than, older-than, date-equal-to or date-not-equal-to. For this example I'm going to use a date filter and use newer-than. This will allow me to get records that have been updated since a certain date. We can do this by applying this filter to a "Field". Every record that is returned from the web service has an updated field, so we can choose this as our filter field. The date we want to specify can be added to a date tag.

     Finally, we need to set up our limit. Limit tells the API how many records to return at a time, with a maximum of 1000 records for any request. This makes things a little complicated. However; we can specify an offset for this which will allow us to get all the records we need. For example's sake, say we have 2050 customers in OpenAir. Our first request will have an offset and limit specified as such: 0,1000. This will return the first 1000 customers. The next request will be 1000,1000 this will return the next 1000 and finally 2000,1000, which will return to us the remaining 50 customers. So each time we make the call we move the offset window up 1000, since this is the maximum allowed.

     The client, company key, user and password will all be specific to the user and will be provided by NetSuite. You can test this out in your browser using their XML API, if you're using Chrome, using the Simple REST Client addon:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><request API_version="1.0 " client="Company1" client_ver="1.1" namespace="default"  key="providedkey"><Auth><Login><company>Company1</company><user>user1</user><password>password1</password></Login></Auth><Read type="Customer" filter="newer-than" field="updated" method="all" limit="0,1000" ><Date><year>2006</year><month>11</month><day>12</day></Date></Read><Time/></request>

Figure 1. OpenAir XML API

     Now that we are sure the data we are sending to the web service is well formatted, we can start building our package that will consume the feed. Our package is going to have a single data flow that is going to look like this:

Figure 2. Data Flow

Ignore the Union All component, that's just there so I can add a data viewer, normally this would go to a data destination.

     First, we need to take all of the tags were passing to the web service and make variables for them. This will allow us to change configurations when necessary, without having to touch any of the underlying C# code:

Figure 3. Variables

You'll notice that all of the variables correspond to values used in the XML block. The only one that doesn't is timeout. This is the time in milliseconds the package will wait for a response from the web service before timing out, I defaulted it to 1 minute.

    Now that we have created our variables, we need to create our Script Component source that will call the web service and get us our data. Drag a script component onto the design surface. When prompted, select source:

Figure 4. Script Component Type

On the script screen, make sure to pick variables:  User::apiVersion,User::client,User::company,User::dtBeginRange,User::field,User::filter,User::key,User::limit,User::method,User::password,User::timeout,User::type,User::user as ReadOnlyVariables:

Figure 5. Script Component Script Screen

We then have to configure our output buffer, so click on Inputs and Outputs to pull this up. On this screen we define what the fields for Customer are going to look like. A good reference for data types can be found here. I defaulted all the fields to DT_STR data types, since they all come back as strings anyways, except for Notes which I made DT_TEXT. But ultimately the data types are up to you, just keep in mind you will have to convert them from strings in code before sending the values to the output buffer:

Figure 6. Script Component Inputs and Outputs Screen

     Now that we're done entering all of our Customer fields, we can start coding. Go back to the Script screen and click on the Edit Script button. Before we can start coding we need to import OpenAir's WSDL to our project, there is a page here provided by Microsoft on how to do this but I'll walk through it anyways. In the script component environment click on Project -->Add Service Reference:

Figure 7. Project Menu

This will bring up the Add Service Reference window, click on the Advanced button:

Figure 8. Add Service Reference Window

This will bring up the Service Reference Settings window, click on the Add Web Reference button:

Figure 9. Service Reference Settings Window

This will bring up the Add Web Reference window where we can (finally) specify our reference. in the URL text box type http://www.openair.com/wsdl.pl?wsdl then click the arrow button to the right of the text box. This will activate the Web Reference name text box. Type "OA" in this box, its what is preferred according to the API documentation, then click the Add Reference button:

Figure 10. Add Web Reference Window

You should now see this added to your project under Web References:

Figure 11. Project Files

Now we can use this in our code in main.cs:

/* Microsoft SQL Server Integration Services Script Component

*  Write scripts using Microsoft Visual C# 2012.

*  ScriptMain is the entry point class of the script.*/

#region Namespaces

using System;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using Microsoft.SqlServer.Dts.Runtime;

using System.Text;

using SC_e49c934c98564fa6be517b883cd15def.OA;

using OA = SC_e49c934c98564fa6be517b883cd15def.OA;

using System.Web.Services.Protocols;

#endregion

#region Class

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    #region Methods

    /// <summary>Outputs records to the output buffer</summary>

    public override void CreateNewOutputRows()

    {

        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

        //DataFlow component interface

        //Stores the current offset value

        int offSet = 0;

        //Stores the results returned from the web service

        ReadResult[] results;

        //Auth error flag

        bool authError = false;

        //Service Session Handler

        OAirServiceHandlerService _svc=null;

      

        try

        {

            //Creates a read request object to send to the web service

            ReadRequest rr = GetReadRequest();

            //Login and get session

            _svc = GetServiceHandler(compMetadata, ref authError);

            //If we had an authentication error, we end

            if (authError == false)

            {

                do

                {

                    //Iterate offSet in read request

                    rr.attributes[2].value = String.Format("{0}, {1} [{2}]", offSet, Variables.limit, DateTime.Now);

                    InfoComponent(String.Format("Loading results {0}, {1} [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);

                    results = _svc.read(new ReadRequest[] { rr });

                    //If we have an error reading records, output errors

                    if (results != null && results.Length > 0 && results[0].errors != null)

                    {

                        foreach (oaError err in results[0].errors)

                        {

                            FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, GetReadErrorMessage(Convert.ToInt32(err.code)), DateTime.Now), compMetadata);

                        }

                        break;

                    }

                    //If we have no records returned

                    if (results == null)

                    {

                        Output0Buffer.SetEndOfRowset();

                        Logout(_svc, compMetadata);

                        throw new NullReferenceException();

                    }

                    else

                    {

                        InfoComponent(String.Format("Adding results {0}, {1} to the output buffer [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);

                        foreach (oaCustomer cust in results[0].objects)

                        {

                            Output0Buffer.AddRow();

                            Output0Buffer.ID = cust.id;

                            Output0Buffer.ADDRSALUTATION = cust.addr_salutation;

                            Output0Buffer.ADDRMOBILE = cust.addr_mobile;

                            Output0Buffer.ADDRSTATE = cust.addr_state;

                            Output0Buffer.ADDREMAIL = cust.addr_email;

                            Output0Buffer.ADDRADDR2 = cust.addr_addr2;

                            Output0Buffer.ADDRCITY = cust.addr_city;

                            Output0Buffer.ADDRFAX = cust.addr_fax;

                            Output0Buffer.ADDRADDR1 = cust.addr_addr1;

                            Output0Buffer.ADDRMIDDLE = cust.addr_middle;

                            Output0Buffer.ADDRCOUNTRY = cust.addr_country;

                            Output0Buffer.ADDRFIRST = cust.addr_first;

                            Output0Buffer.ADDRLAST = cust.addr_last;

                            Output0Buffer.ADDRPHONE = cust.addr_phone;

                            Output0Buffer.ADDRADDR4 = cust.addr_addr4;

                            Output0Buffer.ADDRZIP = cust.addr_zip;

                            Output0Buffer.ADDRADDR3 = cust.addr_addr3;

                            Output0Buffer.INVOICELAYOUTID = cust.invoice_layoutid;

                            Output0Buffer.RATE = cust.rate;

                            Output0Buffer.BUSTYPEID = cust.bus_typeid;

                            Output0Buffer.CODE = cust.code;

                            Output0Buffer.NAME = cust.name;

                            Output0Buffer.TBAPPROVER = cust.tb_approver;

                            Output0Buffer.TERRITORYID = cust.territoryid;

                            Output0Buffer.HIERARCHYNODEIDS = cust.hierarchy_node_ids;

                            Output0Buffer.HEARABOUTID = cust.hear_aboutid;

                            Output0Buffer.STATEMENTS = cust.statements;

                            Output0Buffer.COMPANYSIZEID = cust.company_sizeid;

                            Output0Buffer.WEB = cust.web;

                            Output0Buffer.CURRENCY = cust.currency;

                            Output0Buffer.COSTCENTERID = cust.cost_centerid;

                            Output0Buffer.CONTACTADDRSALUTATION = cust.contact_addr_salutation;

                            Output0Buffer.CONTACTADDRMOBILE = cust.contact_addr_mobile;

                            Output0Buffer.CONTACTADDRSTATE = cust.contact_addr_state;

                            Output0Buffer.CONTACTADDREMAIL = cust.contact_addr_email;

                            Output0Buffer.CONTACTADDRADDR2 = cust.contact_addr_addr2;

                            Output0Buffer.CONTACTADDRCITY = cust.contact_addr_city;

                            Output0Buffer.CONTACTADDRFAX = cust.contact_addr_fax;

                            Output0Buffer.CONTACTADDRADDR1 = cust.contact_addr_addr1;

                            Output0Buffer.CONTACTADDRMIDDLE = cust.contact_addr_middle;

                            Output0Buffer.CONTACTADDRCOUNTRY = cust.contact_addr_country;

                            Output0Buffer.CONTACTADDRFIRST = cust.contact_addr_first;

                            Output0Buffer.CONTACTADDRLAST = cust.contact_addr_last;

                            Output0Buffer.CONTACTADDRPHONE = cust.contact_addr_phone;

                            Output0Buffer.CONTACTADDRADDR4 = cust.contact_addr_addr4;

                            Output0Buffer.CONTACTADDRZIP = cust.contact_addr_zip;

                            Output0Buffer.CONTACTADDRADDR3 = cust.contact_addr_addr3;

                            Output0Buffer.BILLINGADDRSALUTATION = cust.billing_addr_salutation;

                            Output0Buffer.BILLINGADDRMOBILE = cust.billing_addr_mobile;

                            Output0Buffer.BILLINGADDRSTATE = cust.billing_addr_state;

                            Output0Buffer.BILLINGADDREMAIL = cust.billing_addr_email;

                            Output0Buffer.BILLINGADDRADDR2 = cust.billing_addr_addr2;

                            Output0Buffer.BILLINGADDRCITY = cust.billing_addr_city;

                            Output0Buffer.BILLINGADDRFAX = cust.billing_addr_fax;

                            Output0Buffer.BILLINGADDRADDR1 = cust.billing_addr_addr1;

                            Output0Buffer.BILLINGADDRMIDDLE = cust.billing_addr_middle;

                            Output0Buffer.BILLINGADDRCOUNTRY = cust.billing_addr_country;

                            Output0Buffer.BILLINGADDRFIRST = cust.billing_addr_first;

                            Output0Buffer.BILLINGADDRLAST = cust.billing_addr_last;

                            Output0Buffer.BILLINGADDRPHONE = cust.billing_addr_phone;

                            Output0Buffer.BILLINGADDRADDR4 = cust.billing_addr_addr4;

                            Output0Buffer.BILLINGADDRZIP = cust.billing_addr_zip;

                            Output0Buffer.BILLINGADDRADDR3 = cust.billing_addr_addr3;

                            Output0Buffer.BILLINGCONTACTID = cust.billing_contact_id;

                            Output0Buffer.NOTES.AddBlobData(Encoding.ASCII.GetBytes(cust.notes));

                            Output0Buffer.TBAPPROVALPROCESS = cust.tb_approvalprocess;

                            Output0Buffer.PRIMARYCONTACTID = cust.primary_contactid;

                            Output0Buffer.FILTERSETIDS = cust.filterset_ids;

                            Output0Buffer.ACTIVE = cust.active;

                            Output0Buffer.EXTERNALID = cust.externalid;

                            Output0Buffer.INVOICEPREFIX = cust.invoice_prefix;

                            Output0Buffer.TYPE = cust.type;

                            Output0Buffer.USERID = cust.userid;

                            Output0Buffer.TERMS = cust.terms;

                            Output0Buffer.CREATED = cust.created;

                            Output0Buffer.INVOICETEXT = cust.invoice_text;

                            Output0Buffer.COMPANY = cust.company;

                            Output0Buffer.UPDATED = cust.updated;

                            Output0Buffer.SHIPPINGCONTACTID = cust.shipping_contactid;

                            Output0Buffer.SOLDTOCONTACTID = cust.sold_to_contactid;

                            Output0Buffer.BILLINGCODE = cust.billing_code;

                            Output0Buffer.CREATETIME = cust.createtime;

                            Output0Buffer.TAINCLUDE = cust.ta_include;

                            Output0Buffer.TEINCLUDE = cust.te_include;

                            Output0Buffer.UPDATETIME = cust.updatetime;

                        }

                    }

                    //Fetch next 1000 records

                    offSet += Convert.ToInt32(Variables.limit);

                    //Keep loop going while we have records and the records returned are = Variables.limit

                } while (results[0].objects != null && results[0].objects.Length == Convert.ToInt32(Variables.limit));

            }

            else

            {

                Output0Buffer.EndOfRowset();

            }

   

        }

        catch (NullReferenceException)

        {

            if (offSet == 0)

            {

                InfoComponent(String.Format("No records were returned from webservice that were newer than {0} [{1}]",  Variables.dtBeginRange.ToString("MM/dd/yyyy"),DateTime.Now), compMetadata);

            }

        

        }

        catch (SoapException se)

        {

            Output0Buffer.EndOfRowset();

            authError = true;

            FailComponent(se.ToString(), compMetadata);

        }

        catch (Exception e)

        {

            Output0Buffer.EndOfRowset();

            FailComponent(e.ToString(), compMetadata);

        }

        //Log out of web service

        Finished(compMetadata);

        Logout(_svc, compMetadata);

    }

    /// <summary>

    /// Returns a summary of an read error from the web service, since they are not provided

    /// </summary>

    /// <param name="status">The read status code</param>

    /// <returns>The message associated with the status code</returns>

    private string GetReadErrorMessage(int status)

    {

        string msg = "";

        switch (status)

        {

            case 601:

                msg = "Invalid id/code";

                break;

            case 602:

                msg = "Invalid field";

                break;

            case 603:

                msg = "Invalid type or method";

                break;

            case 604:

                msg = "Attachment size exceeds space available";

                break;

            case 605:

                msg = "Limit clause must be specified and be less than the account limit for output data";

                break;

        }

        return msg;

    }

    /// <summary>

    /// Outputs a completed processing message

    /// </summary>

    /// <param name="compMetadata">The dataflow interface</param>

    private void Finished(IDTSComponentMetaData100 compMetadata)

    {

        InfoComponent(String.Format("Retrieving records completed [{0}]", DateTime.Now), compMetadata);

    }

    /// <summary>

    /// Logs out of OpenAir

    /// </summary>

    /// <param name="svc">The OAirServiceHandlerService object that contains our session </param>

    /// <param name="compMetadata">The dataflow interface</param>

    private void Logout(OAirServiceHandlerService svc, IDTSComponentMetaData100 compMetadata)

    {

        InfoComponent(String.Format("Logging out of Open Air [{0}]", DateTime.Now), compMetadata);

        if(svc!=null)

        {

        svc.logout();

        }

    }

    /// <summary>

    /// Returns the read request that were going to send to the web service, our parameters from variables are assigned here

    /// </summary>

    /// <returns>Returns the read request</returns>

    public ReadRequest GetReadRequest()

    {

        ReadRequest rr = new ReadRequest();

        //Set the datatype we want to return

        rr.type = Variables.type;

        //return only records that match search criteria

        rr.method = Variables.method;

        //Filter by date range and by the special not-exported flag

        OA.Attribute attrFilter = new OA.Attribute();

        attrFilter.name = "filter";

        attrFilter.value = Variables.filter;

        //Name of the field to apply date filter to

        OA.Attribute attrField = new OA.Attribute();

        attrField.name = "field";

        attrField.value = Variables.field;

        //The 0,1000 record limit

        OA.Attribute attrLimit = new OA.Attribute();

        attrLimit.name = "limit";

        // set newer-than filter date

        oaDate dateNewer = new oaDate();

        dateNewer.year = Variables.dtBeginRange.ToString("yyyy");

        dateNewer.month = Variables.dtBeginRange.ToString("MM");

        dateNewer.day = Variables.dtBeginRange.ToString("dd");

        rr.objects = new oaBase[] { dateNewer };

        rr.attributes = new OA.Attribute[] { attrFilter, attrField, attrLimit };

        return rr;

    }

    /// <summary>

    /// Returns the session service handler

    /// </summary>

    /// <param name="compMetadata">The dataflow interface</param>

    /// <returns>Service handler</returns>

    public OAirServiceHandlerService GetServiceHandler(IDTSComponentMetaData100 compMetadata, ref bool authError)

    {

        //Connect to web service

        OAirServiceHandlerService svc = new OAirServiceHandlerService();

        //Set timeout

        svc.Timeout = Variables.timeout;

        InfoComponent(String.Format("Logging into Open Air [{0}]", DateTime.Now), compMetadata);

    

            LoginResult loginResult = svc.login(GetLoginParams());

            // Create a new session header object

            // Add the session ID returned from the login

            SessionHeader header = new SessionHeader();

            header.sessionId = loginResult.sessionId;

            svc.SessionHeaderValue = header;

        return svc;

    }

    /// <summary>

    /// Returns login parameters

    /// </summary>

    /// <returns>An object that contains the parameters to login to the web service</returns>

    public LoginParams GetLoginParams()

    {

        // create LoginParam object

        LoginParams loginParams = new LoginParams();

        loginParams.api_namespace = "default";

        loginParams.api_key = Variables.key;

        loginParams.company = Variables.company;

        loginParams.user = Variables.user;

        loginParams.password = Variables.password;

        loginParams.client = Variables.client;

        loginParams.version = Variables.apiVersion;

        return loginParams;

    }

    /// <summary>Outputs an Error</summary>

    /// <param name="errorMsg">The error message to send to the UI</param>

    /// <param name="compMetadata">The dataflow interface</param>

    private void FailComponent(string errorMsg, IDTSComponentMetaData100 compMetadata)

    {

        bool fail = false;

        compMetadata.FireError(1, compMetadata.Name, errorMsg, "", 0, out fail);

    }

    /// <summary>Outputs a message if no data is returned /// </summary>

    /// <param name="infoMsg">The information message to send to the UI</param>

    /// <param name="compMetadata">The dataflow interface</param>

    private void InfoComponent(string infoMsg, IDTSComponentMetaData100 compMetadata)

    {

        bool fail = false;

        compMetadata.FireInformation(1, compMetadata.Name, infoMsg, "", 0, ref fail);

    }

    #endregion

}

#endregion

Before saving and building this code make sure to replace my project name in the code "SC_e49c934c98564fa6be517b883cd15def", with what ever the name of your project is, in the Namespaces region (there's only 2). Now lets step through and explain some of this code, a lot of it I got straight out of the API document. The first thing we do is override CreateNewOutputRows() method: public override void CreateNewOutputRows(). Then we instantiate our ReadRequest object with our query parameters ReadRequest rr = GetReadRequest(). Next, we login to the web service and set up our session: _svc = GetServiceHandler(compMetadata, ref bool authError) and LoginResult loginResult = svc.login(GetLoginParams()). Finally, we set our results object to the result set from the web service  results = _svc.read(new ReadRequest[] { rr }) and then loop through all the results and output to the output buffer: 

foreach (oaCustomer cust in results[0].objects)

                    {

                        Output0Buffer.AddRow();

                       ....

Notice that in this example we're using oaCustomer, if you're looking for another type you would select from the classes provided:

Figure 12. OpenAir Datatypes

Let's save, build and run this package and see what we get in our data viewer:

Figure 13. Data Viewer

Looking at our progress tab we can view the progress of our package in retrieving our data:

Figure 14. Progress Tab

We can also output authentication errors from the web service:

Figure 15. Progress Tab with Authentication Errors

As well as read errors:

Figure 16. Progress Tab with Read Errors

To output read errors, we call  FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, GetReadErrorMessage( Convert.ToInt32(err.code)), DateTime.Now), compMetadata);. It appears that err.text and err.comment are always blank. So I pulled the text of the errors from the API document. If these values ever start appearing in future versions of the API you can replace the code with this:

FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, err.text, DateTime.Now), compMetadata);

If our query returns no results, we display that as well:

Figure 17. Progress Tab with No Records Returned Message

Adding Min and Max Date Functionality to a Snapshot Fact Table

     When generating periodic snapshots of your data, analysts may want to view that data in ways that may be different than the time frame of capture. For example, you could take snapshots of your company's parts inventory several times a day, once a day, several times a month, once a month, once a quarter, etc. But some analysts may want to report on this data by day, by month, by quarter, by year and by either the state of the inventory by the beginning of the time period or by the end of that period. How do we model this data to allow for this kind of analysis?

     For this example, let's expand upon the company parts and imagine we're dealing with a piece of data that looks like the following and the snapshot schedule is daily:

Figure 1. Source System Inventory Schema

This schema represents the parts inventory quantities, grouped by departments, for a company.  Whenever the data warehouse snapshots the data from this schema a date and time of the snapshot is captured so we can deduce what the inventory state was at that time. 

     In order to fulfill the requirement to report on these snapshots on different time groups, we need a way to categorize this data as such. Now with every data warehouse there will be a date dimension at the level of granularity of the day, in which we could place min/max day/month/quarter/year flags that can help us with this problem. But as you may soon discover, these flags would only pertain to inventory exclusively and if you wanted to use this functionality with other snapshots you would have to add flags specific to that piece of data as well. In addition to this, we may not run this table ETL every day, or if we have an ETL job failure we may miss a day's snapshot. Thus a failure on Sept. 30 means that the Sept 29th snapshot is the maximum one captured for that month. With all of these scenarios, this solution would become untenable very fast. The best way to attack this problem, and provide a re-usable solution, would be to separate these flags out into their own junk dimension. 

     First we need to create this dimension, then determine how we're going to integrate it with the ETL of the snapshot fact table. Our goal is a table that looks like this:

Figure 2. Junk Dimension with Time Flags

-->Create dimension to hold our date categories

CREATE  TABLE  DIM_TIME_FLAG(

    FLAG_KEY        int        NOT NULL,

    MIN_MONTH_FLG   char(1)    NULL,

    MAX_MONTH_FLG   char(1)    NULL,

    MIN_QUARTER_FLG char(1)    NULL,

    MAX_QUARTER_FLG char(1)    NULL,

    MIN_YEAR_FLG    char(1)    NULL,

    MAX_YEAR_FLG    char(1)    NULL,

    CONSTRAINT PK_DIM_TIME_FLAG PRIMARY KEY CLUSTERED (FLAG_KEY)

)

 -->Hold our Y/N enum

DECLARE @enum TABLE

  (

       field char(1)

  )

INSERT INTO @enum(field)values('Y'),('N')

-->Temp table to contain cartesian of Y/N for all fields

DECLARE @final TABLE

 (

        flag_key int identity(1,1),

        field1   char(1),

        field2   char(1),

        field3   char(1),

        field4   char(1),

        field5   char(1),

        field6   char(1)

 )

-->Generate cartesian

INSERT INTO @final

SELECT

        a.field,

        B.field,

        c.field,

        d.field,

        e.field,

        f.field

FROM

        @enum a,

        @enum b,

        @enum c,

        @enum d,

        @enum e,

        @enum f

-->Populate DIM_TIME_FLAG

INSERT INTO DIM_TIME_FLAG

SELECT

       0,

       null,

       null,

       null,

       null,

       null,

       null

UNION

SELECT

       flag_key,

       field1,

       field2,

       field3,

       field4,

       field5,

       field6 

FROM

       @final

-->View Table Rows

SELECT

     FLAG_KEY,

     MIN_MONTH_FLG,

     MAX_MONTH_FLG,

     MIN_QUARTER_FLG,

     MAX_QUARTER_FLG,

     MIN_YEAR_FLG,

     MAX_YEAR_FLG   

FROM

     DIM_TIME_FLAG

We use this table to determine if the record in the snapshot fact is the first/last for the month recorded/quarter recorded/year recorded. The MIN/MAX flags will have values of Y/N and the reporting solution can filter on these flags when trying to report on the various times. For our daily snapshot of parts inventory, we could add this table to a data warehouse schema like this:

Figure 3. Parts Inventory Data Warehouse Schema

     Now, we need to figure out how to get DIM_TIME_FLAG integrated into this schema in our ETL plan. Key thing to keep in mind is that these flags will change as more and more snapshots are taken from the source system. Today's records that have MAX_MONTH_FLG=Y may need to be set to N the following day. After populating the other columns in the table you would have to execute this SQL statement to update the FLAG_KEY:

UPDATE A

SET

       A.FLAG_KEY=Z.FLAG_KEY

FROM

       FCT_PART_INVENTORY A

       INNER JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

       LEFT JOIN

(

SELECT

       MIN(B.FULL_DATE) AS MIN_MONTH

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY

       B.YEARMO

)MIN_MONTH ON B.FULL_DATE=MIN_MONTH.MIN_MONTH

       LEFT JOIN

(

SELECT

       MAX(B.FULL_DATE) AS MAX_MONTH

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY B.YEARMO

)MAX_MONTH ON B.FULL_DATE=MAX_MONTH.MAX_MONTH

       LEFT JOIN

(

SELECT

       MIN(B.FULL_DATE) AS MIN_QUARTER

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY

       CONVERT(VARCHAR,B.QUARTER)+CONVERT(VARCHAR,B.YEAR)

)MIN_QUARTER ON B.FULL_DATE=MIN_QUARTER.MIN_QUARTER

       LEFT JOIN

(

SELECT

       MAX(B.FULL_DATE) AS MAX_QUARTER

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY

       CONVERT(VARCHAR,B.QUARTER)+CONVERT(VARCHAR,B.YEAR)

)MAX_QUARTER ON B.FULL_DATE=MAX_QUARTER.MAX_QUARTER

       LEFT JOIN

(

SELECT

       MIN(B.FULL_DATE) AS MIN_YEAR

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY

       B.YEAR

)MIN_YEAR ON B.FULL_DATE=MIN_YEAR.MIN_YEAR

LEFT JOIN

(

SELECT

       MAX(B.FULL_DATE) AS MAX_YEAR

FROM

       FCT_PART_INVENTORY A

       INNER  JOIN DIM_DAY B ON A.DAY_KEY=B.DAY_KEY

GROUP BY B.YEAR

)MAX_YEAR ON B.FULL_DATE=MAX_YEAR.MAX_YEAR

LEFT JOIN DIM_TIME_FLAG Z

ON CASE WHEN MIN_MONTH.MIN_MONTH IS NULL THEN 'N' ELSE 'Y' END =Z.MIN_MONTH_FLG

AND CASE WHEN MAX_MONTH.MAX_MONTH IS NULL THEN 'N' ELSE 'Y' END =Z.MAX_MONTH_FLG

AND CASE WHEN MIN_QUARTER.MIN_QUARTER IS NULL THEN 'N' ELSE 'Y' END =Z.MIN_QUARTER_FLG

AND CASE WHEN MAX_QUARTER.MAX_QUARTER IS NULL THEN 'N' ELSE 'Y' END =Z.MAX_QUARTER_FLG

AND CASE WHEN MIN_YEAR.MIN_YEAR IS NULL THEN 'N' ELSE 'Y' END =Z.MIN_YEAR_FLG

AND CASE WHEN MAX_YEAR.MAX_YEAR IS NULL THEN 'N' ELSE 'Y' END =Z.MAX_YEAR_FLG

     With this table now integrated into this schema, we can not only use it at the level of day, but now month, quarter and year all using the same fact. For example, if we wanted to view data across days we can ignore the DIM_TIME_FLAG and simply filter our query across DIM_DAY and report on something that looks like this:

Figure 4. Daily Parts Inventory

     If we need to report on trending across months we could query the exact same snapshot fact, but this time join to DIM_TIME_FLAG where MIN_MONTH_FLG or MAX_MONTH_FLG=Y (depending of if you want to trend over months based on what the inventory looked like at the beginning of the month or end of the month). This would produce a report that would look something like this:

Figure 5. Monthly Parts Inventory

     The same could be done by quarter, as well, using the quarter flags:

Figure 6. Quarterly Parts Inventory

     And finally by year:

Figure 7. Yearly Parts Inventory

     This design can come in very handy, especially when building dashboards and executives want to be able to trend across the different time frames. It also negates the need to create separate snapshot tables that capture this data for each of these time frames(i.e. a snapshot for months, a snapshot for quarters, etc.).