How to I recognize a fact table in a data warehouse

Hi at all

as by topic title i want to know, if possible, how to can I recognize a fact table when I have a data warehouse in SQL script form.

For example, given the following data warehouse:

Code:

CREATE TABLE SOCIETA
 (
  ID_SOCIETA                 INT IDENTITY,
  RAG_SOCIALE            VARCHAR(100),
  P_IVA               VARCHAR(11),
  VIA                       VARCHAR(100) NULL,
  CITTA               VARCHAR(50) NULL
 );
 ALTER TABLE SOCIETA ADD CONSTRAINT PK_SOCIETA PRIMARY KEY NONCLUSTERED (ID_SOCIETA);
 
 CREATE TABLE ANAGRAFICA_DIPENDENTE
 (
  ID_DIPENDENTE                 INT IDENTITY,
  ID_SOCIETA            INT,
  NOME               VARCHAR(50) NULL,
  COGNOME               VARCHAR(50) NULL,
  DATA_NASCITA            DATETIME,
  COD_FISC               VARCHAR(16) ,
  VIA                  VARCHAR(50) NULL,
  CITTA                 VARCHAR(50) NULL,
  TELEFONO               VARCHAR(15) NULL,
  EMAIL                VARCHAR(50) NULL,
  TIPO_CONTRATTO         VARCHAR(50) NULL,
  DATA_INIZIO_CONTRATTO      DATETIME,
  DATA_FINE_CONTRATTO      DATETIME
 );
 ALTER TABLE ANAGRAFICA_DIPENDENTE ADD CONSTRAINT PK_DIPENDENTE PRIMARY KEY NONCLUSTERED (ID_DIPENDENTE);
 ALTER TABLE ANAGRAFICA_DIPENDENTE ADD CONSTRAINT FK_ID_SOCIETA FOREIGN KEY (ID_SOCIETA) REFERENCES SOCIETA(ID_SOCIETA);
 
 CREATE TABLE CLIENTE
 (
  ID_CLIENTE                 INT IDENTITY,
  RAG_SOCIALE            VARCHAR(100),
  P_IVA               VARCHAR(11),
  VIA                       VARCHAR(100) NULL,
  CITTA               VARCHAR(50) NULL
 );
 ALTER TABLE CLIENTE ADD CONSTRAINT PK_CLIENTE PRIMARY KEY NONCLUSTERED (ID_CLIENTE);

 
 CREATE TABLE COMMESSA
 (
  ID_COMMESSA                 INT IDENTITY,
  ID_CLIENTE                INT,
  DESC_COMMESSA         VARCHAR(100) NULL,
  RAG_SOCIALE_CLIENTE_FINALE   VARCHAR(100),
  BUDGET               DECIMAL(19,2),
  DATA_INIZIO_COMMESSA      DATETIME,
  DATA_FINE_COMMESSA        DATETIME
 );
 ALTER TABLE COMMESSA ADD CONSTRAINT PK_COMMESSA PRIMARY KEY NONCLUSTERED (ID_COMMESSA);
 ALTER TABLE COMMESSA ADD CONSTRAINT FK_ID_CLIENTE FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTE(ID_CLIENTE);
 
 CREATE TABLE ATTIVITA
 (
  ID_ATTIVITA                 INT IDENTITY,
  ID_COMMESSA            INT,
  ID_DIPENDENTE            INT,
  DESC_ATTIVTA                  VARCHAR(100) NULL,
  VIA                  VARCHAR(100) NULL,
  CITTA                 VARCHAR(50) NULL,
  DATA_INIZIO_ATTIVITA      DATETIME,
  DATA_FINE_ATTIVITA          DATETIME
 );
 
 ALTER TABLE ATTIVITA ADD CONSTRAINT PK_STAGIONE PRIMARY KEY NONCLUSTERED (ID_ATTIVITA);
 ALTER TABLE ATTIVITA ADD CONSTRAINT FK_ID_COMMESSA FOREIGN KEY (ID_COMMESSA) REFERENCES COMMESSA(ID_COMMESSA);
 ALTER TABLE ATTIVITA ADD CONSTRAINT FK_ID_DIPENDENTE FOREIGN KEY (ID_DIPENDENTE) REFERENCES ANAGRAFICA_DIPENDENTE(ID_DIPENDENTE);
 
 
  CREATE TABLE COSTI
 (
  ID_DIPENDENTE                 INT NOT NULL,
  ID_ATTIVITA            INT NOT NULL,
  ANNO                     VARCHAR(4) NOT NULL,
  MESE                VARCHAR(2)NOT NULL ,
  COSTO_GIORNALIERO         DECIMAL(19,2),
  COSTO_MENSILE                      DECIMAL(19,2),
  GIORNI_LAVORATI         DECIMAL(19,2)
 );
 ALTER TABLE COSTI ADD CONSTRAINT PK_COSTI PRIMARY KEY NONCLUSTERED (ID_DIPENDENTE,ID_ATTIVITA,ANNO,MESE);
 ALTER TABLE COSTI ADD CONSTRAINT FK_ID_DIPENDENTE_COSTI FOREIGN KEY (ID_DIPENDENTE) REFERENCES ANAGRAFICA_DIPENDENTE(ID_DIPENDENTE);
 ALTER TABLE COSTI ADD CONSTRAINT FK_ID_ATTIVITA_COSTI FOREIGN KEY (ID_ATTIVITA) REFERENCES ATTIVITA(ID_ATTIVITA);
 

 CREATE TABLE SPESE_EXTRA
 (
  ID_DIPENDENTE                INT NOT NULL,
  ID_ATTIVITA              INT NOT NULL,
  ANNO                VARCHAR(4) NOT NULL,
  MESE                VARCHAR(2) NOT NULL,
  COSTO                 DECIMAL(19,2),
  DESC_COSTO            VARCHAR(100) NOT NULL
 );
 ALTER TABLE SPESE_EXTRA ADD CONSTRAINT PK_SPESE_EXTRA PRIMARY KEY NONCLUSTERED (ID_DIPENDENTE,ID_ATTIVITA,ANNO,MESE);
 ALTER TABLE SPESE_EXTRA ADD CONSTRAINT FK_COSTI_SPESE_EXTRA FOREIGN KEY (ID_DIPENDENTE,ID_ATTIVITA,ANNO,MESE) REFERENCES COSTI(ID_DIPENDENTE,ID_ATTIVITA,ANNO,MESE);

