Datawarehouse overall

---Date Modeling Techniques
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/




---Datawarehouse Lifecycle Planning toolkit
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/


---- Naming Convention - list of abbreviations(From Kimball reference)
http://www.indiana.edu/~edss/Services/Naming/nameDisplay.pl?table=ab


---- Kimball Reference for documents related to building a Datawarehouse
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/microsoft-data-warehouse-dw-toolkit/


Data Warehouse3 toolkit by Ralph Kimball
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/




 Data warehousing Methodologies


https://blog.westmonroepartners.com/data-warehouse-architecture-inmon-cif-kimball-dimensional-or-linstedt-data-vault/


https://www.researchgate.net/publication/317229839_Herding_the_elephants_Workload-level_optimization_strategies_for_Hadoop


Types of Dimensions
https://www.vertabelo.com/blog/technical-articles/dimensions-of-dimensions-a-look-at-data-warehousings-most-common-dimensional-table-types


Rapidly Changing Dimensions
https://www.itprotoday.com/sql-server/data-warehousing-rapidly-changing-monster-dimensions


https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.whse.doc/ids_ddi_360.htm


https://www.ibmbigdatahub.com/blog/building-fast-data-warehouse-schemas-part-2


Datawarehouse 2.0 by Bill Inmon


https://gerardnico.com/data/warehouse/start


Various Data models CSU


http://www.databaseanswers.org/data_models/index.htm


https://www.semanticscholar.org/paper/Evolution-of-a-Dynamic-Multidimensional-Meta-Model-Hansen-Cruz/dd8935733b7fb57f6de643308a769c07ac77963c


https://www.semanticscholar.org/topic/Star-schema/417461




https://docs.oracle.com/cd/B19306_01/bi.102/e10084/logical.htm#BABBHEEA




 ---HR Model Very good example


http://www.databaseanswers.org/data_models/hr_platform/index.htm


https://www.databasejournal.com/features/oracle/article.php/3529531/Database-Modeling-Using-Visio.htm


https://www.opm.gov/services-for-agencies/hr-line-of-business/enterprise-architecture/hrlob_dm.pdf  ** Very good example of Project Planning


https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781782173250/7/ch07lvl1sec37/human-resources-data-model


http://tdan.com/a-universal-person-and-organization-data-model/5014


https://www.oracle.com/technetwork/developer-tools/datamodeler/sample-models-scripts-224531.html


https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-modeling-nosql-B.html


http://web.mit.edu/warehouse/metadata/stars/business_area/human_resources.html


Financial DataModels / Universal Datamodels


http://www.univdata.com/Portals/9/Publication_Articles_7_02_Financial_Serv.pdf
https://www.dwhpro.com/teradata-fsldm/


https://www.dwhpro.com/teradata-denormalization-quick-guide/




Product Hierarchy





https://www.enterworks.com/product/product-information-management-software/


https://www2.microstrategy.com/producthelp/10.4/ProjectDesignGuide/WebHelp/Lang_1033/Content/ProjectDesign/Sample_data_model.htm





Geogrpahy Hierarchy


https://stackoverflow.com/questions/4262520/data-modeling-hierarchy-of-geographic-locations


http://www.softwaregems.com.au/Documents/Student%20Resolutions/SONewbie%20DM.pdf




---- Invoice Header


https://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/


https://www.slideshare.net/nongoffna/data-warehouse-order-management?from_action=save


http://www.b-eye-network.com/view/9910


https://www.nuwavesolutions.com/degenerate-junk-dimensions/




https://www.researchgate.net/publication/237622501_Chapter_I_Conceptual_Modeling_Solutions_for_the_Data_Warehouse





http://info.kickfurther.com/7-major-things-you-need-to-know-about-an-invoice


Address in Datawarehouse


http://kimballgroup.forumotion.net/t1918-customer-with-multiple-address-types


https://www.tek-tips.com/viewthread.cfm?qid=732020


https://www2.microstrategy.com/producthelp/10.4/ProjectDesignGuide/WebHelp/Lang_1033/Content/ProjectDesign/Geography_hierarchy.htm


https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.whse.doc/ids_ddi_355.htm


https://www.rittmanmead.com/blog/2011/05/geography-hierarchies/


http://kimballgroup.forumotion.net/t1183-how-to-model-customer-in-data-warehouse




Many to Many Facts examples
https://hub.packtpub.com/solving-many-many-relationship-dimensional-modeling/


https://www.codemag.com/article/1304071/The-Baker%E2%80%99s-Dozen-13-Tips-for-Basics-of-Data-Warehousing-and-Dimensional-Modeling


