Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppvinsights
Helper II
Helper II

Date and time in an IoT project

Hello Community,

 

In an IoT project we have some machines which send several times a day messages. So we have a dimension table

  • DimMachine

and lets say a fact table

  • FactMessage

The fact table has a timestamp. And several kpis like a charging rate.

An active machine is a machine which sends at least one message in a give time period. So I define

no of active machines =
COUNTROWS (
    FILTER (
        DimMachine,
        NOT ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactMessage ) ) )
    )
)

Because we want to see active machines "in a given period" we define a calendar-dimension DimDate. So we need to extract the date of the timestamp in the FactMessage.

Usually I would introduce a time dimension as well, extract the time as well and delete the timestamp in my fact data. But:

We want to have a line chart in which we can show some of the kpis of the messages. We want to see, if the charging rate was over a given limit - we say "more than three time over the limit" then we have a problem. So we cannot show an average, min/max. We need to see the raw value.

 

If using DimDate and DimTime for the x-axis in a line chart, we introduce (for the customer) a strange drillthrough. It looks much nicer if I would use the timestamp of the FactMessage for the x-Axis, because so I have a continouus axis which is much nicer than the default drill-down axis (if using DimDate and DimTime).

 

But using the Timestamp I would use data from a fact table in an axis - which I would love to avoid (e.g.: if having another FactTable with say "errors" I could not bring this information in the same visual - because the timestamp of FactMEssage is not a shared dimension).

 

But introducing a combined Date/Time Dimension with 86.400 rows per day cannot be the solution for that.

 

Other pitfall if working with DimDate AND DimTime are:

  • when we have to show the raw data in a table, you alway have two columns: date and time - and you have to sort both. Using the timestamp you only need to sort one column. 
  • If you have any date/time calculation (e.g. "show me the last message before myself"), you first have to add date and time again, before you can calculate anything

Do you understand what I mean? How do you handle those situations?

Thanks

Holger

 

 

1 ACCEPTED SOLUTION

@ppvinsights 
perhaps no need to have an extremely huge size DateTime table, we only need a DateTime table that contains the exiating datetimes in our two fact tables.

 

DateTime =
DISTINCT (
    UNION ( ALLNOBLANKROW ( Fact.1[DateTime] ), ALLNOBLANKROW ( Fact.2[DateTime] ) )
)

 

View solution in original post

6 REPLIES 6
ppvinsights
Helper II
Helper II

 Hi,

yeah - it is hard to explain. The essence (after two days it is easier to tell something about the real problem 😉 ) is, that I need the timestamp in my axis.

==> I want to show testdata from a machine - like how much electricity was used when starting something. My customer want to see ALL results in a line chart. If I would use DimDate, the line chart would show a maximum or mean of my testdata. Even if I use DimDate AND DimTime as an axis, my customer would have to drill down to see every result without any aggregation. So I use the timestamp of the fact table because I do not have a "DimDateTime" Dimension. But: What if I need another kpi in the same line chart from another fact table? Because the timestamp is not a shared dimension...?

 

So I fully understand why you have to separate date and time in a filtertable/shared dimension. But what, if I need to have timestamps in my axis?

 

Is there any cool idea I do not see?

 

Holger

@ppvinsights 
You may try using TREATAS to change the lineage of the Fact.[DateTime] . Simething like 

CALCULATE ( 
    [Measure],
    TREATAS ( VALUES ( Fact.[DateTime] ), Fact.2[DateTime] )
)

Yeah, I understand - but works only, if the timestamp in Fact 1 is exactly the same as in Fact 2. I think I have a phantom problem and there is no obvious solution for a shared dimension with DateTimes...

 

@ppvinsights 
perhaps no need to have an extremely huge size DateTime table, we only need a DateTime table that contains the exiating datetimes in our two fact tables.

 

DateTime =
DISTINCT (
    UNION ( ALLNOBLANKROW ( Fact.1[DateTime] ), ALLNOBLANKROW ( Fact.2[DateTime] ) )
)

 

Yes, your are totally right! Because really often we just talk about <10 DataPoints per day. So I could introduce a DimDate, a DimTime and between the tose and the fact table I just add this TimeStamp-Union Table with two calulated "Date" and "Time" cols. 

Thanks for your time!

tamerj1
Super User
Super User

Hi @ppvinsights 

I'm trying to understand. Need some screenshots of your report or at least the shape of your report aling with the expected results. Cannot picture what are you trying to achieve. 
one possibility is to have extra two columns, one for date only connected to dim date and one for time only  connected to dim time. for any calculation that requires the full datetime value you can allways use the original column from the fact table. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors