I’ve moved!

My blog can now be found at www.sqlzapper.me.uk

Creating a basic score card in SQL

In many industries we require a method for scoring individuals based on a certain set of criteria.

This post will outline a basic implementation for scoring an individual using sql.  I won’t be touching on any of the more complex areas of score cards such as concepts and making score cards predictive or any of the more technical details around the implementation such as making it performant.

The brief
We want to score a set of individuals, in this case parents.

Create a solution that will enable a parent to be scored based upon the type and number of occurences of an activity they perform on a daily basis.

The solution

Lets run through the table objects in turn –

• Parent – holds a description of the parent
• Activity – holds the activities a parent can perform
• ScoreCard – this table allows us to create different score cards and assign a base score and default score to each
• ScoreCardActivity – map the ScoreCard values to the activity and assign a score
• ParentActivity – daily log of the activities each parent has performed

Creating the structure
create table dbo.Parent
(
ParentId int primary key
,ParentName varchar(50)
);

create table dbo.Activity
(
ActivityId int primary key
,ActivityName varchar(50)
);

create table dbo.ScoreCard
(
ScoreCardId int primary key
,[Description] varchar(50)
,BaseScore smallint — the starting score
,DefaultScore smallint — the score you get if you do nothing
);

create table dbo.ScoreCardActivity
(
ScoreCardActivityId int identity (1,1) primary key
,ScoreCardId int
,ActivityId int
,minValue smallint — minValue and maxValue allow us to assign a different score adjustment for the number of times a parent performs an activity
,maxValue smallint
,ScoreAdjustment smallint
);

create table dbo.ParentActivity
(
ParentActivityId int identity(1,1) primary key
,ParentId int
,ActivityId int
,[date] date
);

alter table dbo.ParentActivity add constraint fk_ParentActivity_ParentId foreign key( ParentId) references dbo.Parent( ParentId);
alter table dbo.ParentActivity add constraint fk_ParentActivity_ActivityId foreign key( ActivityId) references dbo.Activity( ActivityId);
alter table dbo.ScoreCardActivity add constraint fk_ScoreCardActivity_ScoreCardId foreign key( ScoreCardId) references dbo.ScoreCard( ScoreCardId);
alter table dbo.ScoreCardActivity add constraint fk_ScoreCardActivity_ActivityId foreign key( ActivityId) references dbo.Activity( ActivityId);

Creating some test data
— set up the scorecard
insert into dbo.ScoreCard( ScoreCardId, [Description], BaseScore, DefaultScore)
values( 1, ‘Parenting score’, 400, 0);

— create our parents
insert into dbo.Parent( ParentId, ParentName)
values( 1, ‘Dad1’), ( 2, ‘Dad2’), ( 3, ‘Dad3’);

— create our actvities
insert into dbo.Activity( ActivityId, ActivityName)
values( 1, ‘Change nappy’), ( 2, ‘Read book’), ( 3, ‘Play ball’), ( 4, ‘Sit in front of the tv’);

— fill in the log of activites each parent performed with their child
insert into dbo.ParentActivity( ParentId, ActivityId, [date])
values( 1, 1, ‘20120601’) — Dad1 – one nappy change
, ( 1, 1, ‘20120601’) — Dad1 – two nappy changes
, ( 2, 4, ‘20120601’) — Dad2 – just sat infront of the television
, ( 3, 2, ‘20120601’) — Dad3 – played ball
;

— For score card 1 assign the activities to it and set the score adjustment value
insert into dbo.ScoreCardActivity( ScoreCardId, ActivityId, minValue, maxValue, ScoreAdjustment)
values( 1, 1, 1, 1, 10) — the first nappy change will increase the parents score by 10..
, ( 1, 1, 2, 999, 5) — but subsequent nappy changes will only increase the parents score by 5
, ( 1, 2, 1, 999, 20)
, ( 1, 3, 1, 999, 15)
, ( 1, 4, 1, 999, -500) — sitting in front of the tv will have a huge negative impact on the score
;

Now for the code
The following sql would probably be created as part of a stored procedure.

— gather the data for the day we’re interested in
— the correlated subquery keeps a running total of the number of times a parent has performed an activity. This allows us to score subsequent occurences of an activity appropriately using the minValue and maxValue fields in the where clause later.
select p.ParentName, pa.[date], pa.ActivityId
, (select count( ActivityId) from dbo.ParentActivity where ParentActivityId <= pa.ParentActivityId and ParentId = pa.ParentId) as ActivityOccurrence
into dbo.#WorkScoring
from dbo.Parent p
join dbo.ParentActivity pa
on p.ParentId = pa.ParentId
join dbo.Activity a
on a.ActivityId = pa.ActivityId
group by p.ParentName, pa.[date], pa.ActivityId, pa.ParentActivityId, pa.ParentId;

— run the scoring
select w.ParentName, w.[date], sc.BaseScore + sum(sca.ScoreAdjustment) as ParentScore
from dbo.#WorkScoring w
join dbo.ScoreCardActivity sca
on sca.ActivityId = w.ActivityId
join dbo.ScoreCard sc
on sc.ScoreCardId = sca.ScoreCardId
where sc.ScoreCardId = 1 — not needed for this example but if we had multiple score cards either this clause or a select/group by would be required
and w.ActivityOccurrence between sca.minValue and sca.maxValue
group by w.ParentName, w.[date], sc.BaseScore;

The results

And we’re done!

Balanced Data Distributor

I’ve recently been looking at using the BDD component for SSIS 10.0.

When attempting to install the component the following error was received –

“The installation is not successful. Check the following prerequisites: 1. Either Integration Services or BIDS has to be installed. 2. The version of these components has to be either SQL Server 2008 SP2 (or future SPs) or SQL Server 2008 R2 (or future SPs)”

