I've been using Tableau for a little over a month now, primarily using SQL Server data sources. I make heavy use of the "custom SQL query" option and pre-written stored procedures to prepare my data before Tableau visualization.
Now, I've been asked to visualize data from Salesforce. I can connect to Salesforce through the Tableau connector and this works fine for some basic data visualization, but I don't really have the option to "prepare" the data in the way I need to for some more complicated work.
I don't have a workbook to provide, but here is a simplified example of what I'm trying to do: From Salesforce, I can return a table of "Case" records, with columns "AccountId", "AccountCreationDate", "CaseId", and "CaseCreationDate". I have three calculated fields. The first one (DaysSinceAccountCreation) is a level of detail calculation on AccountId that returns DATEDIFF('day',AccountCreationDate,TODAY()); if this value is greater than 90, than 90 is returned. The second one (CountOfRecentCases) is a level of detail calculation on AccountId that returns a count of CaseIds where DATEDIFF('day',CaseCreationDate,TODAY()) is 90 or less. The third one (AccountMetric) is the quotient of these two: (DaysSinceAccountCreation)/(CountOfRecentCases). This works perfectly when I want to calculate AccountMetric for today, but I need to also visualize AccountMetric over time.
If I were doing this in SQL Server, I could write a query that creates date values for the time range I'm looking at and then substitute these values in for "TODAY()". I can't figure out how to do this with Salesforce though. I created a "date table" in SQL Server that returns a two column table of Date and AccountId and tried blending this with the Salesforce data source but the calculated fields aren't returning any data (I admittedly am not very familiar with data blending limitations).
Reading through this thread, I'm considering trying out DBAmp to dump Salesforce data into the more query-friendly SQL Server, but I just wanted to check if anyone has any better suggestions. Thanks!