How to recognize the fact table?

Thanks and regards.

You may have facts, i.e. numerical values, but you don't appear to have a fact table. That is because this data is not dimensionally modeled into a star schema that contains fact and dimension tables.

Yes, i agree. I thought the same thing. It looks like in the script of the first post there isn't any fact table. But, the DBA of my company tells me that it is a data warehouse.

Maybe it's an Inmon data warehouse.

Good point! Gstars, are you familiar with the differences between a Kimball and Inmon data warehouse. A Kimball data warehouse will model the data using star schemas. In Inmon data warehouse will be normalized. If this is Inmon, and you are looking for fact tables, ask the DBA for the data mart.

Sorry for the late reply but I am really too busy these days.

However, by your opinion based on your experience, the data base in the script of the first message, what do it look like?
Do it look like at a classic relational DB or at a Kimbal Data warehouse or at a Inmon Data warehouse and so on?

Here's what I know:
1. It's relational
2. It enforces referential integrity
3. It's in Spanish
4. It's probably a SQL Server database

The tables COMMESSA, COSTI and SPESE_EXTRA are the closest thing a fact table, if this is a dimension model. They are the only tables with measures.

The thing is, any model can be represented in 3NF, even a dimensional model. From a point of view, almost any collection of 3NF tables could be construed as a dimensional model.

ngalemmo wrote:The tables COMMESSA, COSTI and SPESE_EXTRA are the closest thing a fact table, IF THIS IS A DIMENSIONAL MODEL. They are the only tables with measures. [...]

Hi ngalemmo, thanks for your response.

Why do you say if this is a dimensional model?

So, by what you say, it's not possible distinguish precisely a dimensional model from a classic database in 3NF model. A 3NF model can be also a dimensional model and vice versa.

Is there a way for distinguish precisely and in non ambiguous way a dimensional model from a classic database in 3NF?

What' is the substantial and real technical difference between two models that allow me of distinguish them?

thanks.

There are strict dimensional models and there are poor dimensional models. A proper dimensional model follows specific patterns that are easily distinguishable. Less strict forms muddy the distinctions between dimensional and 3NF and are more difficult to identify. It is possible to absolutely identify a dimensional model from the schema, just not in this case.

It looks like in the script of the first post there isn't any fact table. But, the DBA of my company tells me that it is a data warehouse.

I think these tables are used just for reporting purposes, because operational system tables usually have some specific columns like created user, creation date, update user etc..
So your DBA may think that these tables are used for reporting, so this is a data warehouse.

I've seen lots of poor designed reporting environments which are not in 3NF or dimensional form. They just replicate the model in source systems with the columns to be used in reports. So looking for a fact table or a dimension table in this kind of environments makes no sense.

ngalemmo wrote:There are strict dimensional models and there are poor dimensional models. A proper dimensional model follows specific patterns that are easily distinguishable. Less strict forms muddy the distinctions between dimensional and 3NF and are more difficult to identify. It is possible to absolutely identify a dimensional model from the schema, just not in this case.

I've started a few months ago to work with data warehouse, so i'm still inexpert.

The only dimensional model I know is the Star Schema model.

Please, can you show me one example of strict dimensional model and one of poor dimensional model?

umutiscan wrote:I think these tables are used just for reporting purposes, because operational system tables usually have some specific columns like created user, creation date, update user etc..
So your DBA may think that these tables are used for reporting, so this is a data warehouse

Yes, these tables are used for reporting scope, but also for operational one I think.

Sorry, but how many type of data warehouse there exist? Depending on what we have to do have we a different model of data warehouse?

So, the dimensional model (star schema, for me) is not the only model for data warehouse......