Introducing Your Favorite Snippet Tool

Snippets in Visual Studio and SQL Server Management Studio are a great help, and tremendous time savers. Unfortunately the con about VS and SSMS snippets are: They are tedious to create. I have a feeling, that makes it less appealing to use custom snippets, because it includes working with XML to create them, and VS or SSMS offers no help.

History

Well, a couple of days ago, I decided to make a snippet of some SQL, which I had realized, I had to write regularly in the future. I was pretty tired of writing this SQL, and then I remembered: To create a snippet you have to setup an XML document. Then I got really tired. In hope of an easy solution, I did search the web for an online snippet creator. All i did find was tools, which had to be downloaded. No offence, these downloadables are properly mighty fine, but nowadays I think a tool like a snippet creator should be online. Easy to reach, and not another downloaded tool to soil your computer.

Priorities can be strange some times, and I decided, that I would rather write an online tool myself, which could make snippets, than do another handmade snippet.

So here after a small coding marathon, I'll present to you:

YOUR FAVORITE SNIPPET TOOL(that is the name)

Link: www.snippettool.net

Enjoy!

FAQ

Q: Why is the link www.snippettool.net and not www.yourfavoritesnippettool.com when the tool name is Your Favorite Snippet Tool?

A: For your convenience. It is much easier, to remember www.snippettool.net and type it right. 

Q: The first release is version 0.8.0, is it production ready?

A: Yes. There is some extended features for VB, which will be there in a later release. Further I have some ideas for UI improvements. Also, until I have had some more feedback, it wouldn't be right to release it in version 1.0.0

Q: VSI packages are supported, what about VSIX packages?

A: VSIX does not by default, support snippets. Hacks must be applied to make VSIX work with snippets.

Q:The Visual Studio Content Installer does not install the VSI package to Visual Studio 2xxx, why?

A: That is because, Visual Studio Content Installer is a strange piece of software. Specially, if you have more versions of Visual Studio on your machine.

Q: I found a bug, what to do?

A: I will appreciate, if you would write to me about it. Contact information is at the button of the page. 

SSIS: An easy SCD optimization for dev and prod

The value of reading this post, depends on how you work with SSIS and how database nursing are handled in within your organization.

The optimization is a single index, but if you only nurse indexes in prod, you could waste a great time when developing SCDs in SSIS. The method is simple, when you now the nature of your SCD, then you can create an index right away, and reduce your development waiting time. Specially if you are testing with bigger volumes of data.

Let me show you

Let's say you have following table definitions, and you working in a SSIS project using Visual Studio:

-- Staging

CREATE TABLE Staging.Customers

(

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

)

GO 

-- Dimension

CREATE TABLE dbo.dimCustomers

(

CustomerDwhKey INT IDENTITY(1,1),

[Current] BIT, 

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

CONSTRAINT PK_CustomerId PRIMARY KEY CLUSTERED (CustomerDwhKey)

)

GO

You have a Data Flow, where you transfer data from Staging.Customers to the dimension dbo.dimCustomers using the built-in component Slowly Changing Dimension:

In our example setup CustomerId will be a so-called Business key, And Current will be the indicator for which row are current. It should also be noted, it is possible to have more than one Business keys.

We'll configure attributes as:

Now, the Slowly Changing Dimension component work in following way:

For each entity it recieves, it will search the dimension table for an entity with the same business keys(in plural!!!) and is flagged as current, or in plain SQL:

SELECT  attribute[, attribute] FROM dimension_table WHERE current_flag = true AND business_key = input_business_key[, business_key = input_business_key]

Or as it will look like in our example

SELECT AccountId, CreationDate, FirstName, MiddleInititals, LastName FROM dbo.dimCustomers WHERE [Current] = 1 AND CustomerId = some_key

Further, in case we have an historical change, the current entity in the dimension must be expired by setting Current = 0.

UPDATE dbo.dimCustomers SET [Current] = 0 WHERE [Current] = 1 AND CustomerId = some_key 

The solution

As you might have realize by now, we can improve performance tremendously by putting an index on the current flag and the business keys(again plural!!!). For each entity passing through the Slowly Changing Dimension component, the will be at least 1, but likely 2 searches in the dimension table. And the by knowing the business keys and current flag. and the nature of the Slowly Changing Dimension component, you can predict the index which will improve performance.

The index for our sample will be

CREATE NONCLUSTERED INDEX IX_Current_CustomerId ON dbo.dimCustomers

(

[Current],

CustomerId --- Remember to include each business key

)

