Concatenating (Union?) Dimension Tables

Answered

Comments

3 comments

  • Avatar
    Andrew Block

    First question I would have is why do you want to combine the two dimension tables into one? Are they all related? Otherwise I don't see any problem with have many dimension tables...just from the information you presented it seems like you could almost have 3 dimension tables (campaign, market, client) but without some idea of the data behind it, its just a guess....for some insight we have ecubes that can have anywhere from 1-7 dimension tables

     

     

    0
    Comment actions Permalink
  • Avatar
    Gowtham Senthilkumar (Edited )

    Hello David, 

    The consolidated dimension table that you're trying to be build needs to be built using Custom SQL expression. 
     
    Try the SQL expression below and let me know if that works, 
     
    - Rename the current Dim Campaign table into Dim Campaign Sponsored Content and then, run a 'Changes Build' 
    - Create a Custom SQL Table and name it as Dim Campaign
    - Alter the SQL query that is below according to the table names/field names of your model. 
     
         Select campaigninternal, campaign, Market, Region, Client
         From Dim Hulu
     
         UNION 
     
         Select campaigninternal, campaign, Market, Region, Client
         From Dim Youtube
     
     
    - The above SQL will solve your issue creating a Dim Campaign table which would be consolidated. Kindly ensure, the field datatypes of two tables, that are being UNION'ed, are the same.
     
    Hope that helps. 
     
    Thanks, 
    Gowtham Senthilkumar
    0
    Comment actions Permalink
  • Avatar
    Gowtham Senthilkumar

    Hello Andrew, 

    You are right - there could be as many dimension tables as you need that can be connected to the fact table. But, in this case, Davis is trying to consolidate two groups of the same dimension into one dimension table. That is the reason he is looking to combine them.

    Hope that clarifies. 

    Thanks,

    Gowtham Senthilkumar

    0
    Comment actions Permalink

Please sign in to leave a comment.