![]() ![]() I think one of the primary reasons is integration. There are a lot of tools that provide adhoc query (OLAP) capabilities so performance the performance of analysis isn’t a real consideration (after all, they could just use an OLAP engine over the OLTP right?). Riddle me this then, if the source application (OLTP) provides reporting why do we need a data warehouse? Show reports at a particular point in time? Maybe – (although a lot of users struggle with this and tend to think in current terms). So, if we are going to add value (as data warehousing professionals), we still need the dimension to provide this.Īll business applications offer some form of reporting – if you’ve ever seen an information maturity chart, it is the first stage of maturity (see below thanks to an EMC2 slide deck). ) and all of these things are defined and stored in the dimension. Consider a customer dimension – what adds values in analysis? It is often the supplementary data (age group, segment profile, status classification, targeted customer …. What the star gives is a modeled perspective of a particular part of the business. Forget about historic values for a moment – raw information is in the source, if the user wanted that you could give it to them no problem. ![]() Of course this is my opinion but think about it for a moment. The real power of the dimension (and to a lesser extent the fact) is that it adds business context that does not exist (or can not be easily calculated). The star (or at least the principle) is still required. All these products rely on uniqueness between separated tables so we still require the surrogate to enforce and deliver uniqueness. So lets leave it to the presentation tool – pick one, Power BI, SSAS, Tableau, Qlik, Jedox, …. Remember that this has to occur for every record in the fact – (and yes they are those LONG narrow tables)! I suspect that performance would degenerate so quickly both the BI users and the Ops users (remember that this is occurring on the OLTP) would walk away in droves. Interesting idea but not something I’d take on litely. How could you solve this with temporary tables? Well, there may be the thought that you could concatenate the tables primary key and the records start date for uniqueness and then determine what (dimension) record is applicable to a fact record via a query. A temporal table does not provide you the capacity to do this – all it can do is provide the data to construct the star. This is the fundamental design of the star schema. The products surrogate key (not the Product Id or Code) is used in the fact. For example, if I sold a product on a date, I need to look up the product dimension and determine which version of the product was applicable on that date. Using the surrogate uniquely identifies an instance of a dimension at a point in time and therefore, state of the dimension can be precisely identified for the fact record. I will focus on the star schema design since most references explicit refer to changing dimensions (however we can apply these ideas to other methodologies).Ī fundamental construct of the star schema is the surrogate key (almost as fundamental as the concept of dimension and fact). So lets call out some issues with reliance on temporal tables as a data warehouse replacement (and bring some data warehouse assumptions to the surface). Those that blindly follow the sales pitch “I don’t need a data warehouse” because I’ve got history tracking or perhaps “yeah, I’ve got a data warehouse – its a history tracked OLTP” will ensure their users cant get the data they need. However, there is (sadly) a lackingĪmount of discussion about how the feature can be incorporated into the Data Warehouse. The idea’s of temporary tables (recording every change to a table in an table _History table) is a cool feature – don’t get me wrong, I think its great. Microsofts own content ( here) recommends that there are huge productivity benefits in Slowly Changing Dimensions because you can compare the same key at two points in time. One post (which motivated me to write this) claimed that this breakthrough would mean we no longer needed to store historical values – an interesting interpretation of a Data Warehouse to say the least. With the release of SQL2016 just around the corner, there are an increasing number of posts appearing about new features that mean I don’t have to work.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |