Data Model for Analytics Jack G. Zheng Spring 2022 http://zheng.kennesaw.edu/teaching/it3703/ IT 3703 Intro to Analytics and Technology Overview • What is data model? • Compared to similar terms: data structure, data architecture, schema, semantic layer • Why do we need data model and schema (in analytics)? • Major types of data models 2 This lecture notes provide some basic overview of data model and related concepts. It does not go into details of each data model or data modeling techniques. Students will learn and practice more in other courses like CSE 3153 and IT 4713. Data Model • A model is a structured way of representing, describing and explaining things (person, organization, process, system, data). – Process model – System model – Data model – Knowledge model – Business model • Data model is about representation of data, with a set of business concepts and rules. – A data model conceptualizes data elements and standardizes how the data elements relate to one another • https://en.wikipedia.org/wiki/Data_model – How is data grouped and associated? What is this data about? How are they related? • Data models depict and enable an organization to understand its data assets through core building blocks such as entities, relationships, and attributes. These represent the core concepts of the business such as customer, product, employee, and more. – https://www.dataversity.net/data-modeling-vs-data-architecture/ 3 Data Model Examples • E/R (entity-relationship model) is an example of a conceptual data model. • An ER model describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between entities (instances of those entity types). – https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model • It defines data as (or groups data using) – entities – relationships 4 Three Levels of Data Model • Three levels: conceptual vs. logical vs. physical data models • The complexity increases from conceptual to logical to physical. • An example in relational database design http://www.1keydata.com/datawarehousing/data-modeling-levels.html 5 Image from https://www.slideshare.net/Dataversity/ldm-webinar-data-modeling-business-intelligence Basic Elements in Data Models • Entity – Object, thing, activity, event • Attribute – Data that describes entities or relationships • Relationship – How entities are associated • Naming – Names of entity, attribute, and relationship • Other more specific rules (often defined at lower levels like logical or physical level); metadata – Data type, domain (value ranges), etc. 6 Data Modeling • The DMBOK 2 defines Data Modeling and Design as “the process of discovering, analyzing, representing and communicating data requirements in a precise form called the data model.” • Extended readings: challenges in data modeling – https://learndatamodeling.com/blog/challenging- situations-in-data-modeling/ 7 Terms Comparison • Compared to these similar concepts – data structure – schema – data architecture – semantic layer – metadata 8 Data Structure • In computer science, a data structure is a data organization, management, and storage format that enables efficient access and modification. More precisely, a data structure is a collection of data values, the relationships among them, and the functions or operations that can be applied to the data, i.e., it is an algebraic structure about data. – https://en.wikipedia.org/wiki/Data_structure • A data structure is a more technical and lower-level term. It emphasizes a structure or a model that targets computer system (vs. human) for optimal processing. • For example, an array is a data structure, as well as dictionaries. Also, the classes that form your data model, are data structures too, any representation of a specific data object has to be in form of a data structure. – https://stackoverflow.com/questions/24228038/difference-between- datastructure-and-datamodel-with-example • A data set may be lack of data structure (unstructured data) but it has a date model inherently. 9 Schema • Schema is a structure, a representation of a plan or theory in the form of an outline or model. • Database schema refers to the organization of data as a blueprint of how the database (a general sense) is constructed. – Schema is a formal definition of the database and all of its objects. – Schema can be thought as the physical implementation of a data model. • A database generally stores its schema in a data dictionary. It is often visualized in a diagram. • XML Schema is a definition of XML format files. • Good video (also explains why schema in modern analytics) – https://www.youtube.com/watch?v=3BZz8R7mqu0 10 Data Architecture • Both data architecture and data modeling are the knowledge areas defined in DMBOK V2. – “Data Architecture defines the blueprint for managing data assets by aligning with organizational strategy to establish strategic data requirements and designs to meet these requirements.” – “Data modeling is the process of discovering, analyzing, representing and communicating data requirements.” • So, data architecture is actually a system architecture focusing on the management of data. It is not directly about how data is represented internally. • Data architecture is an integral part in the overall enterprise architecture, defining the structure and operation of data in the whole organization. • Focus – Business: processes, organizational structures, and business goals – Infrastructure and platform: on-site or cloud storage? – Systems and technology: virtualization? – Apps and data flows: API, download, import/export? – Security, privacy, compliance: access control, encryption 11 An example of data architecture from https://www.mckinsey.com/business- functions/mckinsey-digital/our- insights/how-to-build-a-data-architecture- to-drive-innovation-today-and-tomorrow Extended reading: • https://www.dataversity.net/data-modeling-vs- data-architecture/ • https://www.integrate.io/blog/data-modeling-vs- data-architecture-5-critical-differences/ Semantic Layer • A semantic layer is a business representation of corporate data that helps end users access data autonomously using common business terms. A semantic layer maps complex data into familiar business terms such as product, customer, or revenue to offer a unified, consolidated view of data across the organization. • By using common business terms, rather than data language, to access, manipulate, and organize information, a semantic layer simplifies the complexity of business data. Business terms are stored as objects in a semantic layer, which are accessed through business views. 12 Extended reading: • https://www.atscale.com/blog/what-is-a-universal-semantic-layer- why-would-you-want-one/ • https://kyligence.io/blog/semantic-layer-the-bi-trend-you-dont- want-to-miss-in-2020/ Metadata and Data Dictionary • Metadata is "data that provides information about other data", but not the content of the data. – https://en.wikipedia.org/wiki/Metadata – Technical meta vs. business metadata • Metadata is a general term used in many domains. – Metadata in webpages – Metadata in video/audio files – Metadata in database systems • Data dictionary – A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a “centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format” – Data dictionary is a system – https://en.wikipedia.org/wiki/Data_dictionary 13 Where are data models used? • Data models are used in many data related fields and operations including – Database design and development – Data management and storage – Business intelligence and analytics – Analytical data storage – Data analysis, query, and report – Software engineering – Data governance https://www.dataversity.net/data- models-create-living-policy-data-management/ – Data architecture 14 Data Model/Schema in Analytics • Why data model (in BI and analytics)? – It is the “intelligence behind BI (analytics)” https://www.slideshare.net/Dataversity/ldm- webinar-data-modeling-business-intelligence – Facilitates the understanding, query, reporting, analysis, and other use of data – Creates business meaning & context – Understands source and target data systems – Optimizes data structures to align queries with reports – More: https://www.linkedin.com/pulse/7-reasons-non-analysts-should-understand-data- modelling-chris-cook/ • In traditional BI and data warehouse, data models are predefined and created before any analysis or reporting. In more recent self-analytics movement, data models may be automatically created at the time of analysis and reporting. • Is data model less important in modern analytics? – It’s important to find a balance between managing & modeling “trusted data sets” and giving users the flexibility to explore. – Most users will find these trusted data sets a welcome asset, but don’t want to be restricted from doing data exploration when appropriate. 15 Extended reading: Data Modeling & Business Intelligence webinar https://www.dataversity.net/ldm- webinar-data-modeling-business-intelligence/ with slides https://www.slideshare.net/Dataversity/ldm-webinar-data- modeling-business-intelligence Typical Data Models and Their Use in Analytics • Entity relationship model (a conceptual model) • Relational data model (a logical model) • Dimensional data model • Hierarchical data model • Graph (network) data model • More details – https://www.guru99.com/data-modelling-conceptual- logical.html • More types of data models – https://docs.microsoft.com/en- us/azure/architecture/guide/technology-choices/data-store- overview 16 Entity Relationship Model • E/R is the most commonly used conceptual level model in software design and database design. • It is a conceptual level model that uses a few simple elements to describe how things are defined (entity) and related (relationship). • It does not have extensive implementation details • Good for human understanding but not computing operations 17 Review ER model in your introduction to database systems course. Relational Model • Relational model is based on relation theories and a set of mathematical rules • It essentially a 2D data table (rows and columns). Although in database, relational model abides to additional stricter rules. • It is common in relational databases and spreadsheet data • Most of the database classes (like CSE 3153, IT 4153, IT 5433, and IT 6733) focus on relational models 18 Review Relational Model in your introduction to database systems course. Dimensional Data Model • Dimensional data model is a data model that specifically constructed around the elements of facts and dimensions – It is directly based on the most common business questions and queries – The platform for (traditional) BI. • Dimensional model is a conceptual model and can be implemented in a logical model like the relational model or in a logical multidimensional database model like an OLAP server (such as SQL Server Analysis Services dimensional model) • Where is Dimensional Model used? 1. A major data warehouse design method by Ralph Kimball (http://en.wikipedia.org/wiki/Ralph_Kimball) • Dimensional modeling has been broadly accepted as the dominant technique for DW/BI presentation. • “Based on our experience and the overwhelming feedback from numerous practitioners from companies like your own, we believe that dimensional modeling is absolutely critical to a successful DW/BI initiative.” (Kimball) 2. The basis of OLAP (multidimensional database) 3. The foundation of multidimensional analysis; most widely used in many business (descriptive) analysis 19 Dimensional model is specifically covered in IT 4713 and IT 7123. Hierarchical Model • A hierarchical database model is a data model in which the data are organized into a tree-like structure. • The data are stored as records which are connected to one another through links. • Hierarchical data models are commonly implemented in many database systems (such as IBM IMS) and data files in XML/JSON format. 20 Hybrid Data Models • Hybrid data models refer to a data model that accommodates two or more types of data models in the same data set • Example – MariaDB uses JSON (Hierarchy) + Relational https://mariadb.com/database-topics/semi-structured-data/ (webinar video recording https://www.youtube.com/watch?v=ES0I_uYJLRw with slides at https://www.slideshare.net/Dataversity/json-relational- how-to-use-hybrid-data-models) – Microsoft SQL Server also support similar features 21 In traditional relational schema design, interests would be in another table and linked back to users (M:N). JSON format simplifies the table structure. Essential Learning Resources • Core readings: – What is data model: https://en.wikipedia.org/wiki/Data_model (focus on section 1 to 4) – More details of the three levels of data models: https://www.guru99.com/data-modelling-conceptual-logical.html – Data model vs. data architecture: https://www.dataversity.net/data- modeling-vs-data-architecture/ – Why do we need data models? https://www.linkedin.com/pulse/7-reasons- non-analysts-should-understand-data-modelling-chris-cook/ – Types of Data Model: https://www.educba.com/types-of-data-model/ - a brief intro to the four basic data models. For a more extensive overview, see the additional resources 3a. • Videos – What is a data model? https://www.youtube.com/watch?v=4qFZ-5i4GS8 – Webinar - Data Modeling & Business Intelligence (and data analytics): Webinar recording https://www.dataversity.net/ldm-webinar-data-modeling- business-intelligence/ and slides here https://www.slideshare.net/Dataversity/ldm-webinar-data-modeling- business-intelligence 22 Additional Good Materials/Resources • Review of three levels of data models – https://opentextbc.ca/dbdesign01/chapter/chapter-5-data-modelling/ – http://www.databaseanswers.org/data_models/types_of_data_models/index.htm • Terms – What is a database schema https://www.youtube.com/watch?v=3BZz8R7mqu0 – https://www.atscale.com/blog/what-is-a-universal-semantic-layer-why-would-you-want-one/ – https://kyligence.io/blog/semantic-layer-the-bi-trend-you-dont-want-to-miss-in-2020/ – Data architecture • https://www.integrate.io/blog/the-ultimate-guide-to-data-architecture/ • https://www.dataversity.net/data-modeling-vs-data-architecture/ • https://www.integrate.io/blog/data-modeling-vs-data-architecture-5-critical-differences/ • A more extensive survey of major data models – https://www.lucidchart.com/pages/database-diagram/database-models – https://afteracademy.com/blog/what-is-data-model-in-dbms-and-what-are-its-types • Data modeling – https://panoply.io/analytics-stack-guide/data-modeling-examples-for-analytics/ – https://www.dataversity.net/category/data-topics/modeling/ – https://www.dataversity.net/data-modeling-trends-in-2022/ – https://www.dataversity.net/data-modeling-trends-in-2020-a-year-of-optimization/ – https://www.dataversity.net/data-modeling-trends-in-2019/ – http://www.agiledata.org/essays/dataModeling101.html – https://learndatamodeling.com • https://www.janbasktraining.com/blog/data-modeling-interview-questions/