Versioned fact table report

[Anonymous]

I have a versioned fact table which has an effective start date and end date for each row. So the fact table supports the snapshot data at a point in time. Say we want to get a Total Of Sales each month based on the snapshot of the table on 20th of each month. The solution I have is to build a snapshot fact table for the snapshot as at 20th each month in the database, then build report on Power BI based on the snapshot table. That should work.

My question is that is there other way that we can achieve this without creating the snapshot table in the database. Somehow directly implemented in Power BI? Sorry this is almost like a modelling question. But just want to see if anyone has done it in Power BI. I think it is not possible.

[ankitpatira · Accepted solution]

@Anonymous

1.  Possibly our prefered option is direct connect, so any tricks in the loading is not relevant in that case.

You can still do this with DirectQuery depending on what your data source is. If it is sql server then you can take advantage of query editor even with directquery.

But yes agree if rules are going to be complex and easy to implement via source do it that way (saying that you can still do it via power bi but depends on how complex rules are).

[v-sihou-msft · Accepted solution]

@Anonymous

In Power BI Desktop, it only takes the data from data source and render it. It doesn't support versioning. For your scenario, I don't think there's other way than using snapshot tables.

Regards,

[ankitpatira]

@Anonymous You can build your report like normal and in the report filters drop start date field from your fact table and set value for 20th for that filter. 

[Anonymous]

@ankitpatira

For a single selected month, yes it could be done via filters with a hard-coded date. But you just cannot do say total sales by month (last 10 months for example)  that kind of analysis - as there are multiple months involved. Hope this makes sense.

[ankitpatira]

@Anonymous Since you only want 20th of each month in power bi desktop when you import data via query editor you can extract out date into a seperate column and then set that new column to be 20 via filter. That way you only looking at 20th of each month. 

[Anonymous]

@ankitpatira thanks for the suggestion. But I think this is not going to be a solid solution, so I assume the answer will be a NO. The snapshot fact table will have to be maintained in the database - which is not a big deal if that is the case.

Reasons:

1. Possibly our prefered option is direct connect, so any tricks in the loading is not relevant in that case.

2. The actual rule for the snapshot could be much more complex than the 20th each month. Could be varied each month etc.

[ankitpatira · Accepted solution]

@Anonymous

1.  Possibly our prefered option is direct connect, so any tricks in the loading is not relevant in that case.

You can still do this with DirectQuery depending on what your data source is. If it is sql server then you can take advantage of query editor even with directquery.

But yes agree if rules are going to be complex and easy to implement via source do it that way (saying that you can still do it via power bi but depends on how complex rules are).