There is probably an easy formula but I can not find it. We add a last update date to all our reports, so for any pbi reports accessing the OLAP cube, we want the last processed date. I could use a max transaction date but that makes weird reading during holidays or weekends and would suggest there has been no update. Any ideas?
Thanks in advance for the help!
Date
Votes
2 comments
-
CascadiaDataworks You could use the DW_TimeStamp field in the data. What I did:
1. Pick the DWH table that will always be deployed and executed with the relevant cube. For example, I wanted to add the Last Updated to my Sales cube so I picked the Sales Posted Transactions table in the DWH>
2. Right click on the table name, go to Advanced->Show System Control Fields. You will now see at the bottom of the list of fields "System Fields" which you can expand to show DW_TimeStamp.
3. Add Field in the Sales Posted Transaction table (in my example) named Last Updated with a Data Type of 'Date'.
4. Right click the new Last Updated field and select Field Transformations.
5. Leave the Operator as 'Custom Value' and click 'Add'
6. Drag DW_TimeStamp to the main window and hit 'Ok'
7. Add 'Last Updated' to your Sales Posted Transactions table in your Sales semantic layer.
8. Deploy and Execute and you should now have the field in the cube to use in Power BI. Every time that original table is updated in the JDM, this field should update.
Hopefully that works for you. Because people often have different cubes updating at different times, I focused on one Cube rather than the whole project. This would show every time the Sales cube updates but could be used for other cubes.
-
Vicky P Thanks for the response. This might be the best solution I guess.
It has the downside that with incremental updates, you might worry end-users that the process has not run in the weekend for example. But I am probably being to careful - currently the processes are full load, so it really is a non-issue today.I have created a separate procedure that requests the last load time of the cube and saves it in a separate table. But this also has its own issues. I probably am going to go with your solution.
Thanks!
Please sign in to leave a comment.