Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Correct URL Creation in DWH Dim v Fact tables

Hi All, 

 

I'm relatively new to the DW & Reporting game, but I’m hoping you can share some wisdom with me. 

I'm looking to include URLs within a Datawarehouse to be used in PowerBI Reports. The body of the URL will be much the same but change based on the unique system id, e.g. order number, or username. 
When I see reference to these online it all seems to be created via measures in the PowerBI Desktop and just popped in wherever.
My question is when creating these within a DWH model, should they be created within the dimension table or the fact tables?

My gut says the Dimension table, but I'm not sure. 

Thank you!

Data model
URL
URL Creation

4 Replies

  • HashamNiaz's avatar
    HashamNiaz
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Anonymous !

     

    You are correct, you need to create those URL columns in Dimension tables. Also make sure when you are in Power BI Desktop, you categorized these URL columns as Web / Image URL base don your scenario.

     

    Regards,

    Hasham

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    If you are creating measures, it doesn't matter which table you create in. Because measures won't have different behavior if they are located under an unrelated table. For measures, the calculation would run regardless of the table that they are in. 

     

    If you want to create columns, it is important to select the right table, based on your requirements and data structures.

     

    Some documents may help you:

    Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Docs;

    Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Docs;

    Calculated Columns and Measures in DAX - SQLBI.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you both for tour help.

    As is the way, more business critcal issues popped up and I didn't get to continue with this since, but I really appreciate your time and advice! 

  • josef78's avatar
    josef78
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Technically, both is right. And two additionally are also right. You can create url column in DWH, you can create URL column in Power Query, you can create URL column as calculated in DAX, and you can create URL as measure in DAX.

     

    But do not forget think about performance.

    If you have small dim table with URL, no mater, is better move column close to DWH, when you can easily reuse in multiple scenarios.

    But if you have URL with unique id on large fact table, is much better move URL to measure using SELECTEDVALUE function based on id

    (because URL with unique id will be next high cardinality column with large strings and uneffective compression)