Should index be a filtered? I'll let that be up to you.

Indexes, bulk and loading of dimensions

Some tend to drop indexes when loading dimenson, with the argument: Bulk loading is fastest without indexes, which SQL Server has to maintain while loading. This argument has to be revised when working with the Slowly Changing Dimension component.

Because the component searches the dimensions so heavily, (in general) it will be faster loading with indexes than without. If there is no indexes, each entity going through the component, will require at least one table scan, which is quite expensive, and gets more expensive as your dimension grows. 

That's all

Balancing resources in SQL Server

Level: 3 where 1 is noob and 5 is totally awesome
System: SQL Server

As software developer I believe in decoupling, and I'm certain that decoupling is just as important in database design as well. I really like having things in minimum chunks, and keeping them independent as possible. Independence and decoupling equals scalability. This post is based on a professional experience, which supports my belief.

At my work we are developing a billing platform, and this platform has several clients. Each client use our platform to bill a certain amount of customers. So this set the scene.

Some time ago, we had one database for each client in one instance, which is a easy pattern. But in SQL Server it can easily be an expensive anti-pattern, and it is certainly a pattern which is my last choice. The problem with this pattern, it is only effective, when the databases is equal regarding size and usage.

The buffer pool

The explanation is, there is only one buffer pool pr. instance. SQL Server is an in-memory database, but in most cases, there is not enough memory for a whole database in the memory. And if there is more databases in an instance, then it is almost certain there no room in the memory for all the bases. To solve this issue, SQL server has the buffer pool, which is a cache. When querying, SQL Server will get the result from the buffer pool. If it not available in the buffer pool, it reads from the storage to the buffer pool. If there if no room in the buffer pool, it will flush some of the buffer pool, to make room for the requested data. Everything has to go through the buffer pool.

The process of getting data from storage to the bufferpool, generates PAGEIOLATCH waits. A high count of PAGEIOLATCH waits can be a sign of much data is being loaded from disk to the buffer pool.

Let us say, we have 3 databases in one instance. They respectively supports 25.000, 150.000 and 300.000 customers. The sizes of the databases reflects the number of customer, because more customers generates more data. What will happen, is the database with the most usage, will push out the data from the other bases, out of the buffer pool. This will give the lesser bases more read from the disk, producing more PAGEIOLATCH wait, adding more latency to the lesser bases. Actually, the smallest databases will suffer the most.

The poor solution  

I'm tempted to call this, the poormans solution, but it can be very expensive. The solution is to extend the buffer pool, with more memory. But there is a physical and economical limit to this. SQL Server 2014 has a new feature called buffer pool extensions, where it is possible to extends the buffer with storage(preferable SSD drives). I guess extending the buffer pool, will only cache more of the database with most uses. Besides, we have tried this feature, and have yet to see some great results. I would really like to hear, if some has used this feature with success.

The better solution

A easy way to balance databases resourcing over an instance, is by using the resource governor feature in sql server. It works excellent, but it is really a doubled edged sword. Used wrong it can really downgrade performance. It would also require some nursing, which I prefer to keep to a minimum.

The best solution

I find the best solution is to have more instances. If I have 3 clients, I will assign an instance to each, make it thier domain. Each instance, should have hard assigned a certain amount of memory, and their databases would never be able to inflict each others performance. 

This was the solution we did end up with, and it gave us better performance overall. 

  

SQL Server, SSIS or SSAS: Fastest and easiest way to create a time dimension

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server 2005 and above

One of the most vital components of a dataware house, is the time dimension. It also seems to be the part which people to have most difficulties to generate. I seen all from black magic in MS Excel to big complex SQL scripts and even fancy SSIS solutions, just to generate time dimensions.

I do believe, I have found a better solution.

