To get started, open SQL Server Management Studio, and add a database TBC, or if you are using Oracle you can add schema TBC and use TOAD or Golden to complete the recipe.

The first and second steps setup a Product Table and populate it with data. The third step retrieves a count of the SKUs in your Product table. You will need this count to determine if the attribute column has a one-to-many relationship with the SKU. This query returned exactly 13 valid SKUs.

The first script in step 4 returns a row for every time the SKU rolls up to one parent. You can see in the following screenshot, that there are exactly 13 rows signifying that each SKU rolls up to one CAFFEINATED value.

This query returns the value, as shown in the following screenshot:

Select SKU, Count(CAFFEINATED) As Cnt 
  From PRODUCT 
  Group By SKU 
  Having Count(CAFFEINATED) = 1;1;

The second script in step 4 shows you that the CAFFEINATED column has a true and false value, which makes this column a good candidate for a Boolean attribute dimension. The reason this is a Boolean attribute is because it has exactly either of the two values TRUE or FALSE. The counts from this query and the previous one shows you that there are one-to-many relationships as SKUs rollup to only one CAFFEINATED value, but a single CAFFEINATED value can have many SKU children.

This script returns TRUE and FALSE values as depicted in the following screenshot:

Select CAFFEINATED, Count(SKU) As Cnt 
  From PRODUCT T1
  Group By CAFFEINATED;

The scripts in step 5 show a one-to-many relationship between SKU and OUNCES. Script 2 of step 5 shows that OUNCES will make a good candidate for a NUMERIC attribute dimension. A NUMERIC attribute type is as the word implies a number. We can use a NUMERIC attribute's value in calculating scripts or outline formulas to create formulas based on the attribute assigned.

This query returns a list of numeric values as depicted in the following screenshot:

Select OUNCES, Count(SKU) As Cnt
  From PRODUCT 
  Group By OUNCES;

The scripts in step 6 show a one-to-many relationship between SKU and PKGTYPE as it returns exactly 13 unique records. Script 2 of step 6 shows the PKGTYPE that would make a good TEXT attribute or an alternate dimension. A TEXT attribute allows for a comparison in calculations and a selection of a member based on the attribute assigned. Text attributes are the default attribute in Essbase.

This query returns a list of text values as depicted in the following screenshot:

Select PKGTYPE, Count(SKU) As Cnt
  From PRODUCT 
  Group By PKGTYPE;

The scripts in step 7 also show a one-to-many relationship between SKU and INTRODATE. Script 2 of step 7 shows that INTRODATE is, as the name implies, a date and as such will make a good DATE attribute dimension. You can use DATE attributes in calculation as well. We can compare different Products, for example, based on the DATE attribute for specific Market. Date attributes from January 1, 1970 through January 1, 2038 are supported.

This query returns a list of date values as depicted in the following screenshot:

Select INTRODATE, Count(SKU) As Cnt
  From PRODUCT 
  Group By INTRODATE;

Moreover, in step 8 you will see a one-to-one relationship between SKU and PRODUCTID, which means there is exactly one PRODUCTID for each SKU, and one SKU will rollup to exactly one parent. Script 2 of step 8 shows that this will more than likely be the main rollup in your PRODUCT dimension. This is also intuitive as the SKU in this case is the column representing the product:

Finally, the relationship between FAMILYID and PRODUCTID is a one-to-many relationship, shown in step 9. A possible outcome of these findings is to create a dimension and hiearchy with three generations as depicted in the following table:

In real world situations, you may come across scenarios where your relationships are not always one-to-one or one-to-many. Keep a note of these exceptions as you may need to concatenate columns, create alternate rollups with shared members, add a prefix or suffix, create a new dimension, or turn on duplicate members in your outline in order to deal with these issues. We will go over some of these in other chapters. That said, the preceding scripts will help you see these discrepancies before you begin building your cube, which will save you time later in your development. In addition, note that when building a BSO cube you can only assign attributes to a sparse dimension.

Refer to the recipes Adding attribute dimensions to hierarchies and Setting Essbase Properties in Chapter 3 to learn how to set up attribute dimensions and their properties in Essbase Studio.