https://www.nuwavesolutions.com/bridge-tables/


https://www.vertabelo.com/blog/technical-articles/five-common-dimensional-modeling-mistakes-and-how-to-solve-them - using a additional fact table








https://www.vertabelo.com/blog/technical-articles/data-warehouse-modeling-the-snowflake-schema




How to load Fact Tables


https://www.tek-tips.com/viewthread.cfm?qid=1643616


https://social.technet.microsoft.com/Forums/en-US/7cba63e1-6742-4db5-8011-44a8fea8c041/slowly-changing-facts-creation-with-scd-type-2-dims-tables?forum=sqldatawarehousing


https://martinschoombee.com/2011/11/16/temporal-fact-tables/




Temporal Modeling
https://www.linkedin.com/feed/update/urn:li:ugcPost:6497167178356318208


https://sqldusty.com/2015/10/19/data-warehouse-design-challenge-relating-a-temporal-fact-table-to-a-date-dimension/


https://sqlbits.com/Sessions/Event10/Temporal_Snapshot_Fact_Table


https://www.researchgate.net/publication/330798405_Temporal_Dimensional_Modeling




Google Search : what is a key of a fact table in a data warehouse


https://stackoverflow.com/questions/21606750/is-there-a-concept-of-slowly-changing-fact-in-data-warehouse




https://www.researchgate.net/publication/330798405_Temporal_Dimensional_Modeling






----Multiple Models
http://www.databaseanswers.org/downloads/Dimensional_Modelling_by_Example.pdf


---Kimball Books on Datawarehouse Designs


https://books.google.com/books?id=XoS2oy1IcB4C&pg=PA422&lpg=PA422&dq=customer+address+dimension&source=bl&ots=1CJ9lElOjF&sig=ACfU3U2sWQOgAAOeBF0CjnoT1vbLwbRSXw&hl=en&sa=X&ved=2ahUKEwi_styn4b7gAhUDL30KHZHjDyI4ChDoATAHegQIBxAB#v=onepage&q&f=false


https://books.google.com/books?id=XoS2oy1IcB4C&dq=customer+address+dimension&source=gbs_navlinks_s




https://books.google.com/books?id=C3G-CgAAQBAJ&pg=PA416&lpg=PA416&dq=customer+address+dimension&source=bl&ots=s5smQqrXHy&sig=ACfU3U2KmmAvpvKkpMgJUXGBcC9etOLOiw&hl=en&sa=X&ved=2ahUKEwi_styn4b7gAhUDL30KHZHjDyI4ChDoATAJegQIBBAB#v=onepage&q=customer%20address%20dimension&f=false

Data Modeling Tools

Modeling Tools

https://my.vertabelo.com/model/Rq7DHBGO83r5piJZJ16j61Tsn0oozsrU

http://www.anchormodeling.com/modeler/latest/

https://blog.codecentric.de/en/2017/07/agile-database-design-using-anchor-modeling/

<<Anchor_Modeling.pdf>>

https://anchor.teachable.com/p/an-introduction-to-anchor-modeling

https://www.red-gate.com/simple-talk/sql/bi/power-bi-data-modelling/

https://gerardnico.com/data/modeling/modeling

https://gerardnico.com/data/quality/quality

http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf

https://docs.oracle.com/cd/B19306_01/bi.102/e10084/logical.htm#BABBHEEA

Looping thru the data

https://support.microsoft.com/en-us/help/299036/how-to-loop-through-a-list-of-data-on-a-worksheet-by-using-macros-in-e

http://codevba.com/excel/for_each_cell_in_range.htm#.XMiF8ehKjIU

how to create new rows in Excel

https://powerspreadsheets.com/excel-vba-insert-row/

Datawarehouse Variations - Bigdata, Temporal Data, Etc

https://www.researchgate.net/publication/317229839_Herding_the_elephants_Workload-level_optimization_strategies_for_Hadoop

https://www.researchgate.net/publication/330798405_Temporal_Dimensional_Modeling

http://www.anchormodeling.com/?author=1

Oracle Refrences

<<Temporal Data Samples.pdf>>

Discussion about Max size of a varchar2 in Oracle

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6141473100346333010

Needs to have a initialization parameter MAX_STRING_SIZE to be able to use that

Datatype Limitations in Oracle

https://docs.oracle.com/cd/E18283_01/server.112/e17110/limits001.htm

Oracle Technology Network

https://www.oracle.com/technetwork/index.html

Oracle SQL Reference

