Correct URL Creation in DWH Dim v Fact tables

[T0_0H]

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!

[HashamNiaz · Accepted solution]

Hi @T0_0H !

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

[josef78]

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)

[T0_0H]

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! 

[Icey · Accepted solution]

Hi @T0_0H ,

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.

[HashamNiaz · Accepted solution]

Hi @T0_0H !

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