A date dimension is usually seeded with all possible valid dates, and any fact with a date outside of that range is assigned an unknown, invalid, or out-of-range dimension key (very often –1). However, we are building not only a reporting data warehouse, but also an operational data store (ODS) that will be used for enterprise application integration (EAI). Therefore, we need to retain values that were received out of range, even if they are clearly wrong or invalid. For instance, a transaction dated in 2092 (and, yes, we do have some; most likely, the user meant 2002 or 2012) must record that in our ODS so we can show it to a user, even if it was obviously a typographical error. It is not our job to correct these errors, but to show what happened. It is valuable information to someone to see that date on a report and investigate what happened so it can be corrected.
We have seeded a wide range of dates (2000-2013) in our dimension table, but we can never seed every possible date that might come in, even if we were to seed ever date from January 1, 1 AD to December 31, 9999. What would we then do with a transaction date of February 29, 2011? Or March 99, 2012? Some of these dates come in on ANSI X12 transactions which generally mandate a YYYYMMDD format for dates; but what do we do when such a transaction comes in with a date of “ABCDEFGH”? We cannot reject the entire transaction out of hand due to an error in one field, and yet, if someone wants to know what was in that particular field of that particular transaction, we should show them so they can go and complain to whoever is producing such invalid dates.
Our solution is to treat date like any other data-driven dimension: if a date comes in that is not yet in the dimension table, add it.
We are currently doing this in our initial entry stage: we scan the data source and select all distinct date values. In SSIS, we then perform a lookup of the date value in the date dimension table, directing all unmatched values to a destination which is the date dimension table. This will ensure that whatever date appears in the subsequent fact data flow will already be populated in the dimension table. We retain the fallback of assigning –1 to any date value in the fact flow that is not found in the dimension, but this should never happen, and we can audit for any –1 date keys to let us know that something in our data warehouse has gone wrong.
One of my concerns with this approach was performance. How long does it take to scan the hundreds of millions of rows in the data source to pick out the distinct values? I thought it might be an hour or more, but figured if it came back in 10 or 15 minutes we could work with it. This was our first iteration, so we would be modeling our processes for other data-driven dimensions off this one, so we would expect to be doing this unique scan for every dimension. I kicked off the query (a SELECT DISTINCT … UNION SELECT DISTINCT …), to run it in the background to see, and it came back in 28 seconds! I ran it again to make sure, and it came back in even less the second time. I was amazed that our source database could scan over 260 million rows in under 30 seconds. Without indexes on those columns, by the way, so it had to have been doing a full table scan. (I do not have explain plan permissions in our source systems to confirm.)
With performance like this, we are easily able to perform the necessary SELECT DISTINCT scans to populate our dimensions prior to pulling in the facts. I had assumed that this approach would have been been prohibitively slow, but as I explained in a previous blog post, “Write What You Mean First”, rather than assume, test.
Showing posts with label data warehousing. Show all posts
Showing posts with label data warehousing. Show all posts
Saturday, July 7, 2012
Thursday, July 5, 2012
Long and Thin: Iteration 1
Josh and I completed our first iteration of our data warehouse today! We started Tuesday, took Wednesday off for the Independence Day holiday, and then completed it this afternoon. All told, we probably only put four or so hours of pair programming into it, for a total of eight man-hours. And we have a working data warehouse!
Granted, it only has one measure (two if you count the bonus “count” measure SSAS throws in for free) and two dimensions (one if you think it’s double counting to use the same dimension in two different roles), but it does contain over a quarter of a billion fact rows, and loads in under two hours. If this were our final product, we could get the load down to a few minutes (straight from source to SSAS), but we knew this isn’t our final product, so we built some additional steps into our data warehouse to accommodate our future plans for expansion.
It is not uncommon for data warehouses to land and stage their data in multiple steps along the way to a unified dimensional model (UDM). Indeed, I expect that it is very rare for any data warehouse of even medium complexity to not employ at least one intermediate stage on the way to the UDM. In our case we are not only data warehousing, but also facilitating enterprise application integration (EAI) throughout our parent organization. We therefore stage our data in an operational data store (ODS) that will serve as the hub for our EAI processes. The ODS will be a cleansed and enhanced normalized relational representation of the data using conformed dimensions for many of its foreign keys. The use of the conformed dimensions will be key to standardizing across both operational applications that consume the ODS and the enterprise reporting. We therefore built our first data warehouse iteration with four staging steps associated with four staging steps we know we will have in our final data warehouse:
This means we copied and stored our entire data set (260-odd million rows) four times on its way to the MOLAP cube. Writing to these databases was by far the bulk of the time. We could copy the data between stages in less than half an hour each. In this our first iteration, the data in each stage was identical, so the only real processing was done in the first stage, which created the conformed dimension tables and performed the foreign key lookups for the facts. Still, the time difference between that first stage and the others was negligible.
Amazingly enough, once the data was in our UDM database, SSAS was able to read the full database and process it into a MOLAP cube in around six minutes!
SSIS does not by default display start and end times of its packages. In our first iteration, we have not yet set up logging and timing infrastructure for our processes — we were running them manually through the debugger — so we do not have exact timings. We do, however, want records, logs, and timings so we can evaluate the impact of our future decisions on the behavior of our data warehouse process, so it is likely that out second iteration, rather than adding data, will add this infrastructure.
Honestly, I am looking forward to our next iteration, and I have not had such enthusiasm for our data warehouse since I took it over six months ago. Dave Rodabaugh’s advice was excellent: go long and thin. Meaning iterate small incremental improvements through the entire assembly line rather than engineering to perfection each stage in a waterfall-like process. Currently, we have no idea how long our data warehouse rewrite process will take (well, we do have our guesses), but we expect to have several iterations under our belts in short order (4-6 weeks), and so we will then have an idea as to how long it takes to do certain things. We should then be able to roadmap out the things we do want to do and attach time estimates to them. These estimates will not be perfect (“prediction is very difficult, especially about the future”), but they will be a whole lot better than our current guesswork.
Granted, it only has one measure (two if you count the bonus “count” measure SSAS throws in for free) and two dimensions (one if you think it’s double counting to use the same dimension in two different roles), but it does contain over a quarter of a billion fact rows, and loads in under two hours. If this were our final product, we could get the load down to a few minutes (straight from source to SSAS), but we knew this isn’t our final product, so we built some additional steps into our data warehouse to accommodate our future plans for expansion.
It is not uncommon for data warehouses to land and stage their data in multiple steps along the way to a unified dimensional model (UDM). Indeed, I expect that it is very rare for any data warehouse of even medium complexity to not employ at least one intermediate stage on the way to the UDM. In our case we are not only data warehousing, but also facilitating enterprise application integration (EAI) throughout our parent organization. We therefore stage our data in an operational data store (ODS) that will serve as the hub for our EAI processes. The ODS will be a cleansed and enhanced normalized relational representation of the data using conformed dimensions for many of its foreign keys. The use of the conformed dimensions will be key to standardizing across both operational applications that consume the ODS and the enterprise reporting. We therefore built our first data warehouse iteration with four staging steps associated with four staging steps we know we will have in our final data warehouse:
- An initial staging area for assembly of conformed dimensions prior to loading the ODS.
- The operational data store.
- A staging area for data being assembled between the ODS and UDM.
- A relational database containing the unified dimensional model, which will be consumed directly, without modification, by SSAS.
This means we copied and stored our entire data set (260-odd million rows) four times on its way to the MOLAP cube. Writing to these databases was by far the bulk of the time. We could copy the data between stages in less than half an hour each. In this our first iteration, the data in each stage was identical, so the only real processing was done in the first stage, which created the conformed dimension tables and performed the foreign key lookups for the facts. Still, the time difference between that first stage and the others was negligible.
Amazingly enough, once the data was in our UDM database, SSAS was able to read the full database and process it into a MOLAP cube in around six minutes!
SSIS does not by default display start and end times of its packages. In our first iteration, we have not yet set up logging and timing infrastructure for our processes — we were running them manually through the debugger — so we do not have exact timings. We do, however, want records, logs, and timings so we can evaluate the impact of our future decisions on the behavior of our data warehouse process, so it is likely that out second iteration, rather than adding data, will add this infrastructure.
Honestly, I am looking forward to our next iteration, and I have not had such enthusiasm for our data warehouse since I took it over six months ago. Dave Rodabaugh’s advice was excellent: go long and thin. Meaning iterate small incremental improvements through the entire assembly line rather than engineering to perfection each stage in a waterfall-like process. Currently, we have no idea how long our data warehouse rewrite process will take (well, we do have our guesses), but we expect to have several iterations under our belts in short order (4-6 weeks), and so we will then have an idea as to how long it takes to do certain things. We should then be able to roadmap out the things we do want to do and attach time estimates to them. These estimates will not be perfect (“prediction is very difficult, especially about the future”), but they will be a whole lot better than our current guesswork.
Subscribe to:
Posts (Atom)