Migrating from 11g to 12C

https://community.oracle.com/docs/DOC-991606

What is a Virtual Column

Restful Data Services

https://docs.oracle.com/cd/E56351_01/doc.30/e87809/getting-started-with-RESTful-services.htm#AELIG90198

Column level Collation and case insensitive database

https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2

Regular Expressions

Regular Expressions

https://www.regular-expressions.info/tutorial.html

Python References

How to Convert Camel Case to Snake Case or reverse in Python

CamelCase to Snake_Case Conversionin Python

https://dev.to/rrampage/snake-case-to-camel-case-and-back-using-regular-expressions-and-python-m9j

Excel References

https://exceljet.net/danger-beware-vlookup-defaults

https://xlsxwriter.readthedocs.io/index.html

How do I know if a Vlookup returns a value?

Select cell B2, copy and paste formula =IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "No", "Yes") into the Formula Bar, then press the Enter key. See screenshot: 2. Drag the Fill Handle down to the range you need to vlookup and return yes or no.

https://exceljet.net/things-you-should-know-about-vlookup

Learn Excel Formula's - https://exceljet.net/keyboard-shortcuts

Excel funtion to truncate the column names to 29 characters + number if the name of the column repeats

Macro to create spaces in CamelCase names

<<Unity_Stage_Glossary_MACRO.xlsm>>

--Formula to rename stage tables

=IF(EXACT(LEFT(F2),UPPER(LEFT(F2))),"BT_"&F2,"CT_"&F2)

how to get table name from a string

=SUBSTITUTE(RIGHT(F2,LEN(F2)-3),"_Stg","")

how to match the values of 2 cells (case sensitive)

=(EXACT(H2,I2))

How to Call a Function in an Excel Cell

https://stackoverflow.com/questions/12351339/how-to-call-vba-function-from-excel-cells-2010

Overview - VBA in Excel

https://www.excelcampus.com/vba/code-modules-event-procedures/

CamelCase to Snake_Case in Excel

https://www.mrexcel.com/forum/excel-questions/381040-camelcase-proper-case.html

Datawarehouse - Date dimension

Date Dimensions Generation Articles

MSSQL

https://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

http://www.sqlservercentral.com/scripts/Data+Warehousing/156805/

https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

Multiple Countries (Holidays and Fiscal Year Info)

** http://www.rad.pasfu.com/index.php?/archives/156-Script-to-Generate-and-Populate-Date-Dimension-Version-2-Adding-Multiple-Financial-Years.html

** http://radacad.com/script-to-generate-and-populate-date-dimension-version-2-adding-multiple-financial-years

PL/SQL Date Dimension

https://stackoverflow.com/questions/28564462/pl-sql-date-dimension-population-of-data-datawarehouse

https://analystarsenal.wordpress.com/2016/11/14/oracle-sql-script-to-generate-date-dimension-data-for-use-in-microsoft-excel-data-models/

http://oracleolap.blogspot.com/2011/01/script-for-time-dimension-table.html

** http://www.oraclenerd.com/2009/03/how-to-populate-your-time-dimension.html

** http://kimballgroup.forumotion.net/t52-date-dimension-in-oracle-with-one-sql-statement

http://www.bradleyschacht.com/date-dimension-script-with-fiscal-year/

https://dylanwan.wordpress.com/2015/10/30/generate-date-dimension-in-plsql-table-function/

https://github.com/dylanwanincorta/Calendar

--Holiday in Date Dimension

https://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/

Consider adding a special row for the unknown date or TBD date as well

Kimball for Time Dimensions

https://www.kimballgroup.com/1997/07/its-time-for-time/

Various Data ModelingLinks

https://greatobi.wordpress.com/2009/07/16/modeling-header-details/


https://dba.stackexchange.com/questions/23056/how-do-you-roll-up-header-level-values-on-a-fact-table-with-line-item-granularit


---Date Modeling Techniques
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/




---Datawarehouse Lifecycle Planning toolkit
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/


---- Naming Convention - list of abbreviations(From Kimball reference)
http://www.indiana.edu/~edss/Services/Naming/nameDisplay.pl?table=ab


---- Kimball Reference for documents related to building a Datawarehouse
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/microsoft-data-warehouse-dw-toolkit/


Data Warehouse3 toolkit by Ralph Kimball
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/




 Data warehousing Methodologies


https://blog.westmonroepartners.com/data-warehouse-architecture-inmon-cif-kimball-dimensional-or-linstedt-data-vault/


https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/


https://www.jenunderwood.com/2017/07/14/back-to-dimensional-modeling-basics/


