Adding tables for varying attributes

The varying attributes is an attribute dimension that maps to multiple dimensions. The concept of varying attributes in a relational environment is depicted by creating a mapping table. In this recipe, we will build a mapping table that joins the SALESMAN table to the Product and Market tables. We will also see how this format works for a varying attribute.

Getting ready

To get started, open SQL Server Management Studio, and add a database called TBC . In this recipe, we are using T-SQL, but the PL\SQL equivalent is provided in the examples.

How to do it...

1. Create and populate a SALESMAN table with following script. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2():

   --This is the syntax in T-SQL
   Create Table SALESMAN(
     SALESMANID       int            NOT NULL,
     SALESMANNAME     varchar (80)   NULL,
     Constraint PK_SALESMAN_SALESMANID Primary Key (SALESMANID)
   );
   go
   --This scripts enter the data into the SALEMAN table
     Insert Into SALESMAN Values(1, 'John Smith');
     Insert Into SALESMAN Values(2, 'Jose Garcia');
     Insert Into SALESMAN Values(3, 'Johnny Blaze');

2. Create and populate a PRODUCTS table:

   --This is the syntax in T-SQL
   Create Table PRODUCTS(
     PRODUCTID   int         NOT NULL,
     SKU         varchar(15) NULL,
     SKU_ALIAS   varchar(25) NULL,
     Constraint PK_PRODUCTS_PRODUCTID Primary Key (PRODUCTID)
   );
   --Insert data into PRODUCTS table
   Insert Into PRODUCTS Values(1, '100-10', 'Cola');
   Insert Into PRODUCTS Values(2, '100-20', 'Diet Cola');
   Insert Into PRODUCTS Values(3, '100-30', 'Caffeine Free Cola'); 

3. Create and populate a MARKETS table:

   --This is the syntax in T-SQL
   Create Table MARKETS(
     STATEID       int           NOT NULL,
     STATE         varchar(25)   NULL,
     Constraint PK_MARKETS_STATEID Primary Key (STATEID)  
   );
   --Insert data into MARKETS table
   Insert Into MARKETS Values(1, 'New York');
   Insert Into MARKETS Values(2, 'Massachusetts');
   Insert Into MARKETS Values(3, 'Florida');

4. Create and populate a SALESMANMAP table:

   --This is the syntax in T-SQL
   Create Table SALESMANMAP(
     SALESMANID   int NOT NULL,
     STATEID      int NOT NULL,
     PRODUCTID    int NOT NULL,
     Constraint PK_SALESMANMAP Primary Key (STATEID, PRODUCTID)  
   );
   
   --Insert data into the SALESMANMAP table
   Insert Into SALESMANMAP Values(1,1,2);
   Insert Into SALESMANMAP Values(1,2,3);
   Insert Into SALESMANMAP Values(2,1,1);
   Insert Into SALESMANMAP Values(2,2,2);
   Insert Into SALESMANMAP Values(3,3,1);
   Insert Into SALESMANMAP Values(3,3,3);

5. Execute the following scripts to join the tables:

   Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_PRODUCTID Foreign Key(PRODUCTID)
   References PRODUCTS (PRODUCTID);
   
   Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_SALESMANID Foreign Key(SALESMANID)
   References SALESMAN (SALESMANID);
   Alter Table SALESMANMAP Add  Constraint FK_SALESMANMAP_STATEID Foreign Key(STATEID)
   References MARKETS (STATEID);

6. Execute the following query to see the relationship between the Market, Product, and Salesman:

   Select
     T4.SALESMANID, T2.SALESMANNAME, T4.STATEID, 
     T3.STATE, T4.PRODUCTID, T1.SKU_ALIAS
     From PRODUCTS T1, SALESMAN T2, MARKETS T3, SALESMANMAP T4
     Where T1.PRODUCTID = T4.PRODUCTID and
     T2.SALESMANID = T4.SALESMANID and
     T3.STATEID = T4.STATEID and
     T2.SALESMANNAME = 'John Smith'

How it works...

The relationships between the tables created and populated in steps 1 through 4 are shown in the following diagram:

The SALESMANMAP table joins the other three tables of which SALESMAN is the varying attribute. Salesman varies depending on the state and product you want to report on. We can retrieve the relationship between tables using the query shown in step 5. The results for this query are displayed in the following screenshot:

The important thing to note about this varying attribute example is that it is possible for two Salesmen to conduct business in the same Market, but it is not possible for the same two Salesmen to sell the same Product in the same Market. This logic is maintained by the constraints placed on the SALESMANMAP mapping table.

See also

Refer to the Adding tables to a Minischema recipe in Chapter 2 to add the varying attribute example to the TBC database. Refer to the Setting Essbase Properties recipe in Chapter 3 to learn how to set the varying attributes' properties.