Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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".
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
@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?
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |