Deciphering Data to Uncover Hidden Insights — Data Modeling

Model the Data (Conceptual)

What Is OLTP?

  1. Finding the age of the current user when given a user ID.
  2. Update the billing addresses for a set of clients.
  3. Delete the users who are not accessing the page more than 1 year.
  1. MySQL
  2. PostgreSQL

What Is OLAP?

  1. What’s the average cost spent on maintenance?
  2. What’s the user’s overall active hours?
  1. AnalyticDB
  2. Teradata

What is Data Modeling

Types of Data Modeling

  1. Conceptual Data Models: High-level, static business structures and concepts.
  2. Logical Data Models (LDMs): Entity types, data attributes and relationships between entities.
  3. Physical Data Models (PDMs): The internal schema database design.

Data Modeling in Business Intelligence

What Is Data Warehousing

  1. Query constraining / grouping / filtering.
  2. Report labeling

Schemas in Data Warehousing

Model the Data (Practical)

  1. Create a data source.
  2. Create a dataset.
  3. Create a workbook, worksheet, or dashboard.
  4. Building a data portal.
  5. Subscribe for timely reports.

UseCase-1: ATM Analytics

  1. Select the Cube Icon
  2. Enter the name and specify the location
  3. Click Ok
  1. Dataset is created
  1. Select the Dataset
  2. Click the Edit Icon
  1. The dataset management page is displayed.
  1. Click the Gear Icon
  2. Click Edit to Change the Name and to add notes
  1. Click Ok
  2. Name is Changed
  1. Click the Gear Icon
  1. Duplicate Dimension or Measure (It will create a copy of the column)
  2. Hide in Analysis (It will hide the column in report interface)
  3. Cancel all hidden (It will remove all the hidden columns and make it available for reporting)
  4. Delete (It will delete the column)
  1. Click the Gear Icon -> Create a Calculated Column or + icon
  1. Search the function on the right-side pane for reference
  2. Name the Calculated column
  3. Write the expression (you can add column by simply double clicking it in the left side pane)
  4. Click ok
  1. Ingest the ATM Master Details data into Quick BI
  2. Click the Join Icon
  3. Select the Dataset Field From ATM
  4. Select the Other Dataset ATM_Master Details
  5. Select the Join on Field
  6. Click Ok
  1. Click the Gear Icon
  2. Select the Change Dimension Type
  3. Select Location
  4. Click the Country
  1. Click the Gear Icon -> Create a Calculated Column or + icon
  1. Search the function on the right-side pane for reference
  2. Name the Calculated column
  3. Write the expression (you can add column by simply double clicking it in the left side pane)
  4. Click ok
  1. Click the Gear Icon
  2. Select Create Level
  1. Hierarchy Level is Created
  2. To add other column in hierarchy, Select the column Gear icon
  3. Click to Move to
  1. Column is added to Hierarchy.
  1. Click the Gear Icon
  2. Select Aggregations
  3. Select Any one from the list (e.g. Average)
  1. Click the Gear Icon
  2. Select Number Format
  3. Select Any one from the list (e.g. #,###0)

UseCase-2: Customer 360

Model the Data (Best Practices)

  1. Validate the data types.
  2. Create Calculated columns whenever necessary.
  3. Change the Locations as Location Type in QuickBI otherwise it will not detect it.
  4. Numbers should be in integer.
  5. Format string as per the business scenario.
  6. Format the date as per the business scenario.
  7. Star Schema is preferable than snowflake schema.
  8. Use custom SQL to model the data.

Conclusion

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to implement ArgoCD-Vault-Plugin with AWS Secrets Manager

Exception Propagation From Another Process

WEBRTC SIGNALING SERVER

Explain use of Coveo for salesforce

5 Hilarious Moments In Every Developer’s Life

Documenting Your Data-Science Project — A Guide To Publish Your Sphinx Code Documentation

A visual analysis of UK number 1s: delving into development

IBM API Connect | Cloud, pOrg, Catalog, Space, Plan usage recommendation

Birds Eye View of APIC Cloud Configuration

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Unlocking interactive dashboards at Rovio with Druid and Spark

Automated Data Pipeline Testing using Great Expectations

Build a Winning Data Pipeline Architecture on the Cloud for CPG

Beating The Data Heist