Live! 360–Speaking at SQL Server Live Orlando 2016

If you are looking for a great conference to attend, please look at Live! 360. This event combines 6 different areas of IT (including SQL Server) with some of the great experts presenting sessions for 5 days. The sessions are on December 5th thru the 9th in Orlando, FL at Royal Pacific Resort.

This is a link with a discount code for up to $500 dollars off – http://bit.ly/LSPK47_REG

Here are the three I am giving.

SQT07 New Performance Tuning and Security Features in SQL Server 2016

12/06/2016

2:00pm – 3:15pm

Level: Introductory to Intermediate

SQL Server 2016 has added and improved features to an already great product. To start, there is now a Query Store to retrieve history of a query’s execution plan and statistics used for that plan. You can compare them to see the changes. You can also see the Execution Plans “Live” to see where a long running query is spending lots of time. You can also compare plans side-by-side, which should make DBAs or anyone that performance tune queries very happy.

From there, you’ll go into some of the database design aspects of 2016 to improve table implementation. In-Memory has been half-baked in previous versions and now is enterprise ready with OLTP tables. The Column-store indexes include update-able clustered and non-clustered indexes. Temporal Tables remove the requirement of using triggers or custom code to find a point in time version of the data. You now can mask columns for limited viewing and casting of data to the end users by login permissions.

The T-SQL enhancements will help with better writing of set-based queries. The stretch database feature will assist in archiving data to the cloud with access in applications. Always Encrypted secures the database for abiding to regulations in health care and finance. The last feature will be row-level security, which has been a frequently requested option.

You will learn:

  • About T-SQL enhancements in SQL Server 2016
  • About Table design changes in SQL Server 2016
  • About Query Plan improvements in SQL Server 2016

 

SQW05 Master Data Management with Data Quality (DQS) and Master Data Services (MDS) in SQL Server 2016

12/07/2016

9:30am – 10:45am

Level: Intermediate

Is there data scattered all over your enterprise? Has your boss asked you to help your users create and manage one source of common data? Are there different spellings of values in the same column of a table? Can you use help from algorithms to find the best match? Is the Customer or Product table in multiple databases with different columns? Do you need help managing the Data Warehouse?

This session will jump into SQL Server 2016 with DQS and MDS. You use the Data Quality projects in Integration Services (SSIS) to rank values and help with misspelled data. You’ll help your users manage the DQS projects giving them management access to data quality and moving that responsibility to the business where it should be.

Next, you’ll use MDS to help consolidate dimensions scattered in multiple databases to form a true Conformed Dimension area. MDS will help your company move away from spreadsheets or flat files to manage critical information for analytics. These features were added to SQL Server in version 2012 and have been upgraded in 2014 and 2016 to help enterprises with the task of Master Data Management.

You will learn:

  • How to use Data Quality Services to cleanse data with DQS and SSIS
  • How to create a Master Data Management system with MDS in SQL Server 2016 to consolidate multiple data sources

 

SQW12 Improve Enterprise Reporting with SQL Server Analysis Services

12/07/2016

4:00pm – 5:15pm

Level: Intermediate

Have you been at a job where requests keep coming in for more and more reports? Does it seem like some are being used and other are not? Are some of the reports exactly the same except a different grouping or sorting request? Has your boss asked you to investigate a Business Intelligence or Data Warehouse solution? Well, it might be time to start using SQL Server 2016 Analysis Services.

Dimensional Modeling is one of the best starts for designing a system with flexible reporting. The database model fits perfectly into Analysis Service (SSAS) databases from Microsoft. There will be a use case for 3 fact tables to create various data marts. We will demonstrate a Multidimensional Cube and the Tabular Model to help you make the decision of which installation to use. Excel and PowerBI will be the focus for reporting.

Using a Lookup Component in SSIS for Surrogate Keys in a Fact table

There are many suggestions for loading a data warehouse using SQL Server integration Services (SSIS). Once you get started, you find a pattern to repeat for facts and dimensions. This might be because the consistency Dimensional Modeling suggests for the design of fact and dimension tables. The SSIS Lookup component provides options to divert handling of non-conformed data or you can use your T-SQL skills up front where a diversion is not necessary.

The most common use of a Lookup component is to find the Dimension surrogate key for one or more rows in a fact table. The CustomerKey from the Customer dimension will be used in the Sales Fact table in order to join to customer hierarchies and attributes like the following T-SQL:

 

