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

Create historical on live link

Hello,

 

I have used the salesforce object connector to access our data.

 

As, the data is reflective of what is in the system just now. How do I go about creating historical data? For example, headcount data/chart taken at the start of the each month.

 

Thanks

Ross

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

Each Salesforce object has a "buddy" object that tracks field changes. For example the "Opportunity"  object has a "Opportunity Field History"  object where value changes for the most relevant fields are recorded.  Ask your Salesforce administrator for details.  

You can use that data to reconstruct the status of the object at any point in time.  I would recommend you do that outside of Power BI though as it is compute heavy. We do it as a scalar function in SQL Server  (SFDC event history data is piped to SQL server, SQL server does the heavy lifting, and Power BI connects to the views/tables).

 

This approach is far superior to any snapshot based "solution".

View solution in original post

3 REPLIES 3
lbendlin
Super User III
Super User III

I mean, you can try to use VBA, but it will be rather slow and Excel may not be able to handle the volume (we have many millions of rows in our event tables, for example).  Better use something more robust.

 

To give you an idea of the required computation here is the scalar-value SQL function:

CREATE FUNCTION [SFDC].[OpportunityStatus] 
(
	-- Add the parameters for the function here
	@ID as varchar(20)
	,@TimeStamp as datetime
	,@CreatedDate as datetime
	,@Field as varchar(50)
	,@CurrentValue as varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @TimeStampValue as varchar(255)
	SELECT @TimeStampValue=
	                -- return NULL when the opportunity was not alive at the time
	                case when @TimeStamp < @CreatedDate then NULL 
	                else  isnull((SELECT TOP 1 [New Value] FROM [sfdc].[OpportunityFieldChanges]
					WHERE [Opportunity Id] = @ID AND [Field   Event] = @Field AND [Edit Date] <= @TimeStamp
						order by [Edit Date] desc, [History ID] COLLATE SQL_Latin1_General_CP437_BIN ) -- any change data before the snapshot? [Edit Date] desc or [History ID] ?
						,isnull((SELECT TOP 1 [Old Value] FROM [sfdc].[OpportunityFieldChanges]
					WHERE [Opportunity Id] = @ID AND [Field   Event] = @Field AND [Edit Date] > @TimeStamp
						order by  [Edit Date],[History ID] COLLATE SQL_Latin1_General_CP437_BIN desc)  -- any change data after the snapshot?
						,@CurrentValue)) -- use the current data from SFDC if no changes have been recorded
					end
	-- Return the result of the function
	RETURN @TimeStampValue

END

 

rossut
New Member

@lbendlin I don't think I will be able to use a SQL server, do you think I can connect via excel then through Power BI?

lbendlin
Super User III
Super User III

Each Salesforce object has a "buddy" object that tracks field changes. For example the "Opportunity"  object has a "Opportunity Field History"  object where value changes for the most relevant fields are recorded.  Ask your Salesforce administrator for details.  

You can use that data to reconstruct the status of the object at any point in time.  I would recommend you do that outside of Power BI though as it is compute heavy. We do it as a scalar function in SQL Server  (SFDC event history data is piped to SQL server, SQL server does the heavy lifting, and Power BI connects to the views/tables).

 

This approach is far superior to any snapshot based "solution".

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors