Flatten table at Universe Level

building semantic layer
1

Hi all,

I have the following two tables in the DB:

Product

Product_ID (PK)
Attribute_ID

Product_Attribute

Attribute_ID (PK)
Attribute_Code (PK)
Attribute_Value

Examples of the Attribute_Code column are: Size, Colour, Expiry_Date, etc.

I want to flatten each attribute I need so that it’s presented as follows in different columns:

Product_ID||Size||Colour
123456||20||Black

In SQL form, this is what I have:

SELECT 
( SELECT ATTR.ATTRIBUTE_VALUE FROM PRODUCT_ATTRIBUTE ATTR WHERE ATTR.ATTRIBUTE_CODE = 'SIZE' 
  AND ATTR.ATTRIBUTE_ID = PROD.ATTRIBUTE_ID ) AS SIZE,
( SELECT ATTR.ATTRIBUTE_VALUE FROM PRODUCT_ATTRIBUTE ATTR WHERE ATTR.ATTRIBUTE_CODE = 'COLOUR' 
  AND ATTR.ATTRIBUTE_ID = PROD.ATTRIBUTE_ID ) AS COLOUR
FROM PRODUCT PROD

Any ideas on how I’d go about implementing this in my universe?

Note: There is already a pivot solution that flattens this at the DB level using similar code to the above,
however I’m exploring other alternatives.

Cheers!

EnIgMa (BOB member since 2009-06-05)

2

Hi EnIgMa,

why u want to solve this in universe itself , u can achive this in reporting side.

As per my understanding you have a table record like this
product table

prodid,attid
123,1
123,2
124,4
125,1

attribute table

attid,attcode,attvalue
1,size,25
2,color,red
3,exdate,20-05-2009
4,size,34

you want to dipaly detial like below

proddetail
123||25||red
124||34
125||25

Regards,
Pradeep

bopradeep :india: (BOB member since 2009-08-03)

3

Hi Pradeep,

The problem comes up because the attributes table is structured like this rather:

attid,attcode,attvalue
1,size,25
1,color,red
1,exdate,20-05-2009
2,size,34
2,color,red

This gives me these results when joined:

proddetail
123||25
123||red
123||20-05-2009

Thanks

EnIgMa (BOB member since 2009-06-05)

4

Well, you can always create a dervied table using pretty much exactly the SQL you have up there.

You could also turn your Product table into a pseudo fact table, by creating a series of derived tables. Create a “Prod_Size” derived table from the Product_Attribute table where Attribute_Code = “size”. Join that derived table back to the Product table, and now the Attribute_Value column becomes simply the Size value. Rinse, lather, and repeat for all the other attributes.

(Note, either of these methods requires the possible values for Attribute_Code to remain pretty static. Any new code added will require you to go either add a new line to the SQL of the first one, or add a new derived table to the second one.)

Lugh (BOB member since 2009-07-16)