Vertica Dimension Table query

JunaidKhan

Hi,

I need support/help in the below query.

"my application is using the vertica and i have one datamart table. and this table is refering to the dimension tables now the issue is i have created one dimension table for column which is in datamart table, my definition for the dim table is like below.

CREATE TABLE DIM_AffectedService (
Affected_Service_ID INTEGER,
Affected_Service VARCHAR(100),
UPDATE_TIMESTAMP TIMESTAMP
) ORDER BY Affected_Service UNSEGMENTED ALL NODES;

ALTER TABLE DIM_AffectedService ALTER COLUMN Affected_Service SET NOT NULL;
ALTER TABLE DIM_AffectedService ADD CONSTRAINT DIM_Affected_Service_PK_ID PRIMARY KEY (Affected_Service);

ALTER TABLE FCT_FAULT ADD CONSTRAINT FF_FK_NE_TYPE FOREIGN KEY (Affected_Service) REFERENCES DIM_AffectedService (Affected_Service);
GRANT ALL ON DIM_AffectedService TO ossa;

FCT_FAULT is the main datamart, now the issue i am facing is i am having the data available for the Affected_Service in FcT_Fault table but i am not able to see any data in the dimension table.

Appreciate.

Jim_Knicely

Hi,

Are you asking why you do not see data in the "Affected_Service" column of the FcT_Fault table?

Normally a fact/dim relationship works like this:

dbadmin=> SELECT * FROM fact;
 c1 | c2
----+----
  1 |  1
(1 row)

dbadmin=> SELECT * FROM dim;
 c2 |  c3
----+------
  1 | TEST
(1 row)

dbadmin=> SELECT fact.c2, dim.c3
dbadmin->   FROM fact
dbadmin->   JOIN dim
dbadmin->  USING (c2);
 c2 |  c3
----+------
  1 | TEST
(1 row)

So in your case, you would add the "Affected_Service_ID" column to the "FCT_FAULT" table and then look up the value of the "Affected_Service" column from the "DIM_AffectedService" table.

Or, you could try a Vertica Flattened table!

https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm

JunaidKhan

Hi Jim, Thanks for your reply, actually i have the value of Affected_Service in fct_table but not in dim table,

the solution you are suggesting is basically i need to save manually the values of affected_service in dim table and then use ID as foreign key in fct table, that is perfect but my concern is there are more then 1000 possible values for affected service so when ever the new entry comes in fct_table should also be in dim table,

please sugges like this way would be appreciated.

Br,