My solution is this script, I have written(inspired from a script I did found on Stack Overflow). You set a start and end date, plus the granularity. My recommendation is to generate times for at least a decade. A decade with the granularity of one hour, takes less than 5 Mb of data storage and 87601 records with this script as default. A decade is generated in a few seconds.

 DECLARE @StartDate DATETIME = '2010-01-01';   
 DECLARE @EndDate DATETIME = '2019-12-31';   
   
 WITH DateTimeGenerator AS  
 (  
  SELECT @StartDate AS DateAndTime  
  UNION ALL  
  SELECT DATEADD(HOUR, 1, DateAndTime) -- Change datepart to ajust granularity, see http://msdn.microsoft.com/en-us/library/ms186819.aspx for more  
  FROM  DateTimeGenerator    
  WHERE  DateAndTime + 1 < @EndDate  
 )  
 SELECT ROW_NUMBER() OVER (ORDER BY DateAndTime) As Id,   
 DateAndTime,   
 DATEPART(HOUR, DateAndTime) AS [Hour],  
 DATEPART(DAY, DateAndTime) AS [Day],  
 DATEPART(WEEK, DateAndTime) AS [Week],  
 DATEPART(MONTH, DateAndTime) AS [Month],  
 DATEPART(QUARTER, DateAndTime) AS [Quarter],  
 DATEPART(Year, DateAndTime) AS [Year]  
 INTO DimTime  
 FROM  DateTimeGenerator  
 OPTION (MAXRECURSION 0)  

Enjoy

SQL 2014: Getting started with OLTP, OLAP, SSIS, SSAS and SSRS

As passionate developer, I come in touch with many technologies, but only a few of them has been so tough to crack as SSAS and SSRS. While learning SSIS, I felt i was learning a lot about datawarehousing, and I couldn't really imagine there could be more to it. At the same time I knew there must be more to it, as SSAS and SSRS was a part of the SQL Server suite.

Now I know that SSIS, SSAS and SSRS can be translated to data gathering, data modelling and data visualisation.

And I have written this post to give a swift explenation of SSIS, SSAS and SSRS so it might be easier for you to get started.

My dream database setup 

Starting with a greenfield project, is often difficult. There no experiences and no knowledge, thereby there is paved no road, to go by when developing the system. In my opinion, the 1st mistake which the most make at this point, is to put it all data in same database. This leads to a monolith system, which is hard to maintain and expand. The 2nd mistake is not to divide the database setup in OLTP and OLAP. This lead to analysis with heavy querying in production and passive historic data also stored in production.

OLTP is an acronym for Online Transaction Processing. In other words, it is the database, which is in the line of business. You can also call it, the database with live data. In my dream setup for at system, i would have a bunch micro services, all supported by their own database. Not necessarily relational databases, I would have the database which fitted the job best. The data in these databases, would be on a journey, which ended in a OLAP system. This journey could span from instant to the lifetime of the system.

OLAP is an acronym for Online analytical Processing. OLAP is almost always, analysed for financial reasons, and therefor it should be the last stop for data. It must not return to OLTP, because it would mean, that history could be re-written which is a problem when it comes to finance.

The process of getting data from OLTP to OLAP, would be data gathering, which is the perfect intro for next topic: SSIS.

SQL Server Integration Services (SSIS)

SSIS has become one of my favourite SQL Server tools. With few drag and drops, it is possible to setup data copy or transformation. Compared to scripting(as a developer I usually prefers scripting), it is so much faster to get something up and running, and also easier to structure. Enough praising.

Referring to OLTP about having several different databases, and SSIS being able to read from different source. It is the perfect tool to support this solution.

DQS (Data Quality Service) and MDS (Master Data Service) are often mention together with SSIS. These tool works only with SQL Server, but if you are all-in with SQL Server these tools are quite good. DQS is for correcting data, as they are gathered. MDS is sort of an authority which distribute a data model through out a system to the connected SQL Server database. It makes sure data integrity is kept.

There is other suppliers of ETL tool for SQL Server, but I only use SSIS.

SQL Server Analysis Services (SSAS)

Even thou data is arranged by stars, and it browsable through excel, it can be better. While a star is easy to imagine, it can be more difficult to imagine an cube, and in the beginning even to realize the difference. A cube is like a dimensional system, as we now it from math. Most of us can imagine 1st, 2nd, 3rd dimensional system. Some of us can even imagine a 4th dimensional system, by collapsing 3 dimensions and add a 4th dimension, so it look 2 dimensional again.

Each dim table in a star, would be a dimension in a cube, but they are still not the same. In principle you could take a star, print out all tables, put the papers on a table and then draw lines for each relation from the dimension tables to the fact table. Where a cube is arranged (as mentioned before) an a virtual dimensional system, it is really not printable, but fast for analysing/browsing.

SSAS Helps you building cubes, so that would be data modelling. It will also assist you to do some data mining. I'll will refrain to speak of data mining with SSAS, because I haven't touch that topic yet.

Working with cubes, is a way in SSAS which called dimensional mode. SSAS has two more modes: PowerPivot and Tabular. It is other ways to model data, and which one is best? It depends. 

But with there is no point in having data modelled, if you can't deliver their information in a readable way.

