Thursday, July 15, 2010

Data Warehouse vs. Data Mart

II'm currently working on a Business Intelligence project and depending on who I am talking to we are either building a Data Mart or a Data Warehouse and everyone's definitions of both seem to be vastly different. Since this makes communication a bit difficult at times I took it up on myself to attempt to clarify the difference between a mart and warehouse.

My project is following the Kimball approach of dimensional modeling so I tailored my definitions to this approach. Keep in mind that the detailed definitions differ depending based on whether you're talking about the top-down (Inmon) approach or the bottom-up (Kimball) approach. The differences are mostly around the modeling techniques and whether or not the data warehouse is in 3NF or de-normalized.

My non-technical description of data mart and data warehouse are as follows.

A data mart is typically a single star schema representing a specific subject area. Sales is a good example of this and Manufacturing is another.  A data warehouse contains the subject specific data marts and attempts to tie an entire organization’s subject data together using conformed dimensions (attribute data that can be used consistently across multiple facts/marts/star schemas).

That is the very simple description I provided the group I am working with. That said, I still needed to determine if we were building a data mart or a data warehouse. From my definition you would think it would be an easy thing to figure out. Since I tend to overthink most things in life I struggled a bit with this.

The database I'm building to support the Business Intelligence (BI) solution consists of subject areas such as Orders, Complaints, Deliveries, and about 4 more subject areas. They will each have their own fact table and share a number of dimensions. Based on that I would say it is a data warehouse because it is a set of data marts/start schemas providing data across numerous subject areas and sharing conformed dimensions. However, this solution is part of a commercial software product that supports Restaurant Order Taking and is not being developed for or within a specific organization. So now I believe it's a question of perspective. From the customers/restauranteur's perspective, the order taking application and the new BI add-on is simply a small part of a much larger picture. The data contained with the mart/warehouse might be considered a single subject area to this organization. The subject area would be Order Taking or more specifically Web and/or Call Center Order Taking. The customer might have other data around accounting, human resources, inventory, supply chain, etc. The solution I'm building could just be part of a bottom-up enterprise data warehouse at some point at which point it would be considered a data mart by the purchasing organization.

So which one is it? I believe you can argue that it's both. However, I believe we must look at it from our perspective of my client who is a software products company. My client sells a suite of order taking products to the restaurant industry. From within the software product company this is a data warehouse that brings together all aspects (subject areas) of their products.

You could argue that it doesn't matter and that this is purely a symantics or philisophical debate. Or possibly I've missed the boat. What do you think?

In relation to the product and the company that owns and sells this product to restauranteurs, this solution can be considered a data warehouse as I stated above but what about looking at it from the customers (restaurant owner) perspective?

will be an add-on to I would say it is a data warehouse because it captures data across the solution and consists of many subject areas.

Monday, June 21, 2010

DISTINCT or GROUP BY?

GROUP BY and DISTINCT can return the same results when looking for uniqueness in a resultset. Consider the Sales table below.

Store     Product        Date

-------------------------------------
A          Monopoly    1/10/2010
B          Connect 4    1/12/2010
A          Monopoly    1/14/2010
A          Battle Ship   1/16/2010
B          Othelo         1/16/2010
 
If you wanted to know what games each store sold from 1/10/2010 through 1/16/2010 you could write either of the following queries:

SELECT DISTINCT
    Store,
    Product
FROM
    Sales

-OR-

SELECT
    Store,
    Product
FROM
    Sales
GROUP BY
    Store,
    Product

So which one?? I hate to admit it but back when I first got into SQL performance tuning I was working in an Oracle 8i environment. If I remember correctly, GROUP BY performed better than DISTINCT for one reason or another. Well in the days of SQL Server 2005 and 2008, GROUP BY and DISTINCT perform equivalently. Therefore, my rule of thumb is to use DISTINCT when no aggregations on additional, non-unique columns are necessary. If you just want unique combinations of store and game use distinct. If you want to know how many of each game were sold by store then use GROUP BY.

Wednesday, June 16, 2010

Unique Constraint or Unique Index?

A few things to keep in mind when deciding whether you want a unique index or constraint.

1) The index creation options other than FILLFACTOR that are available for a unique index are not available for a unique constraint.

2) A unique key can be referenced by a foreign key constraint but a column with a unique index cannot be referenced by a foreign key constraint.

3) A less subtle difference is related to the timing of validation. Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification. Often times indexes are validated at the end of a large modification. As a result it will take longer for a failure to occur with a rollback at the end of the modification.

SQL Formatter Is Great for Auto Generated SQL

Ever had to try to decipher SQL that was generated by a report writer or sql generation tool such as Cognos Framework Manager? How about trying to read through a co-worker's messy SQL? Not fun.

One of my favorite tools is the Instant SQL Formatter. It will clean up any valid SQL statement based on user configurable options. I usually just stick with the defaults. It's a great tool and it's free. Can't beat that. One of the first things I do when I set up a machine at a new client site is to add it to my favorites in IE.

SSIS Extracting Reading from Oracle 10g

Here are a few hints if your extracting data from an Oracle database using SSIS.

1) Use Oracle 11g client. The 10g client has bugs around the naming of the Program Files directory on 64-bit machines. It doesn't like the parantheses in Program Files (x86). There are workarounds for using the 10g client but it's been my experience that it's not worth the effort.

2) Make sure to install the 32-bit client for running SSIS in the IDE (debug mode) and the 64-bit client for the 64-bit SSIS runtime.

3) Use the Attunity connector for Oracle. This component can extract data up to 100 times faster than out of the box OLE DB for Oracle provider.

Wednesday, May 12, 2010

How To: Shrink a Log File

Are you in a dev environment and by screwing around with large dml statements you've created a monsert log file that you need to truncate and are not worried about loss of data? Is your database in Simple Recovery Mode? If you answered yes to these questions . . . fire away:

DBCC SHRINKFILE (<Log Name>, 0, TRUNCATEONLY)

If you get an error stating that the log file is in use but you have an immediate need to truncate the log file and don't want to wait you can also do the following:

1) Detach the database
2) Delete the log file from the file system
3) Attach the database and remove the the reference to the log file prior to completing the process in the UI

Upon attach, SQL Server will create a new log file at the minimum size specified.

Tuesday, April 13, 2010

Don't Forget to Clear Your Buffer!

Sounds like a Colon Cleanse pitch no?

Well it's not. One of the most important things you can do when performance tuning is to remember to clear the procedure cache between test cases.

Don't be fooled by the word procedure. The procedure cache deals with almost all queries submitted to the SQL Server engine, not just those that live in stored procedures. It stores compiled execution plans for later use which helps speed things up by not having to regenerate execution plans when one is already available.

Back to clearing . . .

It's important to clear the cache between query submissions so that we start from scratch when we submit our new query. If we have "left-over" plans in the cache the optimizer won't necessarily create a newly optimized plan thus corrupting your tuning/testing efforts.

DBCC FREEPROCCACHE will clear all execution plans from the cache causing all subsequent SQL statements, stored procs or not, to be recompiled the next time they run.

It's also helpful to run DBCC DROPCLEANBUFFERS to clear the data buffers. This will ensure more accurate testing as all queries will have to retrieve their data from disk. Running CHECKPOINT prior to DROPCLEANBUFFERS will move all dirty data pages to disk, even further ensuring accurate testing results.

http://www.devx.com/tips/Tip/14401