Handling on-going events [Beginner's Question]

[Anonymous]

Hi,

I am trying to do analysis on how weather affects demand for city bikes in Oslo. So far, I have three tables:

Weather observations are done on hourly basis, started_at and ended_at from DimTours are datetime. Now to my questions.

1.I have to connect this whole thing somehow. I could create a fact table but then which date do I use it to join the data from DimTours and DimWeather, started_at or ended_at? 

2. I cannot figure out how to handle on-going events(bike tours in this case) in a way that I can use date slicer to show the count of bicycles that are rented at this given moment and not only the tours that have either started or ended at this point. Besides, it seems like Power Bi let's me to create only points on the map based on one latitude and one longitude but I have two of them. One point when the tour has started and one when it had ended.

It may be very simple but I cannot find out how to handle those situations where I have time intervals and multiple date columns for one dimension, so any hints and helpful links are also appreciated.

Cheers!

[V-lianl-msft]

Hi @Anonymous ,

You can refer to this blog:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109 

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

[lbendlin]

Your DimTours and DimObservation tables are actually "fact" tables.

Fact tables contain values that can be treated mathematically (sum, avg etc). Dimension tables contain filter dimensions.

Having said that, Power BI doesn't really work like that. Facts and Dimensions are just an artificial construct to help us discuss data

Same with data models. You will hear Star schema, or Snowflake.  While they are good guidance (and considered best practice by some) they are not strictly required.

Anyway, back to your data.  You have multiple date fields in your fact tables. the Date table has hourly granularity. That is unusal, but possible.  However, you currently have no way to link it to your observation table. Does the "time" column have a time or a datetime value? You need to find some composite key between the two tables, someting like YYYYmmDDhh,  so you can link them etc.

For the Tours table you need to figure out which datetime field is "primary". Power BI only allows one active relationship between tables.

[Anonymous]

@lbendlin 

Hi, thank you for your answer and clarification! I have created this table with hourly granularity because initially I was thinking about linking everything based on hour and I've had star schema in mind.  As for the observation table, I have one set of observations per hour:

But then, to connect my tables, I still need some bridge between them. Otherwise I won't be able to visualise for example average duration of the tour vs average temperature.

edit: Actually, I think I could use YYYYmmDDhh as a key, I just need to add a row to my tours table everytime a tour exceeds one hour(?) and then I have many to one relationship. Gonna try that!

So the only solution would be to create two relationships(one active and one inactive) to both date fields and then USERELATIONSHIP() to achieve expected output?  

[lbendlin]

that's one option, but (by far) not the oly one.  You could also create a reference of your dates table so you can link the trip start to the dates table and the end time to the reference.  Or you could use LookupValue etc.