Main concept of dimension and DMQL?

Question

I am creating a test DWH for personal purpose. I have read about star,snowflake and constallation schemas concepts, and for initial purposes I will use star schema.

The RDBMS I am using is postgreSQL.

Throug the bibliography I read I have some doubts that can not be solved yet.

When we speak about dimensions are we talking about schemas? For example I can create schema Time, and schema location to have two dimensions? I think that we are really talking about single tables, and the schemas are a good methodology to split the origins about DWH. Imagine that I have two databases from app's to centralize in DWH. I can have schema 1 for app1, and schema 2 for app2 where each schema has their own star schema using tables to define each dimension.

Is it correct?

My other question is: What's the difference between DMQL and SQL? Could be the first used in any RDBMS? I try to use it in postgre but it does not work. Can I create a DWH using sql?

Thanks.

Answer 1

When we speak about dimensions are we talking about schemas?

From the Postgres docs:

A database contains one or more named schemas, which in turn contain tables.

Dimensions are stored in tables (sometimes views). Schemas are not tables. They contain tables.

Yes schemas are good to split database objects in functional areas, but in my experience, splitting facts and dimensions into schemas gets tricky because dimensions can belong to a few subject areas (fact tables) so you can't just confine them to one schema.

Staging tables on the other hand make perfect sense to be put into schemas. If your staging table data comes from app1 then put it in schema app1.

You should be designing your DWH around business process and fitting different applications into it seamlessly. A fact might contain records from a few different source systems (in fact you often have a metadata column indicating where the record came from)

So in summary do not use schemas in your star schema. Use schemas in your staging database