My perspective is traditional RDBMS (Oracle), and retrieving data with a reporting tool like Business Objects or straight SQL Nothing too fancy, just dump to Excel. But JOINing a lotta tables.

We're migrating to SF but our analytics team still needs to pull all manner of data. Right now using SOQL using Workbench - this is painful imo.

Einstein doesn't seem like the right tool for us. It's set up for fancy dashboards and such, but that's not what we need to do.

SOQL is extremely limited imo. For example, I have two healthy queries that each "join" to several other tables. But I want to JOIN my two healthy tables, in the same manner one could JOIN to VIEWS fom Oracle. 

I don't think standard Salesforce reporting is going to work, although I haven't tried it out. But it just not seem like the way to do all of our reporting (data pulls).

For example, SOQL doesn't have logical LEFT JOINs, as I understand it. Say I have student(contacts), applications, and sports recruiting. In Oracle, I would start with STUDENTS, then LEFT JOIN to both applications and sports recruting. 

But in SOQL, it looks to me like you START with the "leaf" (sports recruiting), then work you way to the branch (applications) and THEN to the "root" (STUDENTS).
- so you wouldn't be able to count the number students with no applications, and the number of students with no sports recruiting.

So my only architecture option is to suck the data out of SF into a traditional RDBMS and then JOIN everything (normally)?

Hope this makes sense.

At the moment I have two main queries (College Application and Recommendations). Both "join" up to Contact for Student Name, Gender, etc., that I'm doing using SOQL in Workbench.

But I'd like to keep these as "clean" as possible, but then JOIN them together to create a larger dataset.

Is this possible? Is it possible to create a VIEW (like in Oracle) and then "JOIN" the two views?

*Having  a hard time grasping how to pull data out of SF, for various analytical purposes.

 

I'm learning all the nuances of SOQL via Workbench, and as you know, it's painful for an Oracle SQL person.

I'm a data analyst, and my goal is to pull Salesforce data into Excel.

I'm just trying to do a case statement in SOQL, such as 
     case gender
          when 'F' then 'Female'
          when 'M' then 'Male'
          else 'Other'
      end

Can this be done in Workbench? I'm told you can't do it, but I need to know for sure, and what are the alternatives.

I asked one of our devleopers, and his response was a switch statement in Apex. I don't think Apex is going to be my tool, plus it's a lot more programm'y than a simple SQL CASE statement.

It looks like Einstein is the way to do this, but I haven't investigated that yet, mainly I'm trying to learn the Data Model and to just pull data. Oracle seemed a lot better in that regard.

Not sure the best topic to choose so just chose "General Development", since there's no SOQL topic.

Thank you. 

We will eventually have a massively large CONTACT table and want to restrict access by user roles.

How can this be done? 

I want to do a "left join" of CONTACT table to TargetX_SRMb__Application__c, but I want to filter the Application table by "Class_Year = '2021'

and can't seem to get that.

I can do a successful query like this but ALL rows are returned, so it's ignoring the WHERE clause in my inline (SELECT ...)

select 

ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,

FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c,

(select 
TargetX_SRMb__Application_Decision__c,
TargetX_SRMb__Start_Term_Year__c,
AIS_Feeder_Source__c
from TargetX_SRMb__Application__r
where TargetX_SRMb__Start_Term_Year__c = '2021')

from Contact
where AIS_Record_Type_Name__c = 'Recruitment_Manager_Student'
  and TargetX_SRMb__Application__r.TargetX_SRMb__Start_Term_Year__c = '2020'

But if I flip the query I can't get it to work ... CONTACT__r doesn't work ... 

select 
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c,
(select 
AIS_PRMI_Id__c, 
ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,
FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c, 
usna_international__c
from Contact__r
)
from TargetX_SRMb__Application__c
where targetx_srmb__start_term_year__c = '2021'

Just learning SOQL so it's a challenge.

