2

I'am new in DWH so I have a problem.

We have couple fact tables (5 in fact), in the first table we have Applications (when people want to buy our product they make App), then, after people make App and some checks, that's App go to another database, there App receives a new number and more checks, If everything is alright we conclude a contract (this is the second fact, Contract), App from first and second database join by UID (they have different ID), if the App did not reach the second databse (was rejected), it doesn't have UID. The third fact is SMS send, fourth Transaction, fifth operator's call.

How to join all of this?

I think so, in Facc App we have AppID (degenerate dim) from second db (during ETL I'll join two applications table by UID and insert it as one row), in Fact Contract we have AppID too, in SMS send fact containt AppID too, in Tran Fact we have contract id and Operator's call containt contract id too. Of course we have dim, some common like Customer, some not.

I thought should I join the fact tables directly or not?

Or I can aggregate what I need in FastApp, and then join the result with FactContract by AppId and use similar techniques for other facts. (here is a similar question here)

enter image description here

And what to do with big column in Fact like IP adress (from which App was created), device, email for confirm which was used, sms text, etc. There are many such columns, they are wide and they are needed for analysis, not often, but they are needed. I think split Fact on two, with link one-to-one.

Andrea
  • 11,801
  • 17
  • 65
  • 72
AlexRov
  • 21
  • 1
  • 4
  • 1
    Data model and/or DDLs with some sample data would be helpful. – demircioglu Jul 19 '18 at 18:35
  • What you need is called "Accumulating Snapshot Fact Table". https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table/ – RADO Jul 20 '18 at 05:20
  • Accumulating Snapshot Fact Table.. no, i don't think so. We will need to update Fact rows and delete old rows. For example, user create App but did not fill it completely (but row for App was created in DB1, but it has some empty columns), in week we will sent him sms with link, if he click on it and complete all steps, App in DB1 update (App row has two date columns created and updated). During ETL I'll take that row, delete old row (which I inserted one week ago) and insert new, I think it will faster than update. – AlexRov Jul 20 '18 at 08:38
  • If use Accumulating Snapshot Fact Table our Fact just exploded in size, because our row can update many many times during time. – AlexRov Jul 20 '18 at 08:39

0 Answers0