[vc_row][vc_column][vc_column_text]Author: Moises Inestroza
The business intelligence is an array of methodologies, software and technologies which provide inside and well-structured information to companies and organizations that works as support for taking decisions.
It allows to get data related to entry into new markets, product offering, removal of information islands, financial controls, cost optimization, production planning, customer profile analysis, products profitability and so on, to analyze them in order to turn them into knowledge.
In practice, the business intelligence is a determinant factor to succeed and its implementation wouldn’t be so easy if we didn’t have an instrument as the Kimball Methodology, for the construction of Datawarehouse circumscribed to the company scope in an integrated and steady way but variable over time.
As a result of our experience on the Datamart design for datawarehouse system based on the Kimball Methodology we have rescued some notes and suggestions we will present in the following article which we consider relevant for those who want to implement and explore solutions of that type:[/vc_column_text][slzcore_item_list_sc icon_type=”02″ array_content=”%5B%7B%22content%22%3A%22Once%20the%20process%20that%20will%20be%20designed%20a%20OLAP%20solution%20(database%20oriented%20to%20analytic%20processing)%20is%20selected%2C%20it%20is%20necessary%20to%20perform%20an%20analysis%20and%20create%20a%20OLTP%20diagram%20(database%20oriented%20to%20transaction%20processing)%20of%20each%20implicated%20model.%20To%20understand%20clear%20enough%20the%20dynamic%20of%20these%20data%20by%20verifying%20through%20consulting%20scripts%2C%20It%20will%20be%20crucial%20to%20make%20decisions%20of%20OLAP%20model.%20The%20diagram%20must%20embody%20all%20the%20structures%20to%20consider%2C%20that%20is%20to%20say%2C%20all%20the%20information%20that%20is%20wanted%20to%20remove%20to%20datawarehouse%20and%20its%20relations.%22%7D%2C%7B%22content%22%3A%22To%20identify%20correctly%20the%20%E2%80%9CGrain%E2%80%9D%20where%20the%20information%20is%2C%20in%20other%20words%2C%20the%20minimal%20detail%20level%20to%20get%20a%20certain%20measure%20or%20identify%20certain%20event%20that%20has%20occurred.%22%7D%2C%7B%22content%22%3A%22To%20carry%20out%20an%20analysis%20to%20create%20a%20OLAP%20model%20which%20specifies%20the%20dimension%20tables%20and%20resulting%20events%2C%20preferably%20proposed%20in%20a%20%E2%80%9CStar%20Topology%E2%80%9D.%20%20In%20this%20data%20model%20all%20the%20information%20must%20come%20together%20from%20different%20identified%20sources.%20%22%7D%2C%7B%22content%22%3A%22It%20is%20advisable%20to%20name%20the%20tables%20with%20the%20appropriate%20prefix%20according%20to%20each%20table%20OLAP%2C%20(DIM%2CFACT)%20nature.%20The%20names%20of%20the%20DIM%20tables%20(dimensions)%20have%20to%20end%20up%20in%20singular%2C%20as%20for%20the%20FACT%20tables%20(facts)%20in%20plural.%22%7D%5D” icon_fw=”fa fa-first-order”][ultimate_info_table package_heading=”Importance of OLAP (DIM, FACT) tables”]
| As we know, OLAP model is made up of dimension and fact tables. The dimension tables (DIM) are tables that contain attributes or fields which are used to restrict and gather stored data in a fact table (FACT).
For those small catalogs that represent little or no frequency over time (countries, category). These ETL logic is basic. It consists on cleaning and reloading the data from the source. Theoretically, these types are known as SCD type 1(Slow Change Dimension) We wish to keep a versioning of dimensions in order to have a tracking of changes in values in certain ETL logic entity. The current registration has to be compared in the DWH to the OLTP current registration source. For example, in the case of a customer who has a Civil Status attribute which at certain point is “Single” but later his/her token gets updated to “Married”. We have to keep both customer’s versions, just in case we want to analyze his/her transactions later by comparing his/her behavior when he/she was Single or Married. Upon finding any difference in some of his/her attributes, a new registration is made in the DWH dimension with a new subrogated key and feeding 4 additional control attributes: VERSION, CURRENT_VERSION (Flag with 0 for No and 1 for Yes), DATE_SINCE (Timestamp date since the new version is valid). |
[/ultimate_info_table][slzcore_item_list_sc icon_type=”02″ array_content=”%5B%7B%22content%22%3A%22It%20is%20necessary%20to%20create%20subrogated%20keys%20for%20all%20the%20dimensions%2C%20that%20is%20to%20say%20a%20new%20key%2C%20a%20unique%20correlated%20value%20different%20from%20the%20natural%20key%20which%20comes%20from%20the%20registration%20in%20the%20OLTP%20source%20system%20that%20is%20also%20kept.%20%22%7D%2C%7B%22content%22%3A%22In%20the%20fact%20table%20must%20appear%20all%20the%20events%20or%20transactions%20that%20happen%20such%20as%20sales%2C%20payments%2C%20purchases%2C%20logs%20and%20so%20on.%20It%20is%20advisable%20to%20include%20from%20each%20linked%20dimension%2C%20not%20only%20your%20subrogated%20key%20but%20also%20your%20natural%20key%20since%20at%20certain%20point%2C%20the%20registration%20in%20the%20dimension%20may%20have%20changed%20the%20version%20so%20there%20have%20occurred%20transactions%20linked%20to%20the%20previous%20version%20and%20the%20current%20version.%20By%20having%20a%20natural%20key%20all%20the%20facts%20linked%20to%20certain%20entity%20could%20be%20identified%20more%20easily%20without%20needing%20to%20travel%20to%20the%20dimension.%22%7D%2C%7B%22content%22%3A%22The%20whole%20DWH%20table%20must%20have%20a%20timestamp%20which%20allows%20to%20determine%20what%20date%2C%20time%2C%20minute%20and%20second%20certain%20register%20was%20created%20in%20the%20database.%20This%20is%20very%20useful%20when%20for%20supporting%20and%20monitoring%20as%20well%20as%20audit%20purposes.%22%7D%2C%7B%22content%22%3A%22In%20every%20dimension%20default%20registrations%20must%20be%20created%20for%20those%20cases%20where%20the%20information%20is%20not%20available%2C%20does%20not%20apply%20or%20simply%20does%20not%20exist%20so%20distinguish%20those%20scenarios%20in%20the%20facts.%20To%20this%20effect%20it%20has%20to%20be%20inserted%20into%20each%20dimension%20and%20must%20be%20used%20with%20subrogated%20keys%20%20and%20natural%20keys%2C%20values%20that%20mustn%E2%80%99t%20match%20with%20the%20real%20keys%20values%2C%20for%20example%3A%20(0)value%20for%20the%20case%20N%2FD%20%2C%20Not%20defined%2C%20the%20value%20minus%20one(%20%20-1)%20for%20the%20case%20N%2FA%2C%20Not%20applicable)%20and%20the%20value%20minus%20two%20(-2)%20to%20be%20determined.%20(TBD)%22%7D%2C%7B%22content%22%3A%22It%20is%20necessary%20to%20be%20very%20aware%20of%20how%20to%20handle%20the%20Null%20cases%2C%20suggesting%20to%20assess%20the%20following%20alternatives%3A%20to%20leave%20the%20Null%20value%20in%20numeric%20variables%20type%20amount%2C%20counting%20and%20fees%20since%20the%20aggregation%20functions%20already%20know%20how%20to%20handle%20the%20Null%20cases%20so%20if%20it%20is%20changed%20to%200%20value%2C%20it%20won%E2%80%99t%20be%20possible%20to%20determine%20which%20are%20the%20null%20cases%20or%20which%20are%20the%20true%20cases%20with%20(0).%20If%20%20there%20is%20a%20Null%20in%20the%20alphanumeric%20fields%2C%20leave%20it%20%E2%80%98N%2FA.%20As%20for%20the%20foreign%20%20id%20dim%20there%20mustn%E2%80%99t%20be%20left%20any%20Null%20value.%20It%20has%20to%20be%20aiming%20at%20the%20default%20registration%20of%200%2C-1%2C-2.%22%7D%2C%7B%22content%22%3A%22To%20have%20a%20%E2%80%9CTime%E2%80%9D%20dimension%20with%20date%20pre-calculation%20already%20made%20so%20this%20way%20%20saving%20those%20exploiting%20the%20DWH%20information%20from%20this%20processing%20task.%20The%20time%20dimension%20could%20have%20the%20following%20information%3A%22%7D%5D” icon_fw=”fa fa-first-order”][/vc_column][/vc_row][vc_row][vc_column width=”1/4″][/vc_column][vc_column width=”3/4″][slzcore_item_list_sc array_content=”%5B%7B%22content%22%3A%22Month%2C%20year%2C%20first%20day%20of%20the%20month%22%7D%2C%7B%22content%22%3A%22Full%20date%20including%20the%20name%20of%20the%20day.%20%22%7D%2C%7B%22content%22%3A%22The%20short%20name%20of%20the%20day%2C%20the%20week%20day%20in%20numbers.%20%22%7D%2C%7B%22content%22%3A%22The%20short%20name%20corresponding%20to%20the%20day%20of%20the%20week.%22%7D%2C%7B%22content%22%3A%22Weekend%20flag.%22%7D%2C%7B%22content%22%3A%22Flag%20whether%20it%20is%20a%20workable%20day%20or%20not%2C%20that%20is%20to%20say%20if%20they%20are%20working%20days.%22%7D%2C%7B%22content%22%3A%22The%20day%20of%20the%20month%20in%20number.%22%7D%2C%7B%22content%22%3A%22The%20day%20of%20the%20year%20in%20number.%22%7D%2C%7B%22content%22%3A%22The%20week%20of%20the%20month%20in%20number.%22%7D%2C%7B%22content%22%3A%22The%20number%20of%20week%20of%20the%20month.%22%7D%2C%7B%22content%22%3A%22The%20week%20of%20the%20year.%22%7D%2C%7B%22content%22%3A%22The%20number%20of%20days%20the%20month%20has.%20%22%7D%2C%7B%22content%22%3A%22The%20end%20of%20the%20month%20date.%22%7D%2C%7B%22content%22%3A%22The%20month%20of%20the%20year.%22%7D%2C%7B%22content%22%3A%22The%20name%20of%20the%20month%20of%20the%20year.%20%22%7D%2C%7B%22content%22%3A%22The%20name%20of%20the%20shortest%20month%20of%20the%20year.%22%7D%2C%7B%22content%22%3A%22The%20combination%20of%20both%20year%20and%20month%20in%20numbers.%22%7D%2C%7B%22content%22%3A%22The%20year%20quarter%20which%20corresponds%20a%20certain%20date.%22%7D%2C%7B%22content%22%3A%22The%20year%20quarter%20name%20which%20correspond%20to%20a%20certain%20date.%22%7D%2C%7B%22content%22%3A%22The%20year%20quarter%20termination%20date.%22%7D%2C%7B%22content%22%3A%22The%20year%20semester%20which%20corresponds%20to%20a%20certain%20date.%22%7D%2C%7B%22content%22%3A%22Flag%20if%20it%20is%20month-end%20closing.%22%7D%5D”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]Having outlined the notes at the present article, we hope we have helped you to have a better understanding and significance of a datawarehouse functioning in order to be corporate successful. Finally we put Go Consultores to you orders where any of our teamwork will be glad to present you our service.[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]
[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_custom_heading text=”REQUEST MORE INFORMATION CONTACT US!” font_container=”tag:h2|text_align:center” use_theme_fonts=”yes”][contact-form-7 id=”1283″][/vc_column][/vc_row]