cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T0_0H
New Member

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!

2 ACCEPTED SOLUTIONS
HashamNiaz
Solution Sage
Solution Sage

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

View solution in original post

Icey
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
josef78
Super User
Super User

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
New Member

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
Community Support
Community Support

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.