Getting data from the cloud using SSIS

So much of our source data is now in the cloud. We need to continually extract it for our Business Intelligence solutions. Unfortunately, these cloud providers tend not to offer database connections for extracting data. However, it's not too difficult, using SSIS, to script an object as a source for an SSIS Data Flow.
There are some challenges extracting cloud data

• Every cloud provider has it's own way of authenticating (certificates, userid/password, token in https header etc). 
• Each provider has it's own way of paging through or filtering data.
• Data types are not guaranteed.
• Column names (attributes) are self defining

However, using the WebClient object, we can write a little code and get the JSON record passed back to the SSIS data flow. JSON is the most popular data structure, which can be handled natively within SQL Server with SQL JSON functions (IsJSON, JSON_Value, JSON_Query).

What I have found fruitful, is to break the http response into data records that go straight into a staging database. No data typing at this stage. The staging database can be queried with IsJSON() and JSON_Value() to populate the data warehouse. Only a small amount of code is required to perform the conversion of JSON to datatyped columns ready for loading. There is no need for the added complexity and fragility of extracting to flat files.

Here is some very simple vb code to download records that uses the https header to authenticate. "{""id"":" identifies the start of a new record for this provider. Note, the https url will typically be parameterised to include filters etc from SSIS control flow. MyOutputBuffer is the name of the output defined on the script source object (see below). The actual name is "MyOutput", "Buffer" is added by SSIS. In this case, I have defined one column in the output called JSONRecord as a string with length of 8000.

So, while it is not as simple as a database connection, it is not complicated and in my experience it is reliable. As always, I encourage defensive programming, so that if a connection or task is broken/unavailable, at any time, the next time the job starts it will automatically continue from where it left off.

    Public Overrides Sub CreateNewOutputRows()

        Using client2 As New Net.WebClient

            client2.Headers.Add("Authorization: bla bla bla")

            client2.Headers.Add("Content-Type: application/json")

            sInputBuffer = client2.DownloadString("https://myCloudProvider.com")

        End Using

        Dim sInputBuffer As String

        Dim sJSONRecord As String

        Dim iEnd As Int32

        Dim iStart As Int32

        iStart = InStr(sInputBuffer, "{""id"":")

        While iStart > 0

            iEnd = InStr(20, sInputBuffer, "{""id"":") - 1

            If iEnd > 0 Then

                sJSONRecord = Mid(sInputBuffer, iStart, iEnd - iStart)

                sInputBuffer = Mid(sInputBuffer, iEnd, 999999)

            Else

                sJSONRecord = Mid(sInputBuffer, iStart, 9999)

                sInputBuffer = ""

            End If

            With MyOutputBuffer

                .AddRow()

                .JSONRecord= sJSONRecord

            End With

            iStart = InStr(sInputBuffer, "{""id"":")

        End While

    End Sub

The example below has four entities (tables) extracted from the cloud. The fourth one is iterative as it must call for a set of records for each of another entity.

AWS Redshift versus SQL Server cube

Here is a straight out performance benchmark between AWS' Redshift, SQL Server cube, and SQL Server columnstore indexing. If you are choosing between these (and potentially other) platforms, this will provide some interesting reading. 
Essentially, the Microsoft cube was 97% faster than Redshift.

Note, I tried to make the test as independent and representative of a retail enterprise's query load as possible. The underlying fact tables contained 100 million, 430 million and 600 million rows. The queries ranged from the banal to common calcs such as "year to date" and "stock days on hand".

If you are serious about making the right decision for your organisation, you might set up a similar test with your own data and popular queries. 

Here is the full report:
Data Platform Performance Paper

This benchmark has been updated to include SQL Tabular cube performance. Here is the updated paper.

Multidimensional and Tabular Compared

Microsoft's SSAS offers a choice of technology for OLAP cubes - the original Multidimensional, and the newer, Tabular. I compared them back in 2012 http://richardlees.blogspot.com.au/2012/05/sql-server-2012-tabular-versus.html. It is now safe to assume now that Tabular is the strategic technology. Not officially strategic, but with the dearth of Multidimensional enhancements since 2012 and Tabular recently becoming the default install, it is clear to see where Microsoft is heading.

It has been pleasing to see enhancements to Tabular in each SQL release, which has reduced the need for Multidimensional. However, there are still a few features that Tabular does not support, necessitating new developments with Multidimensional. The standout feature that Tabular does not support is composite primary keys. Ralph Kymball, needs to take some responsibility. His Star Schema modeling methodology employs single column surrogate keys for all dimension tables. Kymball has advanced data modeling a great deal, but I think it's wrong to blindly generate surrogate keys when natural primary keys exist. Kymball methodology requires fact and dimension tables to be physically generated from underlying normalized tables. This involves a lot of work, reconciliation and performance cost, when the underlying tables could be used directly, with their composite primary keys.