I'm trying to do this, logically:
(
select [this and that],
   (select [other stuff] from Table_B)
from Table_A
)
where Table_B_field = '2021'

I can do this in Oracle with a snap, but how to do so in Salesforce / SOQL / Workbench.
 

*** No idea what Topic to put this under ...

​​​​​​​

 

We are using Salesforce and planning on using Big Objects as the Data Warehouse, then use Einstein to pull from Big Objects and then report fancy Dashboards and such. (We are migrating from an Oracle environment, some components in Production.)

But we don't have Big Data, we have little data, so I am confused why we would need a "Big Data" solution in Big Objects. Also, as an Oracle developer, the 100 table limitation in Big Objects (seriously) perplexes me.

Couldn't our Data Warehouse simply be another Salesforce org and just move the data there (and switch it up a bit), and then report out of it normally (or use Einstein)? All the industrial strength data warehouse solutions are for big data - Azure, AWS, Google Cloud Platform, etc.

 

I am new to Salesforce and come from a reporting / SQL background and perspective. I started to look at how to write SQL (SOQL) in Salesforce.

This link answered my question exactly - it's painful. So that gave me the quick answer, and a headache as well.

On this link it describes a limitation of Salesforce in regards to SQL:
SOQL doesn’t support all advanced features of the SQL SELECT command. For example, you can’t use SOQL to perform arbitrary join operations, use wildcards in field lists, or use calculation expressions.

So my specific question is now, what does it mean "you can't use SOQL to perform arbitrary join operations"? Is this the same as simply saying "You can't do join operations"? What exactly is the adjective "arbitray" trying to convey.

And per the first link of the guy who was "OMG how do I write a normal SQL statement in Salesforce?", any suggestions on how to get started? I do have the Trailheads, and I see SOQL modules in "Apex Basics & Database", but do you have any tips on getting started.

My perspective is Oracle SQL and BusinessObjects for your traditional reporting.

Also, I think this is my first post, I don't see any Category for "Reporting", "Einstein", etc. So I put it in 
 

My perspective is traditional RDBMS (Oracle), and retrieving data with a reporting tool like Business Objects or straight SQL Nothing too fancy, just dump to Excel. But JOINing a lotta tables.

We're migrating to SF but our analytics team still needs to pull all manner of data. Right now using SOQL using Workbench - this is painful imo.

Einstein doesn't seem like the right tool for us. It's set up for fancy dashboards and such, but that's not what we need to do.

SOQL is extremely limited imo. For example, I have two healthy queries that each "join" to several other tables. But I want to JOIN my two healthy tables, in the same manner one could JOIN to VIEWS fom Oracle. 

I don't think standard Salesforce reporting is going to work, although I haven't tried it out. But it just not seem like the way to do all of our reporting (data pulls).

For example, SOQL doesn't have logical LEFT JOINs, as I understand it. Say I have student(contacts), applications, and sports recruiting. In Oracle, I would start with STUDENTS, then LEFT JOIN to both applications and sports recruting. 

But in SOQL, it looks to me like you START with the "leaf" (sports recruiting), then work you way to the branch (applications) and THEN to the "root" (STUDENTS).
- so you wouldn't be able to count the number students with no applications, and the number of students with no sports recruiting.

So my only architecture option is to suck the data out of SF into a traditional RDBMS and then JOIN everything (normally)?

Hope this makes sense.

At the moment I have two main queries (College Application and Recommendations). Both "join" up to Contact for Student Name, Gender, etc., that I'm doing using SOQL in Workbench.

But I'd like to keep these as "clean" as possible, but then JOIN them together to create a larger dataset.

Is this possible? Is it possible to create a VIEW (like in Oracle) and then "JOIN" the two views?

*Having  a hard time grasping how to pull data out of SF, for various analytical purposes.

 

I'm learning all the nuances of SOQL via Workbench, and as you know, it's painful for an Oracle SQL person.

I'm a data analyst, and my goal is to pull Salesforce data into Excel.