SELECT c.CommuteDistance, Sum([OrderQuantity]) AS OrdQty , Sum([SalesAmount]) AS SalesAmt

FROM [dbo].[FactInternetSales] fis

INNER JOIN dbo.DimCustomer c ON c.CustomerKey = fis.CustomerKey

GROUP BY c.CommuteDistance

 

CommuteDistance

OrdQty

SalesAmt

5-10 Miles

10615

4893148.0413

2-5 Miles

10084

4965514.4362

1-2 Miles

10170

4541608.1498

0-1 Miles

21307

11207592.2248

10+ Miles

8222

3750814.3686

Table 1: Sales Amount and Order Qty sums for Commute Distance

 

Once you get the source data for the Sales fact in an SSIS package, the Lookup component can be used to get the surrogate from the customer dimension. In Figure 1, the object LU – Customer Key lookup in the flow of populating this fact table.

 

clip_image003

Figure 1: Loading the FactInternetSales table from the AdventureWorksDW Database

 

The options for Lookups vary based on some properties. In Figure 2, we can see some of these options.

 

clip_image005

Figure 2: General Lookup properties

 

Most lookups for dimensions can use the Full Cache mode because there is not a lot of dimension rows. But, if you come across large dimensions in a data warehouse, sometime should be spent seeing if a Partial Cache or No Cache will help with loading speed as well as using a Cache connection manager. The Full Cache option will load all the data from the dimension table into memory before any flow starts in the package. This is why it is good not to SELECT all the columns in the dimension table for a lookup.

 

clip_image007

Figure 3: Connection properties

 

Figure 3 shows the connection using a T-SQL statement to only get the CustomerKey and CustomerAlternateKey from the DimCustomer table. The CustomerAlternateKey is the business key that matches customer to rows in the source data for FactInternetSales. If you use the option “Use a table or a view”, the generated query will select all columns from the source.

Let’s go back to the General properties to look at “Specify how to handle rows with no matching entries”. Figure 4 shows the different options available.

 

clip_image009

Figure 4: General properties

 

The “Fail component” option will stop processing of the import if no match is found in the lookup table. This is not a good option for loading data into a fact table. Most systems would want the import to continue for the customer surrogate keys that are found.

  1. Ignore Failure – Null will replace lookup values selected. Those rows with no match are streamed to the normal flow in package with the Null value in match columns selected.
  2. Redirect rows to error output – red line output will show a failure but can pipe the data to any component. Those rows with no match are not streamed to the normal flow in package.
  3. Fail component – the package stops with a failure if no match exists, if all match there is no failure
  4. Redirect rows to no match output – output can be piped to another component and processing continues. Those rows with no match are not streamed to the normal flow in package.

So, the two other options I use most are “Redirect rows to no match output” or “Ignore Failure”. The redirect rows… option can be used to stage the rows that have problems and get someone to look at them before the next import. The Ignore Failure option can be used to substitute a Null value in the CustomerKey column.

Now, this will not work if importing to a fact table with foreign keys and a Not Null property on the CustomerKey column, but what I do most often is change the Null value to -1 and have an entry in the DimCustomer table for the Unknown possibility like this example.

 

CustomerKey

GeographyKey

CustomerAlternateKey

FirstName

LastName

MaritalStatus

Gender

-1

-1

-1

Unknown

Unknown

U

U

 

clip_image011

Figure 5: Derived Column component

 

Figure 5 shows what can been done to convert the Null value in the CustomerKey column to -1. This way with the -1 row in the customer dimension table, we will be able to insert the row into the fact table without having to process the rows outside of this package.

 

clip_image013

Figure 6: Selected Lookup Columns and names

 

Figure 6 shows the Columns tab Properties. Here is where we match the Source fact column for customer to the Business Key in the Customer Dimension. We also select the CustomerKey from the Available Lookup Columns list in order to get the surrogate key for the fact table. The selected CustomerKey is where the Null value will be located if no match if found.

This process also indicates that another process needs to be implement to determine why existing fact rows have business keys for the customer source table that are not imported into the Customer dimension. This one tip can go a long way and patterns developed to help with this situation. It also indicates the power and flexibility SSIS provides data warehouse architects in implementing an ETL solution for its business.