Best practices in data modeling

Best Practices in Data Modeling
Dan English

Objectives

• • • • •

Understand how QlikView is Different from SQL Understand How QlikView works with(out) a Data Warehouse Not Throw Baby out with the Bathwater Adopt Applicable Data Modeling Best Practices Know Where to Go for More Information

QlikView is not SQL (SQL Schemas)

SQL take a large schema and queries a subset of tables. Each query creates a temporary “Schema” of only a few tables. Query result sets are independent of each other.

Query 1

Query 2

QlikView is not SQL (QV Schemas)

QlikView builds a smaller and more reporting friendly schema from the transactional database. This schema is persistent and reacts as a whole to user “queries”. A selection affects the entire schema.

QlikView is not SQL (Aggregation and Granularity)
Store Table
Store A B SqrFootage 1000 800

Sales Table

Store A A A B B

Prod 1 2 3 1 2

Price $1.25 $0.75 $2.50 $1.25 $0.75

Date 1/1/2006 1/2/2006 1/3/2006 1/4/2006 1/5/2006

Select * From Store, Sales Where Store.Store = Sales.Store will return:

SqrFootage 1000 1000 1000 800 800

Store A A A B B

Prod 1 2 3 1 2

Price $1.25 $0.75 $2.50 $1.25 $0.75

Date 1/1/2006 1/1/2006 1/1/2006 1/1/2006 1/1/2006

Sum(SqrFootage) will return: 4600 If you want the accurate Sum of SqrFootage in SQL you can not join on the Sales table in the same Query!

QlikView is not SQL (Benefits)

•

QlikView allows you to see the results of a selection across the entire schema not just a limited subset of tables.

QlikView is not SQL (Benefits)

•

QlikView allows you to see the results of a selection across the entire schema not just a limited subset of tables. QlikView will aggregate at the lowest level of granularity in the expression not the lowest level of granularity in the schema (query) like SQL.

•

QlikView is not SQL (Benefits)

•

QlikView allows you to see the results of a selection across the entire schema not just a