I'm just trying to do a case statement in SOQL, such as 
     case gender
          when 'F' then 'Female'
          when 'M' then 'Male'
          else 'Other'
      end

Can this be done in Workbench? I'm told you can't do it, but I need to know for sure, and what are the alternatives.

I asked one of our devleopers, and his response was a switch statement in Apex. I don't think Apex is going to be my tool, plus it's a lot more programm'y than a simple SQL CASE statement.

It looks like Einstein is the way to do this, but I haven't investigated that yet, mainly I'm trying to learn the Data Model and to just pull data. Oracle seemed a lot better in that regard.

Not sure the best topic to choose so just chose "General Development", since there's no SOQL topic.

Thank you. 

We will eventually have a massively large CONTACT table and want to restrict access by user roles.

How can this be done? 

I want to do a "left join" of CONTACT table to TargetX_SRMb__Application__c, but I want to filter the Application table by "Class_Year = '2021'

and can't seem to get that.

I can do a successful query like this but ALL rows are returned, so it's ignoring the WHERE clause in my inline (SELECT ...)

select 

ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,

FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c,

(select 
TargetX_SRMb__Application_Decision__c,
TargetX_SRMb__Start_Term_Year__c,
AIS_Feeder_Source__c
from TargetX_SRMb__Application__r
where TargetX_SRMb__Start_Term_Year__c = '2021')

from Contact
where AIS_Record_Type_Name__c = 'Recruitment_Manager_Student'
  and TargetX_SRMb__Application__r.TargetX_SRMb__Start_Term_Year__c = '2020'

But if I flip the query I can't get it to work ... CONTACT__r doesn't work ... 

select 
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c,
(select 
AIS_PRMI_Id__c, 
ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,
FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c, 
usna_international__c
from Contact__r
)
from TargetX_SRMb__Application__c
where targetx_srmb__start_term_year__c = '2021'

Just learning SOQL so it's a challenge.

I'm trying to do this, logically:
(
select [this and that],
   (select [other stuff] from Table_B)
from Table_A
)
where Table_B_field = '2021'

I can do this in Oracle with a snap, but how to do so in Salesforce / SOQL / Workbench.
 

*** No idea what Topic to put this under ...

​​​​​​​

 

We are using Salesforce and planning on using Big Objects as the Data Warehouse, then use Einstein to pull from Big Objects and then report fancy Dashboards and such. (We are migrating from an Oracle environment, some components in Production.)

But we don't have Big Data, we have little data, so I am confused why we would need a "Big Data" solution in Big Objects. Also, as an Oracle developer, the 100 table limitation in Big Objects (seriously) perplexes me.

Couldn't our Data Warehouse simply be another Salesforce org and just move the data there (and switch it up a bit), and then report out of it normally (or use Einstein)? All the industrial strength data warehouse solutions are for big data - Azure, AWS, Google Cloud Platform, etc.

 

I am new to Salesforce and come from a reporting / SQL background and perspective. I started to look at how to write SQL (SOQL) in Salesforce.

This link answered my question exactly - it's painful. So that gave me the quick answer, and a headache as well.

On this link it describes a limitation of Salesforce in regards to SQL:
SOQL doesn’t support all advanced features of the SQL SELECT command. For example, you can’t use SOQL to perform arbitrary join operations, use wildcards in field lists, or use calculation expressions.

So my specific question is now, what does it mean "you can't use SOQL to perform arbitrary join operations"? Is this the same as simply saying "You can't do join operations"? What exactly is the adjective "arbitray" trying to convey.

And per the first link of the guy who was "OMG how do I write a normal SQL statement in Salesforce?", any suggestions on how to get started? I do have the Trailheads, and I see SOQL modules in "Apex Basics & Database", but do you have any tips on getting started.

My perspective is Oracle SQL and BusinessObjects for your traditional reporting.

Also, I think this is my first post, I don't see any Category for "Reporting", "Einstein", etc. So I put it in