The newbie cloud architect diary

1. A Data Mart allows for more efficient OLAP operations, especially low-cost aggregation and low-latency request. Indeed, compute cost is way more expensive than storage cost in a DW. Reports tend to repeatedly run the same requests over and over again (think of a dedicated screen with auto-refresh set to 5s). Users usually do not expect to wait for seeing results.
2. A Data Mart is achieved through Dimensional modeling using for example a Star schema or a Snowflake schema. In such schema, dimension tables hold the Busines Entities and their attributes, while fact tables mainly hold the relationships, optionally some measures in case their granularity fits well. Otherwise, it might be convenient to add another layer dedicated to the aggregation of facts and the calculation of Key Performance Indicators (KPI), accessible from a summary statistics table or a multi-dimensional database aka. cube.

• dim_user
• id (pk)
• username
• dim_time
• id (pk)
• dt
• hour
• day
• month
• year
• weekday
• weeknum
• message
• id (pk)
• user_id (fk)
• time_id (fk)
  
1. units

1. Since the concept of a DW was born at Wall-Mart, it has been more or less replicated from receipt across all retailers, and finally adopted by any industry with a large amount of Order transactions. So that a vast majority of productive instances are used for Sales analytics. 
2. Designing a DW is often (wrongly) considered as One-shot initiative, or seen as an activity coming right from the "Dark side":
• Experimental, so that it doesn't easily justify any long-term investment.
• Sensitive, so that may it may affect legislation for data privacy and intellectual property.
• Confidential, so that it may impact market competition and innovation.
3. Producing a reliable recommendation out of both an operational schema and some reasonable user input (e.g. from a Business Analyst) is difficult for a machine. Indeed, taking decisions based on Static dictionaries or Machine Learning models requires large and representative historical knowledge. While some academical websites exist (ex. databaseanswers.org, learningdatamodeling.com), there is no widely recognized catalog other than the Data Model Resource Book (DRMB) originally written by Len Silverston.
4. Making a Data schema evolutionary (e.g. Data Vault Modeling) adds some more complexity.
5. Even Gartner makes up a Wish-wash of actual DW advancements in 2020!

## denormalization.py

# read input file
import json
fact_name = 'message'
with open(r'rocketchat_' + fact_name + '_sample.json') as f:
    data = json.load(f)

# custom: flag metrics and dimension columns
data['units'] = 0.0
data['dim_user'] = data.pop('u')
data['dim_time'] = {'_id':0,'dt':data.pop('ts')}

# custom: enrich dimensions
import pandas as pd, dateutil.parser
dt = dateutil.parser.parse(data['dim_time']['dt'])
data['dim_time']['hour'] = dt.hour
data['dim_time']['day'] = dt.day
data['dim_time']['month'] = dt.month
data['dim_time']['year'] = dt.year
data['dim_time']['weekday'] = dt.weekday()
data['dim_time']['weeknum'] = dt.isocalendar()[1]

# normalize data
from pandas.io.json import json_normalize
df = pd.DataFrame(json_normalize(data))

# create file db
from sqlalchemy import create_engine
db_uri = 'sqlite:///db.sqlite.denorm'
engine = create_engine(db_uri, echo=False)

# create temporary dimension tales
for col in df.columns:
    if col.startswith('dim_'):
        tbl = col.split('.')[0]
        pd.DataFrame(json_normalize(data[tbl])).to_sql(tbl, 
            con=engine,
            if_exists='replace',
            index=False)

# create temporary fact table
df.drop(list(df.filter(regex='dim_[^.]*\.[^_]')), axis=1, inplace=True)
df.to_sql(fact_name,
        con=engine,
        if_exists='replace',
        index=False)

$ pip install -r requirements.txt
$ python denormalization.py

## reflection.py

fact_name = 'message'

# open file db
from sqlalchemy import create_engine
db_uri = 'sqlite:///db.sqlite.denorm'
engine = create_engine(db_uri, echo=False)

# reflect sample data and create DDL
from sqlalchemy import MetaData, Table, Column
from sqlalchemy.schema import CreateTable
from migrate.changeset.constraint import PrimaryKeyConstraint, ForeignKeyConstraint
meta = MetaData()
meta.reflect(bind=engine)
# sort tables by dependency order
for t in meta.sorted_tables:
    print('-- ', t.name, ' sample data: ', engine.execute(t.select()).fetchall(), '\n') # stdout ddl
    # fact
    if t.name == fact_name:
        for c in t.columns:
            # fk
            if c.name.startswith('dim_'):
                t.append_constraint( ForeignKeyConstraint([c.name], [c.name]) )
        # rm all non relevant cols
        ddl_lines = str(CreateTable(t)).split('\n')
        for i in reversed(range(2, len(ddl_lines) - 3)):
            if not [ele for ele in ['units ','"dim_'] if(ele in ddl_lines[i])]:
                del ddl_lines[i]
        tbl_ddl = "\n".join(ddl_lines)
    # dimension
    else:
       # pk
       t.append_constraint( PrimaryKeyConstraint('_id', name=t.name + '_pk') )
       tbl_ddl = CreateTable(t)
    # todo: standardize data types to VARCHAR (except fact units)
    # todo: replace dots with underscores in column names
    print(tbl_ddl) # stdout ddl
    # recreate table
    t.drop(engine)
    engine.execute(tbl_ddl)

$ pip install -r requirements.txt
$ python reflection.py

--  dim_time  sample data:  [(0, '2020-08-30T15:21:37.974Z', 15, 30, 8, 2020, 6, 35)] 
CREATE TABLE dim_time (
	_id VARCHAR NOT NULL, 
	dt VARCHAR, 
	hour VARCHAR, 
	day VARCHAR, 
	month VARCHAR, 
	year VARCHAR, 
	weekday VARCHAR, 
	weeknum VARCHAR, 
	CONSTRAINT dim_time_pk PRIMARY KEY (_id)
);

--  dim_user  sample data:  [('3jh6AN3eLZKRCD6E9', 'tncad')] 
CREATE TABLE dim_user (
	_id VARCHAR NOT NULL, 
	username VARCHAR, 
	CONSTRAINT dim_user_pk PRIMARY KEY (_id)
);

--  message  sample data:  [('MshE4AKDejGaiwJkv', 'uj', 'GENERAL', 'tncad', False, '2020-08-30T15:21:37.974Z', 0.0, '3jh6AN3eLZKRCD6E9', 0)] 
CREATE OR REPLACE TABLE message (
	UNITS FLOAT, 
	DIM_USER__ID VARCHAR, 
	DIM_TIME__ID VARCHAR, 
	FOREIGN KEY(DIM_USER__ID) REFERENCES dim_user (_id), 
	FOREIGN KEY(DIM_TIME__ID) REFERENCES dim_time (_id)
);