As we have been looking at data warehouse structures we reviewed data marts, and star schemas. Today we’re going to enhance the star schema, converting it into a more complicated form known as a snowflake schema. A snowflake schema begins as a star schema. As dimensions are added to fact tables, a lot of redundant data begins to emerge in […]

http://www.diffen.com/difference/Snowflake_Schema_vs_Star_Schema

Today we continue the review of different data warehousing structures. We began yesterday with Data Marts. As a quick review, a data mart is simply a table that is self contained. It is rarely joined to any other data. Everything needed to provide results for your query is often contained in a single data mart table. Sometimes there are a […]

The first data warehouse I worked on was built completely on data marts. Data marts are an anti-pattern, ignoring data normalization principles, creating a single table with repeating data in multiple rows, or having repeating columns. These tables are not normalized to the basics of even first normal form. The de-normalization is not randomly achieved. Often, query results for producing […]

Keith provides some really thought provoking comments about Data Marts, prompting me to respond with my take on the whole topic of reporting databases. Keith writes: I saw the heading on this article and before I read the article, I got stuck… What is a data mart anyway? I’m not talking about the surface definition. I’m talking about in our […]

Yesterday we reviewed some tips on how to optimize query performance through the use of # tables and indexes. The resulting query performance increase was dramatic. In our real world scenario the time dropped from 30 seconds to under a second. In our comments, AZ Jim brought up the fact that the two queries we execute could be materialized permanently […]

Last week I concluded with a high level review of the different Join Techniques Defined in the ANSI standards, as implemented in SQL Server. We looked at the Merge, Loop and Hash join methods, in preparation for the topic today on table variables and # tables. The problem, bringing back again this much argued topic, is a simple report, based […]

SQL Server has three different join techniques we’re used to hearing about. A Merge Join takes two ordered sets and walks through them, keeping the matching rows. This join only looks at records sequentially in both tables it only moves forward, never backward. If no match is found in table A, and the value in table B is Lower, then […]

There was a lot of feedback on yesterday’s editorial citing confusion or miss-represented concepts. I think I haven’t been clear in my terms, and there are terms that are nearly the same “RowVersion” = Type, RowVersions = Data Change Tracking. Perhaps this paragraph from https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx will clarify, or make the concept even more murky. This is how ADO.Net is configured […]

I’m bringing two different concepts from editorials this past couple of weeks to demonstrate a whole new topic. Previously we talked about auditing data in your database. SQL Server, by default, has the ability for you to turn on a feature called RowVersioning. When this feature is enabled, SQL Server maintains a history table of all RowVersioned tables in your […]