Previous Topic: Deleting Partitioned Tables |
Next Topic: Distributed Domains |
SAND supports the creation of dimension tables for use with star or snowflake schemas. These tables are created on the head node, and are replicated automatically across all partition nodes. In MPP node, all new tables created via the CREATE TABLE command are considered dimension tables and are thereby copied to other nodes, unless the "LOCAL" keyword is included in the command statement.
As well, a standard table can be converted to a dimension table using ALTER TABLE...ADD DIMENSION (in MPP mode) or ALTER TABLE...DIMENSION OF partitioned-table (outside of MPP mode). However, when an existing table is converted in this way, only the table structure is replicated on the other nodes. To copy the table's records as well, the REFRESH DIMENSION TABLE command must be executed after the table is converted.
Important:
When creating a dimension table, the containing schema on the head node must already exist on the remote nodes where it will be replicated, and the users defined for the connections to the remote nodes must have the necessary privileges, otherwise the dimension creation command will fail.
Once the dimension table is created and replicated across nodes, all changes (via INSERT, UPDATE, DELETE, ALTER TABLE) to this table on the head node are propagated to the other nodes, whether or not in MPP mode. Similarly, removing the dimension table from the head node with the DROP TABLE command will cause the same table to be removed from the remote nodes. Note that the same kinds of changes to dimension tables on remote nodes will not be propagated; only changes to dimension tables on the head node.
If, for any reason, the replicated dimension tables become inconsistent with one another in terms of data, they can be "reset" at any time to an exact copy of the dimension table on the head node using the REFRESH DIMENSION TABLE command. This inconsistency might arise, for example, from manual INSERTs, UPDATEs, and DELETEs at the nodes, or by loading data directly into any of the dimension tables (including the head table).
If a dimension table is no longer required as a dimension, it can be converted back to a standard table using the ALTER TABLE...DROP DIMENSION command on the head node. The corresponding tables on the remote nodes are not removed or changed by this command.