Subscribe:
RSS feed
Microsoft Business Intelligence and Data Warehousing
Process Modeling – Is this a fact or a dimension?
This is a question I see a lot on forums (and also get asked) and it’s generally based on confusion around events and processes (such as an incident, a bank application process etc). Let’s remember what the difference is between a fact and a dimension. A fact table stores a business process. A fact is therefore an […]
Posted in: Data Modeling, SQL
Date Order
November 19, 2011
I had a request recently to change the order of the date dimension in my SSAS cubes to descending, so that users could easily find the most recent dates. Users are using COGNOS Analysis Studio to slice and dice the data. The users of course could have simply dragged the date hierarchy onto the “canvas”, […]
Populating your date dimension
October 9, 2011
My friend and colleague Boyan Penev wrote a post recently on his blog regarding what a date dimension table should contain: http://www.bp-msbi.com/2011/09/building-a-date-table/ I’ve got a script that I’ve been using for a while to populate a date dimension table that I thought worth posting. I’ve not included fiscal periods in this script. Living in Australia this […]
Posted in: Data Modeling, SQL
Tracking History with Slowly Changing Dimensions
September 24, 2011
In this post I’d like to show a few of the different ways to maintain history. As anyone who has been in data-warehousing for a while can attest to, the two most common scenarios that business users want to see are the data as it was (Kimball Slowly Changing Dimension Type 2) , or as-is […]
Posted in: Data Modeling, SQL
Dimensional Modeling Worst Practices
May 16, 2011
As a consultant I see a lot of BI/DW solutions with various clients, and am often called in to fix performance issues. Often times developers/architects will ignore the Ralph Kimball methodology, or try to “improve it” by breaking a few rules. So I thought it might be worthwhile for me to post some of the […]
Posted in: Data Modeling, SQL
SSRS Sparkline report using MDX
March 23, 2011
In this post I am going to describe how to create a report with Sparklines using an MDX query. In my current role I do a lot of Proof Of Concept pre-sales work for different industries, and I’ve really grown to love sparklines. I think they are fantastic in giving users an understanding of how […]
Using the DatePicker in SSRS with MDX
March 22, 2011
This post, continuing on from the previous post https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/, shows how to use the DatePicker functionality within SSRS as a parameter for an MDX query. First we set up the Date parameter, which we will call pDate. We change the data type to Date, and leave all other tabs with the default settings. We then […]
Using a Date Parameter in SSRS with MDX
March 22, 2011
There are two ways of using a date parameter within SQL Server Reporting Services for reports using SSAS as the source. I’d firstly like to thank my friend Boyan Penev for helping me out with some MDX questions I’ve had – please visit his excellent blog: http://www.bp-msbi.com/ The first method is to use a MDX […]
Data Modeling Conundrum Follow up
March 17, 2011
As a follow up to my previous post on a Data Modeling Conundrum in January (see https://jsimonbi.wordpress.com/2011/01/16/data-modeling-conundrum/) I left it open as to what the appropriate dimensional model should be. To recap from my first post, let’s say that I’m trying to track Marine Incidents. Zero or many vessels may be involved in an incident, […]
Posted in: Data Modeling, SQL
SQL Hierarchy Comparative Performance
March 1, 2011
This is the last post I will be doing on SQL Hierarchies. Since I’ve covered the most common methods for dealing with hierarchies I thought it worthwhile to do some performance comparisons. I will be testing displaying the descendants of the root node, as well as testing aggregations from a sales table for both the […]
Posted in: Data Modeling, SQL
John Simon on BI 
November 23, 2011
8