Facts and Measures

Facts and Measures

Measures are useful numeric values originating from business transactions, which are used for analysis through aggregation later on.

They are also known as facts, so fact tables are basically collections of keys and measures.

Depending on the type, some facts are more useful for aggregation and analysis than others.

So how can you identify the type of measure you are collecting?

Here’s a quick general guide for confirming what type of fact you’re working with:

Questions to practice:

What are measures used for?

What are the different types of measures?

Can you think of any examples for each of the types?

My Epic Life Quest

Have you ever heard of anyone actually following through with their new year’s resolution? How about five new years resolutions? I’m sure most people wouldn’t even dream of achieving that much, possibly because it’s no fun. Well that’s exactly what Steve Kamb’s Epic Quest of Awesome is all about.

Steve has accomplished more than most could accomplish in ten lifetimes, using a system likened to playing a video game. All of his goals are split into levels, and each goal counts for 20% exp toward the next level, with some major goals counting for 100% exp.

I’m not a gamer, but this sure does add a fun twist to goal-setting, and more importantly, achieving.

So, with great respect to Steve Kamb and the legendary Brent Ozar, here is my…

EPIC LIFE QUEST

Level 2 – The Transition

Level 1 – The Japan (Completed 30/06/2016!)

  • Pass the Microsoft 70-463 Exam (Completed 18/04/2016!)
  • Get a Japanese driver’s licence (Completed 17/03/2016!)
  • Have an awesome time in Okinawa (Completed 26/04/2016!)
  • Run 100km in a single month (Completed 31/05/2016!)
  • Become MCSA SQL Server 2012/2014 certified (Completed 30/06/2016!)

Blog goals:

  • Get 10 followers on my blog
  • Write four blog posts in a single month

Exercise goals:

  • Run 600 minutes in a single month
  • Do 6000 Russian twists in a single month
  • Do 2000 push-ups in a single month

Study goals:

  • Pass the MCSA
  • Pass next exam
  • Get MCSE!

–!>

Detect package runtime in SSIS

In this article we’ll be looking at how to add a simple runtime detection component to your SSIS packages. Although it’s possible to query the use32bitruntime value in the SSISDB catalog.executions table with an Execute SQL task, this only works for packages that have been deployed to IS Server. It’s better use a more universal solution that isn’t going to depend solely on the project deployment model.

The best way to detect the runtime of a package execution is using a script task. After the runtime has been detected, precedence constraints can be used to tell SSIS what to do in the case of 32-bit, or 64-bit execution.

In this example, the solution will end up looking like this:

runtimedesignerview
Continue reading “Detect package runtime in SSIS”

My Running Journal

After spending three months doing pushups and ab exercises, I’ve come to realise something:

There is no substitute for regular cardio!

After just one run, all my sluggishness, all of my worries, all of my inaction just seemed to evaporate. From now on, I will endeavour to run three times a week, and this blog will hold me accountable to my word.

Date Distance Time Speed Details
March 2016 55.1 km 290.5 min 11.38 kph 9
April 2016 43.6 km 236 min 11.08 kph 7
2016-5-4 4.8 km 30 min 9.6 kph Takahagi East
2016-5-5 5.7 km 30 min 11.4 kph Takahagi West-East Cct
2016-5-7 5.7 km 32 min 10.68 kph Takahagi East-West Cct
2016-5-8 5.0 km 29 min 10.34 kph Gusto-Roundabout Cct
2016-5-10 5.0 km 27 min 11.11 kph Gusto-Roundabout Cct Light rain
2016-5-12 6.1 km 35 min 10.45 kph Beisia-Roundabout Cct
2016-5-14 6.1 km 32 min 11.43 kph Beisia-Roundabout Cct
2016-5-17 6.8 km 35 min 11.65 kph 7Eleven-Beisia-Roundabout Cct
2016-5-21 6.8 km 37 min 11.02 kph 7Eleven-Beisia-Roundabout Cct
May 2016 52.0 km 287 min 10.87 kph
2016 TOTAL 155.5 km 843.6 min 11.05 kph

Viewing the SSISDB Structure Easily in T-SQL

