10 Essential Rules of Dimensional Modeling by Kimball University

Margy Ross from Kimball University writes the 10 Commandments of Dimensional Modeling. (Well she hated to use the word Commandments but I still liked it.)

The article contains pointers, most of which we do follow while designing a Data Warehouse. But, it was really good to have all of them at one place.

I am listing the Commandments here, but please read the full article by clicking on the link here...

1. Load detailed atomic data into dimensional structures.
2. Structure dimensional models around business processes.
3. Ensure that every fact table has an associated date dimension table.
4. Ensure that all facts in a single fact table are at the same grain or level of detail.
5. Resolve many-to-many relationships in fact tables.
6. Resolve many-to-one relationships in dimension tables.
7. Store report labels and filter domain values in dimension tables.
8. Make certain that dimension tables use a surrogate key.
9. Create conformed dimensions to integrate data across the enterprise.
10. Continuously balance requirements and realities to deliver a DW/BI solution that's accepted by business users and that supports their decision-making.

Create Clustered Index as Unique or Not?

Clustered Index is very important and determine the order in which data is stored in the table. A table without a Clustered Index is called a HEAP. (Try and picture a Heap in your mind and then you can visualize how data is stored in table without a Clustered Index.. Huh!!)

Clustered Index also helps the SQL Engine uniquely identify a row. Thus, if possible, always create a Clustered Index as UNIQUE. If you do not specify the UNIQUE clause while creating your Clustered Index, SQL Server automatically adds a 4 byte uniqueidentifier column to the table to make every row unique.

Thus, specifying a UNIQUE clause also helps save valueable database space.

More information can be found at the following MSDN Page.

Handy Shortcuts for SSMS

I like to add the following short-cut keys to my SQL Server Management Studio. This makes a lot of regular day-to-day commands much easier to use.

Click on Tools Menu.
Select Options Menu
Select Keyboard on the Environment Tree
add the following shortcuts

Ctrl+3 : sp_Columns
Ctrl+4 : sp_Depends
Ctrl+5 : sp_SpaceUsed
Ctrl+6 : sp_HelpIndex
Ctrl+7 : sp_HelpText


(You can add/change your own Shortcut Commands)

Click OK. You will have to re-start SSMS to use these shortcuts.

Next time if you want to see the size of a table, instead of typing sp_SpaceUsed TableName, simply select the table and press Ctrl+5 and Voila...

A list of Database Engine Stored Procedures can be found here...

Comparison of Kimball and Inmon Methods

I have always been bugged and intrigued by Kimball and Inmon methods of Data Warehouse. Some questions that always used to bother me was:
What is the Kimball Method?
How is Inmon's Datawarehouse different from Kimball's?
Which model am I working on? Strangely I did not knew this, except for the fact that it was a Dimensional Model.

I could find a plethora of articles on web. But this one by Mary Breslin is the best. "Data Warehousing Battle of the Giants: Comparing the Basics of the Kimball and Inmon Models". The article explains both the methods, does a fair analysis and comparison and also suggest which approach is best in a given scenario.

I could also find a link to the Presentation for the same article. You can download the PDF here.

Clear Log Space from Database

After large operations on the database the Log file may get filled and you may get a warning that there is no space to execute the SQL Command.

Use the following command to clear the Log File.
DBCC SHRINKFILE(LogFileName, 1)

You can get the name of the Log File with the following command
sp_helpdb databasename

DBCC SHRINKFILE is an interesting command and can also be used to shrink the Data files. Read the following MSDN article to more about this command.

View Log Size and Space Used

Use the following command to see the current log size (MB) and space used for all Databases.

DBCC SQLPERF(LOGSPACE)

The output will be as follows

Move a Partitioned Table to PRIMARY Filegroup

The following command will move the Partitioned Table to PRIMARY Filegroup. (The table will be un-partitioned then....)

1. Drop the existing Non Clustered Indexes on the Table (if any)
2. Drop the Clustered Index on the table
3. Re-Create the Clustered Index on the table specify the Filegroup as PRIMARY.
   CREATE CLUSTERED INDEX IndexName ON TableName(ColName) ON [PRIMARY]
4. Re-Create the Non Clustered Indexes (if any)