The data access method for a relational database is the Structured Query Language (SQL). Whether data access is done from a database View, third-party reporting tool or an internal process, there was an SQL Query statement written to retrieve the data records from the database. All SQL Queries are written for a specific business purpose.
Utilizing the progressive learning nature of DSF you can teach the system the business context of your data by discovering with SQL Queries. Every SQL Query processed creates an Entity (Business Purpose). The more SQL Queries processed the better.
SQL Query Discovery can be access from the Discover section of the main menu on the Data Discovery tab:
SQL Query
Enter a valid SQL Query for your business purpose.
Not all SQL Queries can be fully discovered. If your query cannot be discovered, try simplifying the query and/or breaking it up into multiple smaller ones. DSF can learn even from a partial query as long as it can run. Any query processed successfully will make some data elements available for later use.
For example:
The HumanResources.vJobCandidate database View found in the Microsoft AdventureWorks sample database cannot be processed by DSF.
SELECT jc.[JobCandidateID], jc.[BusinessEntityID], [Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix], jc.[ModifiedDate] FROM [HumanResources].[JobCandidate] jc CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; /Resume')
Note: View Statement truncated for brevity in this example.
The CROSS APPLY statement is not supported by DSF. However, if you remove the CROSS APPLY and the corresponding data field Name.Prefix, DSF will still process and discover the other elements.
SELECT jc.[JobCandidateID], jc.[BusinessEntityID], jc.[ModifiedDate] FROM [HumanResources].[JobCandidate] jc
Trick
To create a Business Object (Entity ) in DSF which you know should exists, but was never created due to the lack of database View to process, create a virtual Business Object (Entity ) using an SQL Query. Just include the JOIN statements required to link the related tables and let DSF discovery do the rest.
For example, this SQL Query will create a virtual Business Object (Entity) that would expose ALL Vendor Table related data elements in the Microsoft AdventureWorks sample database:
SELECT Vendor.Name AS VendorName
FROM Purchasing.Vendor Vendor
INNER JOIN Person.BusinessEntityAddress BusinessEntityAddresses ON BusinessEntityAddresses.BusinessEntityID =Vendor.BusinessEntityID
INNER JOIN Person.Address Addresses ON Addresses.AddressID = BusinessEntityAddresses.AddressID
INNER JOIN Person.StateProvince AS StateProvinces ON StateProvinces.StateProvinceID = Addresses.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegions ON CountryRegions.CountryRegionCode =StateProvinces.CountryRegionCode
INNER JOIN Person.AddressType AS AddressTypes ON AddressTypes.AddressTypeID = BusinessEntityAddresses.AddressTypeID
INNER JOIN Person.BusinessEntityContact AS BusinessEntityContacts ON BusinessEntityContacts.BusinessEntityID =Vendor.BusinessEntityID
INNER JOIN Person.Person AS People ON People.BusinessEntityID = BusinessEntityContacts.PersonID
LEFT JOIN Person.PersonPhone AS PeoplesPhones ON PeoplesPhones.BusinessEntityID = People.BusinessEntityID
LEFT JOIN Person.PhoneNumberType AS PhoneNumberTypes ON PhoneNumberTypes.PhoneNumberTypeID =PeoplesPhones.PhoneNumberTypeID
LEFT JOIN Person.EmailAddress AS EmailAddresses ON EmailAddresses.BusinessEntityID = People.BusinessEntityID
Even though this query selects only 2 fields, DSF learns how to access hundreds of related fields that it will make available to users from the JOINed tables.
Discovering data by SQL Query is a fast and convenient way to teach DSF about your data. With a few tricks, you can create business context and expose hundreds of data elements in a very short amount of time.