- Identify what Xtraction views you want to set up for the new adapter (or at least identify the first view to get started)
- Determine what pieces of information you want to have in the view(s)
- Determine what database fields contain those pieces of information, including what tables the fields reside in.
- Determine if any manipulating of that data needs to occur (for example, the values are stored as 1 and 0 in the data, but we want to show "Yes" and "No" to the user)
- Determine what labels to give each field that will be displayed to the user in the interface. Our standard is to use the labels found in the native application that the users are used to seeing for continuity.
- Determine the relationships between all of the tables
- Add a new data source to the data model and configure its settings, including the connection string
- Add a new view to the new data source
- Add the tables to the new view
- Add the fields to each table
- Add the joins between the tables
- Add 1 or more URL definitions to the view properties, if applicable
- Add 1 or more subquery filter definitions to the view properties, if applicable
- Add a default column list set to the view properties.
- Repeat steps 8 -14 for each view determined in step 1
Steps 1-6 will take the longest time if you don't already know the database schema. Places to look for information about these items include:
- The native application interface
- The native application vendor documentation
- Online searches for people discussing this information
- The SQL logic behind any out-of-the-box reports provided by the vendor, if available
- The SQL logic behind any SQL Views, Stored Procedures, or User Defined Functions found in the database
Steps 9-11 are usually pretty straightforward; however, sometimes the way the data is stored in the database or how you want to use the data may require some more advanced modeling techniques that correspond to more complicated sql queries to get the results you want.
You will find that a lot of this is an iterative process.
I've been data modeling in Xtraction for over 10 years and my experience is that steps 1-6 usually takes the bulk of the time, unless it happens to be a database you already know well. It is also further complicated when the vendor does not document their database schema (and most don't).
I hope this helps.