It seems that this error is only received if you’ve patched your R2 instance to SP1.  The workaround is to trick the installer into thinking that a lower patch level is installed.

Using regedit set the following keys under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\ to these values –

• DTS\Setup\SP = 0
• DTS\Setup\Version = 10.50.1600.1
• BIDS\Setup\SP = 0
• BIDS\Setup\Version = 10.50.1600.1

 

Thanks to joshgallagher.info for the fix.

 

In search of an easier way to manage lookup data

Our current method for managing lookup data is to use  custom sql scripts.  Our lookup data scripts form part of our continuous integration environment and are ran as part of the database project post-deployment script so they need to be re-runnable.  This is achieved by checking for the existence of a record before inserting.  If the record doesn’t exist it will insert one and if one already exists it will update all non key columns so that the record is essentially synced.  For example  –

— create a temporary table to use to sync the static table to

create table dbo.#mytable( cola int, colb varchar(30), colc varchar(30)) 

— insert lookup data into the temp table

insert into dbo.#mytable( cola, colb, colc) values( 1, ‘a’, ‘record’) 

insert into dbo.#mytable( cola, colb, colc) values( 2, ‘another’, ‘record’) 

— update existing records

update m

set m.colb = t.colb

    ,m.colc = t.colc

from dbo.mytable m

    join dbo.#mytable t

        on m.cola = t.cola

 — now insert new records

insert into dbo.mytable( cola, colb, colc)

select cola, colb, colc

from dbo.#mytable t

where not exists( select ‘x’ from dbo.mytable where cola != t.cola) 

 

These scripts were written prior to SQL2008, obviously projects targeting SQL2008 databases could use the merge statement.

These scripts can become quite large and tedious to maintain, particularly seeing as though you have to make an entry for the same piece of lookup data twice; once for the insert statement and once for the update statement.

After spending many minutes creating a new lookup data script I remembered reading a SQLCAT post titled Top Ten Hidden Gems in SQL2005.  Item 1 on the list is TableDiff.exe.  Rather than rewrite the article here is an excerpt from it –

Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input;

• Connectivity- Provide source and destination objects and connectivity information.
• Compare Options – Select one of the compare options
• Compare schemas: Regular or Strict
• Compare using Rowcounts, Hashes or Column comparisons
• Generate difference scripts with I/U/D statements to synchronize destination to the source.

TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema.

I haven’t actually given this a try yet but I reckon it would be possible to write a script that contains only insert statements into a work table and then to call TableDiff.exe to sync this table to the static table.

It would be great to hear how others manage lookup data so please comment!

Database projects using SQL Data Tools

Over the last few years as a development department we have invested a lot of time in converting our database software into visual studio database projects.  The time spent has been more than worthwhile as the benefits gained in terms of ease of deployment alone are invaluable.

So when Microsoft announced that database projects are changing from Data Dude (.dbproj) projects into SQL Server Data Tools (.sqlproj) projects I thought its time I best get the changes reviewed.

The information on this blog is simply my initial findings from playing around with the tools.  I’ve approached it with a view to comparing .dbproj projects to .sqlproj projects.

Key differences overview

• .dbproj files have been replaced by .sqlproj files
• .dbschema files have been replace by .dacpac files
• Server Projects no longer exist
• Database Unit Test projects aren’t yet included in VS2012 (I’m guessing Microsoft haven’t yet updated them to make them compatible with LocalDB)
• Database projects are no longer “Deployed”, they’re “Published”
• Vsdbcmd.exe has been replaced with SqlPackage.exe
• The project branch structure that we’re used to is no longer automatically created.  Each object that you add to the project is placed in the root of the project structure.
• Primary and foreign keys and indexes and constraints are all placed on the “create table” script rather than being separate files
• SSDT power tools are required to gain schema view

Builds and Deployments

• LocalDB can be used to build and deploy projects locally rather than having to install a full SQL Server instance.  This will be extremely useful for build machines and isolating builds from one and other.
• A .dacpac file is created when a build is ran or when a snapshot of a database project is created
• A .dacpac file can be “unpacked”.  It contains the files –
  • DacMetadata.xml – contains the database name and version number
  • model.sql – contains create statements for all objects in the project
  • model.xml – similar to a .dbschema file
  • Origin.xml
  • refactor.xml – contains information from the refactor log
• A publish summary report is created – DeploymentReport.txt
• Projects can be published through the visual studio gui or by using SqlPackage.exe
• Hit F5 to “Deploy” or sync project changes to the in memory localdb
• VS2010 Database unit test projects will execute against the in memory localdb
• LocalDB instances can be managed via the command line using SqlLocalDB.exe
• A localdb instance named after the solution is created automatically when the project is opened and the current project state is deployed to it
• Specifying a localdb name of “(localdb)\v11.0”in a connection string will automatically create a localdb instance without having to create it programmatically
• LocalDB instances are created here – C:\Users\username\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances
• SqlPackage.exe is a command line utility that automates the following database development tasks:
  • Extract: Creates a database snapshot (.dacpac) file from a live SQL Server or SQL Azure databases.
  • Publishing: Incrementally updates a database schema to match the schema of a source .dacpac file.
  • DeployReport: Creates an XML report of the changes that would be made by a publish action.
  • DriftReport: Creates an XML report of the changes that have been made to a registered database since it was last registered.
  • Script: Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.

Hello world!

Hello and welcome to my blog.  My intention for this blog is to use it as an opportunity to share my experiences of all things SQL Server related.  For a bit of background about me and what I do please read the about page.

One final thing, please don’t take anything you read on here as fact!  Whilst I’ll do my best to ensure the content is correct some posts will undoubtedly contain errors; if you spot any please contact me.

Happy reading..