Various Data models CSU


http://www.databaseanswers.org/data_models/index.htm




 ---HR Model Very good example


http://www.databaseanswers.org/data_models/hr_platform/index.htm


https://www.databasejournal.com/features/oracle/article.php/3529531/Database-Modeling-Using-Visio.htm


https://www.opm.gov/services-for-agencies/hr-line-of-business/enterprise-architecture/hrlob_dm.pdf  ** Very good example of Project Planning


https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781782173250/7/ch07lvl1sec37/human-resources-data-model


http://tdan.com/a-universal-person-and-organization-data-model/5014


https://www.oracle.com/technetwork/developer-tools/datamodeler/sample-models-scripts-224531.html


https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-modeling-nosql-B.html


http://web.mit.edu/warehouse/metadata/stars/business_area/human_resources.html




----Multiple Models
http://www.databaseanswers.org/downloads/Dimensional_Modelling_by_Example.pdf
---- Invoice Header


https://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/


https://www.slideshare.net/nongoffna/data-warehouse-order-management?from_action=save


http://www.b-eye-network.com/view/9910


https://www.nuwavesolutions.com/degenerate-junk-dimensions/




https://www.researchgate.net/publication/237622501_Chapter_I_Conceptual_Modeling_Solutions_for_the_Data_Warehouse





Many to Many Facts examples
https://hub.packtpub.com/solving-many-many-relationship-dimensional-modeling/


https://www.codemag.com/article/1304071/The-Baker%E2%80%99s-Dozen-13-Tips-for-Basics-of-Data-Warehousing-and-Dimensional-Modeling


https://www.nuwavesolutions.com/bridge-tables/


https://www.vertabelo.com/blog/technical-articles/five-common-dimensional-modeling-mistakes-and-how-to-solve-them - using a additional fact table




Edit Model in A browser
https://my.vertabelo.com/model/Rq7DHBGO83r5piJZJ16j61Tsn0oozsrU




https://www.vertabelo.com/blog/technical-articles/data-warehouse-modeling-the-snowflake-schema




How to load Fact Tables


https://www.tek-tips.com/viewthread.cfm?qid=1643616


https://social.technet.microsoft.com/Forums/en-US/7cba63e1-6742-4db5-8011-44a8fea8c041/slowly-changing-facts-creation-with-scd-type-2-dims-tables?forum=sqldatawarehousing


https://martinschoombee.com/2011/11/16/temporal-fact-tables/


https://www.kimballgroup.com/2008/11/fact-tables/




http://www.zentut.com/data-warehouse/fact-table/


Temporal Modeling
https://www.linkedin.com/feed/update/urn:li:ugcPost:6497167178356318208


Google Search : what is a key of a fact table in a data warehouse


https://stackoverflow.com/questions/21606750/is-there-a-concept-of-slowly-changing-fact-in-data-warehouse




https://www.researchgate.net/publication/330798405_Temporal_Dimensional_Modeling

How to load facts - contd

https://stackoverflow.com/questions/21606750/is-there-a-concept-of-slowly-changing-fact-in-data-warehouse

https://it.toolbox.com/question/efficient-way-to-update-fact-tables-in-warehouse-101110

https://dwbi1.wordpress.com/2015/02/09/populating-fact-tables/

http://www.dbta.com/Columns/Database-Elaborations/Updating-Fact-Rows-In-a-Star-Schema-Set-of-Tables-is-Not-a-Good-Practice-118541.aspx

https://dwbi1.wordpress.com/2015/02/09/populating-fact-tables/

HR DataModel Sample from www.databaseanswers.org

Visit following site for details

Borrowed from : http://www.databaseanswers.org/data_models/hr_platform/index.htm

An Access Database is available. If you have any comments or suggestions I'd be glad to hear them. The Specifications for the Data Model have been defined as an example of our Best Practice. Conceptual Data Model   Our Approach :- Step 1. Create a draft Conceptual Data Model to show the important 'Things of Interest'. Step 2. Request Stakeholder feedback on the Conceptual Data Model. Step 3. Update the Conceptual Data Model to reflect the Stakeholder feedback. Step 4. Obtain Stakeholder agreement to the updated the Conceptual Data Model. Step 5. Produce the cost estimate for Phase 1. Step 6. Discuss the cost estimate with the Stakeholder. Step 7. Plan the next Steps. We agree with Stakeholders what are the most important functional areas that should appear in this High-level Data Model. Complete Layered Data Architecture  Dezign Logical Data Model ERwin Logical Data Model