SQL Server Reporting Services (SSRS)

As you might have guessed SSRS is the visualisation part of the SQL Server suite. There not much to say. It easy to design reports with the editor, you can render reports to various formats and make them available from a reporting server or a share point site.

Take advantages of having SSIS, SSAS and SSRS available

SSIS, SSAS and SSRS are available from SQL Server standard, BI and Enterprise. Not using these tools, when having SQL Server licenses which includes them is as failure. Often have I seen, and have properly in past done myself, custom made reports and based on some analysis directly from the production environment. 

Nobody finds it funny to create custom reports and nobody finds it funny to maintain them. So using SSIS, SSAS and SSRS is a win-win, because it would mean full value from licenses, and developers would use less time on reports and more time on funny stuff.

Getting started 

Getting started with SSAS and SSRS, was a bit hard for me, but fortunately I did stumble in the book: Microsoft SQL Server 2014 Business Intelligence Development: Beginner’s Guide by Raza Red. It is real page turner, which is nice, if you reads a lot of books per year.

It is very comprehensive and filled examples, with good explanations, which I like a lot.

SQL Server 2014: SQL Server Data Tools(SSDT) and Visual Studio 2013 Challenges in Training Kit 70-463 and in general

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server 2014

Notice: I'm using the SQL Server 2014 Developer edition, so if you are using a different edition, things might be different.

Some History

I'm currently studying for the exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012, and I'm using the 70-463 training kit for the exam. I have decided to use SQL Server 2014 for the practical training(regarding to SSDT, I now know it is the best to use SQL Server 2014). It has just been released and regarding to exam 70-463 there is no change. People who took the exam for SQL Server 2012 is also certified for SQL Server 2014. For more see FAQ here at http://www.microsoft.com/learning/en-us/sql-certification.aspx.

When using the training kit, you sooner or later realize, it has become a bit outdated. Which is quite natural, because it was published 25. Dec. 2012, and lot have happened since. There has been new releases and updates of Visual Studio, and we even have a new version of SQL Server. Which, all in all is nice.

The first sign of you might be challenged when using the training kit with SQL Server 2014, is in the beginning. The exam kit provides a list of features you have to install, and last on this list is SQL Server Data Tools, also known as SSDT. As you might discover, this feature is not shipped with SQL Server 2014, and it is the most important component for SSIS and the exam 70-463.

SSDT is a Visual Studio plugin, for developing SQL Server Integration Service(SSIS) packages. SSDT is a replacement from Business Intelligence Development Studio, also known as BIDS.Among things, SSDT delivers SSIS packages templates to Visual Studio. SSIS 2012 depends on Visual Studio 2010, Like SSIS 2014 does, but SQL Server 2012 installs the Visual Studio 2010 IDE if none is present. SQL Server 2014 has failed to do so, everytime I have tried to install it. If there is no SSDT in the SQL Server 2014 installation, it might make sense. Also it is okay, because I prefers to use Visual Studio 2013, and if you are a developer like me, I guess you prefer the same.

And Now the Tricky Part

I discovered Microsoft had developed SSDT (SSDT-BI which it is called now) for Visual Studio 2012, it the most easy package to find, and I thought it was the latest. I installed it, and was hit hard in chapter 3 of the training kit 70-463. Because I has installed SSDT-BI 2012 for Visual Studio 2012.

The problem was I have created a SSIS package with the SQL Server Import and Export tool, the version which comes with SQL Server 2014. It stores it SSIS Packages in the SSIS Package format version 8, while the SSDT 2012 stores in the SSIS Package format version 6. The SSIS Package format is apparently not forward compatible. 

The pain I experienced, in chapter 3 of the training kit, was I had to create a package with the SQL Server Import and Export tool (version 2014 in my case) and add it to a SSIS Project created with SSDT 2012. The because the version numbers didn't match, result was this:

Fortunately I succeeded to find SSDT-BI 2014 well hidden at 

http://blogs.msdn.com/b/analysisservices/archive/2014/04/02/sql-server-data-tools-business-intelligence-for-visual-studio-2013-ssdt-bi.aspx

It saves SSIS Packages in SSIS Package format version 8, like the rest of the SQL Server 2014 Suite, plus it works with Visual Studio 2013. I'll expect it will work it with the rest of the training kit 70-463, or else I'll provide a solution on this blog. As bonus info, you have to select "Perform a new installation of SQL Server 2014", or you will get some kind of architecture error. Despite the option name, it only installs SSTD-BI 2014.

Enjoy