The really sad fact is SSAS documentation, comparing Multidimensional and Tabular, does not even list composite keys as a feature worth comparing. It's as if, no one would require a cube technology supporting composite primary keys. See https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas

Since SQL 2012, I have chosen Tabular where possible, but it has been on rare occasions that Tabular could succeed. Mostly due to Tabular not supporting composite keys, and my unwillingness to generate redundant star schemas. Why would you generate copies of fact and dimension tables, just to build surrogate keys? Surrogate keys have no meaning. Business keys have meaning, and are typically part of the table already. Also, when you generate a redundant star schema, you need to continually append (or rewrite) to these tables as your DW data arrives. And, you should continue to reconcile these generated tables with the source tables. One might argue that the resultant star schema is more efficient than a star schema with composite keys, but that argument is marginal and rarely overcomes the increased cost and lag of building star schemas. And there can be significant performance loss in flattening (denormalising) the snowflake dimensions.

Even something simple like a Type 2 dimension already has a perfectly good natural key consisting of the source system's business key (eg ClientId) and EffectiveFromDate. So, why not just add EffectiveFromDate_Client to the fact table? A complete dimension and fact table rewrite is unnecessary!

I'm hoping that data warehouse developers see the sense of natural composite keys and that Tabular supports them in the near future. In the meantime, I will only be creating Tabular models on the rare occasions where composite keys are not part of the data structures.

If you would like to see Tabular support composite keys, please vote on this connect item. https://connect.microsoft.com/SQLServer/feedback/details/3144082

For a performance comparison of Tabular versus Multidimensional see https://richardlees.blogspot.com/2019/08/aws-redshift-performance-versus-sql.html

Query Plans and Parameter Sniffing

It's not uncommon (and often quite annoying) for an SQL query to "flip". This is where the optimiser decides to use a different query plan even though the query code has not changed. This could be due to many things, but most commonly changes in statistics (for example, the table is now so large that an index is preferable to a table scan) but it could also be due to "parameter sniffing". This is where the optimiser "sniffs" the parameter values for the execution invoking the compile and uses these values (along with data statistics) to determine an appropriate query plan. Since query plans are cached, this can mean that the first execution of a procedure will generate a plan that is inappropriate for subsequent executions with different parameters. I don't want to cause anxiety - it is generally a good thing that the optimiser dynamically takes parameters, data statistics, physical resources etc into account in determining the best query plan. However, consider a procedure that has two parameters, @AccountNumber and @AccountType. When the procedure is first called, it may use @AccountNumber and @AccountType values to determine appropriate query plans. If AccountType='X' is very rare in the database, the optimiser might, quite rightly, choose a query plan using an index on AccountType. However, this index would be inappropriate when @AccountType='A', which is the value for 99 % of the data records. This is problematic, as the cached query plan could be favouring either parameter value, randomly determined by the "first" execution. Here are some a few techniques to help with this issue.

1. Keep the base procedure, but have it call one of two other procedures depending on @AccountType value. Each of these sub procedures will be compiled on appropriate values.
2. Use WITH RECOMPILE on the procedure, so that it recompiles with every execution.
3. Declare a new parameter in the procedure @AccountType2, which is set to @AccountType within the procedure and @AccountType2 is the one referenced in the query. This way, when the optimiser "sniffs" the @AccountType2 parameter it will always be the same initial value. Note, the sniffing happens when the proc is called, not when the statement is about to be executed.
4. Part of the solution might be to disable automatic updating of statistics and to update statistics manually (followed by performance and plan flipping tests). Note, there is a lot more to statistics and this is probably only appropriate for a tight OLTP environment. Disabling automatic updating can negatively impact performance of ad hoc queries that are using recently inserted key ranges. However, note; updated statistics will trigger recompilation of dependent query plans.
5. Use the OPTIMISE FOR hint. However this often is not appropriate as there is no appropriate value that will continue to be appropriate in the future. Note, since 2008, you can OPTIMIZE FOR UNKNOWN, which is similar to point 3 above.
6. Use the KEEPFIXED PLAN hint, which inhibits recompiles due to optimality issues.
7. Use a plan guide. Technically, this solution works, but it does add to operational overhead and the plan guide may not be appropriate in the future.
8. Enable Trace Flag 4136, which will disable parameter sniffing. Since parameter sniffing is generally a performance benefit, this is a bit drastic.
9. Force the use of a particular join and/or index. This is frowned upon for good reason.