The following view will allow you to view the entire SSISDB structure easily, which is useful particularly when confirming the presence of certain folders, projects and packages from an SSIS Execute SQL Task.

Make sure you’re connected to the SSISDB database first before you run this code.

IF OBJECT_ID(N'catalog.structure', N'V') IS NOT NULL
  DROP VIEW catalog.structure;
GO
 
CREATE VIEW catalog.structure
  AS
  SELECT
         F.folder_id
       , F.name AS [folder_name]
       , PR.project_id
       , PR.name AS [project_name]
       , PA.package_id
       , PA.name AS [package_name]
       , CASE
         WHEN PA.version_major IS NOT NULL THEN
           CONCAT(N'v',PA.version_major,N'.',PA.version_minor,N'.',PA.version_build)
         ELSE
           NULL
         END AS [package_version]
  FROM catalog.folders AS F
    LEFT OUTER JOIN catalog.projects AS PR
      ON F.folder_id = PR.folder_id
    LEFT OUTER JOIN catalog.packages AS PA
      ON PR.project_id = PA.project_id;
GO
 
SELECT *
FROM catalog.structure;

Project & Package Deployment Methods – SSIS Basics

As a newbie to SSMS and SSIS, one of the hardest things to wrap my head around was the difference in the way in which projects and packages are deployed.

After diving into Books Online, as well as playing around with SSIS endlessly, I came up with the chart below, which illustrates some of the differences between the project and package deployment models.

projectvspackage

Build

When a project is built, all of the files in the project are stored in an .ispac file, which is basically an archive containing all of the package files (.dtsx), project-level parameter and connection files, and a few others.

When a package is built, the package files are stored separately as .dtsx files, and a Deployment Utility file (.manifest) is also generated if the CreateDeploymentUtility option is set to True.

UI-based deployment method

Projects are deployed with the Integration Services Deployment Wizard, which can be initiated by either right-clicking the project in SSDT and selecting Deploy, or by double-clicking the .ispac file stored from the build. The first method will generate a new .ispac file to work with anyway.

Packages are deployed with the Package Installation Wizard. This can be accessed by double-clicking on the .manifest file generated from a build. The packages can then be deployed to either a file system location, or SQL Server, in the SSIS Package Store.

(Automated) Command line deployment method

For projects, use the following command line:

ISDeploymentWizard /S /SP:"MyProject.ispac" /DS:"MYSERVER" /DP:"/SSISDB/MyFolder/MyProject"

When the /S[ilent] option is not used, the UI version of the wizard will appear. It’s a good idea to first use the UI version to generate a command line to work with, which is displayed on the last page of the wizard.

The options for this method are as follows:

ISDeploymentWizardOptions

For packages, use the following command line.

dtutil /File DimCustomers.dtsx /COPY DTS;MSDB\DimCustomers

The above line deploys a package from the file system, to MSDB in the SSIS package store. The following code is an alternative that achieves the same thing:

dtutil /File DimCustomers.dtsx /COPY SQL;DimCustomers

A lot of abbreviation can be made with the options, so it pays to memorize what they all mean:

dtutilOptions

For more examples of how to use dtutil, be sure to take a look at books online.

Try using the methods mentioned above, and let me know how you go!

 

Alerts and Notifications in SSIS

An alert enables an operator or operators to receive notifications when a certain event occurs within SQL Server, such as a query error, SSIS package failure, or backup completion.There are three different methods of notifications, as follows:

  1. Database Mail
  2. Pager (for which support will be removed in the future, but is still supported for anyone stuck in 1994)
  3. Net send (still supported but this feature cannot be used for any OS after Windows XP Service Pack 2)2000px-crystal_clear_app_email-svg

Hmm, so there’s really only one method of notification which you should use: the Database Mail notification.

There are three ways in which you could use notifications with SSIS packages:

  1. The Send Mail Task, typically in an Event Handler (SMTP server will be required to set this up)
  2. The Notify Operator Task, which is similar to the Send Mail Task, but allows you to select the operators in SQL Server Agent to be notified
  3. The Execute SQL Task, using the sp_notify_operator command, is basically the same thing as the Notify Operator Task, but this method allows greater flexibility.