Category archive for ‘Data Modelling’ 
-
Data granularity- avoid going against the grain
In the world of data warehousing, the grain of a fact table defines the level of detail that is stored, and which dimensions are included make up this grain. Obviously, the higher the grain the better- although source systems and data volume/performance may intervene. Using the example in the Wikipedia article on fact tables, a [...]
-
Mystery or Junk data warehouse dimensions
Sometimes, when you are designing a star schema model, you'll find yourself in a dilemma. You've come up with a beautiful design, right out of the pages of a Ralph Kimball book with 5 dimensions, and 5 measures, and you are on your way to star schema heaven when suddenly the users start asking akward [...]
-
Data migration Part 3- Mapping the legacy systems
This is part three of an ongoing series that's taking a look at data migration projects. In this part we're going to talk about how important it is to know where you are starting from, before you head off on a new application journey. Understanding and mapping your legacy systems is a key success factor [...]
-
MS Access query example and comparision to Datamartist
Microsoft Access allows users to create complex queries and analyze large data sets. However, it can be complicated to use compared to Excel. In this post, I'll talk about ms access queries and the equivalent way to perform the same data transformation in the Datamartist tool- visually and simply. Microsoft Access has a clear role [...]
-
MS Access vs Excel vs Datamartist
When data analysis requirements really get tough, the tough get going- and start to seriously use databases. Let's face it, if you're considering Microsoft Access chances are what you need to get done is beyond what Excel does well, so you're looking for options. Its also likely that your IT department is unable or un-willing [...]
-
Joining the Dimension Table to the Fact Table- Purchasing Data mart (Part 5)
After we have created the dimension tables and the fact table and populated them with data the final step to getting a star schema is of course to actually join the dimension tables to the fact table. In the datamartist tool we do this with a Join block. Check out the first four parts of [...]
-
Hierarchies and Tree Structures in Dimensions- an Example Item Dimension (Part 4)
Having a way to create and manage tree structures (Hierarchies) with your dimension and fact tables is a key part of making a dimensional model in any data warehouse or data mart. Hierarchical structures lend themselves to managing a very large number of categories and we use them to create drill down paths. Check out [...]
-
Connecting the dimension table to the fact table- Vendor Example (Part 3)
In parts one and two of this series we introduced our challenge (to make a data mart to analyze the Acme Company's spending) and showed how the Datamartist tool could import millions of rows of data and then turn it into a fact table we can use in Excel. Now we need to create a [...]
-
Creating a Fact Table with the Vendor dimension Purchasing DM (Part 2)
In creating a data warehouse or data mart data model there are two key types of tables- fact tables and dimension tables. Fact tables hold the data to be analyzed, dimensional tables provide categories and analysis values that organize the data. So we have our mission from Part 1: to analyze the "Acme does everything" [...]
-
Degenerate Dimensions in Datamarts
Not all dimensions are created equal. A typical dimension is defined by a table that holds the reference data that is being joined to the fact data. So in the fact table, for example, we have the product ID, or the product code, and in the product dimension table we have a single row for [...]