Note, SET options can affect plan creation and reuse. This means that if you want to use a plan in Management Studio the same as the application, you will need to ensure your SET options are the same as the application. SET options that affect reuse include ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER. The SET options used in the compile can be found in the query_plan under the tag StatementSetOptions. 

Again, plan flipping is not something that should cause you anxiety. However, it is good practice for database developers to recognise plan flipping possibilities and use the techniques above to minimise its causing performance issues. Most plans are very good and better than the above average developer. I encourage developers to make use of point 1 above, where there are different "classes" of parameter values.

Here is a useful query to see the query plans for a particular procedure. Click on the query_plan to see a graphical plan view. Note, this query is looking for procedures only. You can also view p.objtype='Adhoc' for ad hoc queries that are compiled as "temporary procs". You are able to view the parameter values used for compilation of the query plan from the query_plan of the first query above. Simply copy out the query_plan xml and search for ParameterList.

   SELECT p.*,st.text, qp.query_plan

      FROM sys.dm_exec_cached_plans p

            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

            cross apply sys.dm_exec_query_plan(p.plan_handle) as qp

      WHERE st.text LIKE '%MyProcedureName%'

        and p.objtype='Proc'

Here is a useful query to see the performance of multiple query plans

   SELECT * FROM sys.dm_exec_query_stats

      WHERE plan_handle in

                  (SELECT plan_handle FROM sys.dm_exec_cached_plans p  

                        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 

                        WHERE text LIKE '%MyProcedureName%'

                          and p.objtype='Proc')

Here is a query to list all the procedures that have more than one query plan.

   SELECT db_name(st.dbid) DbName, st.text, count(distinct plan_handle)

      FROM sys.dm_exec_cached_plans p

            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

            cross apply sys.dm_exec_query_plan(p.plan_handle) as qp

      WHERE p.objtype='Proc'

       group by db_name(st.dbid),st.text having count(distinct plan_handle) >1

       order by 1,2

Basket Analysis Made Easy

This article is aimed at technologists who hear about Basket Analysis and need a simple explanation with examples. Basket Analysis used to be the preserve of high cost specialist providers. Now we have OLAP technology and hardware that makes Basket Analysis available at commodity prices. Everyone involved in retail analytics should understand Basket Analysis. Essentially, Basket Analysis supports analysis of sales by the combinations of items purchased.

To elaborate, I will provide two classic examples of Basket Analysis.

1. Menu Trim
The business would like to remove a couple of low value items from the menu (product list). A smaller menu generally supports lower operational cost and inventory investment. Candidate items might be the lowest by sales value or gross profit contribution. We observe that the lowest value items are Frozen Yoghurt and Fruit Salad, which we can find by a simple slice of items and their sales/gross profit. However, this does not take into account whether customers come in to buy the product on its own, or whether they are buying lots of other products too. We can see from the Basket Analysis query below, that Frozen Yoghurt customers buy lots of other products with their Frozen Yoghurt. Basket Analysis can sum all the products the customer buys with Frozen Yoghurt, which we call Affected Net Sales. This suggests that we can remove Fruit Salad from the menu we only risk the $15,414 of sales, but if we remove the Frozen Yoghurt, we risk $1,899,151 of sales. We say that it is "at risk" because the customers might be coming in especially for the Frozen Yoghurt, and purchasing other products while there. Without Frozen Yoghurt these customers might go elsewhere for their entire basket.

2. Attracting Large Value Transactions
Your business might have a strategy to increase sales by promoting menu items that tend to be involved in large value transactions. Basket Analysis can help you do this by identifying menu items (product items) involved in large transactions. In the example above, Avocado Toast has a high transaction average of $105, while the average transaction for all items is $45. So, the business strategy might be to discount and/or promote Avocado Toast to attract these high value customers.The marketing geniuses will work out a way to attract these customers, which might be by discounting Avocado Toast, offering a discounted side item, promoting quality/health features, inter-company coupon, etc. Once the campaign starts, Basket Analysis can very succinctly report on the success of the campaign. One of the key indicators will be the transaction average of Avocado Toast transactions during the campaign. The sales of Avocado Toast might increase two fold, but if the Transaction Average is halved, you are probably only attracting customers who purchase Avocado Toast alone. Ie, you are not attracting customers with large transaction value. In the chart below, we can see that the Avocado Toast promotion has been successful in increasing Avocado Toast transactions, however, these additional transactions have been small value transactions. The marketing geniuses might need to try a new strategy!

I hope this has shown how Basket Analysis can help business' create better strategies and measure how well these strategies are working. This is just two simple examples of an infinite number of possibilities, only limited by your creativity. Basket Analysis has become a commodity feature in retail analysis and is becoming an essential tool for successful retail strategies.