Good keys, what are they like?

A central value add of data warehouses is their ability to restore the sanity that comes from using good keys. Taking a model-agnostic view of keys, they refer to “something” that is uniquely identifiable. Depending on what you are modeling, those “somethings” have different names, for example: entities, rows, tuples, cells, members, documents, attributes, object instances, and relations between the any of the latter. Because this article is about relational databases and because I don’t want to move up to the “logical” level with you (as I promised before), I will use the term: “row” as the “something” that a key refers to, and the term “column” as the data structure that implements the key.

Moving on, the definition of a “good key” is a column that has the following properties:

It forced to be unique
It is small
It is an integer
Once assigned to a row, it never changes
Even if deleted, it will never be re-used to refer to a new row
It is a single column
It is stupid
It is not intended as being remembered by users

Grade of the Steel: Introduction

2011-05-01
Grade of the Steel SQL Server Archive
1 Comment
Data Warehouse, ETL, FusionIO, OLTP, Performance, Scale, SQL, SQL Server

There are a lot of things I want to say on this blog, so I do apologize in advance for jumping a bit between subjects. I will shortly write the next installment of my data modeling series – but before that, I want to let you know about some exciting new tests that I am working on in my copious amount of spare time Smile .

Tips for the Frequent Traveler

A bit off topic: I just stumbled on Paul Randal’s blog post about travel tips that he and Kimberly is sharing. I found it very interesting to read the way they travel. I am personally a very frequent traveler (250K miles clocked up on One World last two years) – so I wanted to share my travel tips. Following Paul’s structure:

Why Integer Keys are the Right Choice!

2011-04-26
Data Modeling
4 Comments
Data Warehouse, ETL, Keys, Master Data, Performance

The blog subject has come up a lot during my years of consulting. Source systems, traditionally composed of shoddy code and poor data modeling, will often use “natural keys”. Such keys can be of any data type, strings (CHAR/VARCHAR), DECIMAL, or other inefficient types. It is my claim that keys in a warehouse should always be integers and that it is not that difficult to implement proper keys. In this post, I will show you what the price is of using non-integer keys and let you be the judge of how much hardware you are willing to waste on making lazy key choices.

Utility functions: fn_convert_to_base and fn_nums

I will often use code to illustrate my points in this blog. Because data generation is a big part of these examples, I will take the chance to introduce a few functions that I find useful for that. I will use these functions in my examples, so refer to the Utilities category on this blog to find the source. Most of these functions are adaption from other users of SQL Server and I will do my utmost to give credit where credit is due. If you feel you are the owner of the original idea – please send me an email so I can give you proper credit.

Defining the Good Data Model

2011-04-18
Data Modeling
6 Comments
BI, Data Warehouse, Dimensional Models, Performance, Scale

Designing data models is fun – at least if you are a geek like me. But as much as I like the academic thrill of building something that is complex – I am aware that it is often humans that eventually must see and maintain my beautiful (data) model. How can we design something that humans can understand?

Humans are buggy! In general, they don’t deal well with complexity. You can blame modern education, you can scream and shout, or languish on the fact that the IT industry is riddled with incompetence, you may even throw Kimball or Inmon books at the wall in anger. But the empirical tests all show the same: the wetware is the final test of the model.

Data Warehouse Introduction

2011-04-17
Data Modeling
3 Comments
Architecture, Data Warehouse, ETL, Staging

In this blog, I will argue for and against key architectural choices that face the data modeler who wants to build a big data warehouse. I will do my utmost to avoid hand waving and references to “Logical Models”. Indeed, I find that talking about a “Logical Model”, whatever that is nowadays, distracts from the real issues that must be solved by the designer and that such model provide very little value in a real life implementations. I will therefore move directly back and forth from between conceptual (business user understandable) design and the actual implementation.

Creating custom aggregations in Analysis Services 2005

2006-12-15
SQL Server Archive
No Comments
Aggregations, Analysis Services, BI, SQL Server

Most of you are probably using the aggregation wizard to create aggregations for your cubes. If you done your homework and set attribute relationships, counts and properties right (please refer to my previous post) – the aggregation wizard will generally do a good job creating the right aggregations.

Automating build of Integration Services projects

2006-11-29
SQL Server Archive
1 Comment
ETL, Integration Services, SQL Server, Visual Studio

After our experiences with the build process in Analysis Services – expectations were low when we turned our gaze towards Integrations Services (SSIS).

Our overall experience with SSIS has generally been positive – it’s an extremely powerful tool with some interesting performance characteristics. There are some annoying flaws – especially in the Visual Studio integration. When I work with SSIS projects – three or four daily crashes of Visual Studio is not uncommon. Never fear – the power provided by the tool more than makes up for this when it comes to choosing the ETL platform. And buggyness is what service packs are for … isn’t it? 🙂

The first thing you notice when you choose “build” in Visual Studio on an SSIS projects is that Visual Studio doesn’t do anything – except of course copy files to the bin directory. Why? Well, guess you could say that the *.dtsx files contain all the information need to run the packages – there is “nothing to be done”.

Automating build of Analysis Services Projects

2006-11-28
SQL Server Archive
5 Comments
Analysis Services, Patterns, SQL Server, Visual Studio

As you may be aware, an Analysis Services project consists of the following files:

A project file (*.dwProj)
One or more Data sources (*.ds)
One or more Data source views (*.dsv)
A database file (*.database)
One or more cube files (*.cube) with their partition (*.partitions)
One or more dimension files (*.dim)

You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want – you will see why later.

Previous Page «‹6 789 ›Next Page