Template Management

There are two template stores:

  • Local Template Store is located in the solution database. When you select [Save as Template], it will be stored locally.
  • Online Template Store is a shared online store hosted by Profitbase. You can upload/download templates between these.
To use a template, click [New ..] from the main navigator and then select From Local/Online Template in the Add dialog.
To store a template, click [Save As Template] from the main navigator on selected content, and select template content.

Template Management UI consist of following sections:

  • Toolbar (top) from where you select Local  or Online Template Store.
  • Template list (left) from where you can select a template. Right click to get a menu for delete and up/download.
  • Content section (upper right) displays the template header with name, type, description, version etc.
  • Template detail sections (lower right) has sections for:
    • Template content
    • Data Sets
    • Access settings

Templates and Access

The license the solution is opened with decides witch Templates you can access, and if you can upload, delete etc. A license is associated with a type. Contact Profitbase if your license is to limited for your needs.

Each Templates content item is associated with an Owner licence and an Availability setting. The availability is either Public or Private. Private availability limits access to the Template to the Owner and other licenses added to the Access settings section.

 

Connection Template

When adding a New external Data Source one will find a list of Connection Templates in the "Connection type" drop down.

You can define your own Connection Template via "Save As Template" in the Data Source Connection Settings form:



 

The built-in Connection templates are as follows:

CSV files via PowerShell -> Flat File Source and Import csv file data via PowerShell

DB2 Linked Server -> Allows for Connection to an AS400 DB2 database via linked server. More here: IBM DB2 Source

DB2 SSIS -> Allows for Connection to an AS400 DB2 database via SSIS. More here: IBM DB2 Source

Excel -> Allows for Connection to Microsoft Excel worksheets via .Net or SSIS (xls files Microsoft.Jet.OLEDB.4.0 Provider, xlsx files uses Microsoft.ACE.OLEDB.12.0 Provider). More here: Excel Source

File Delimited SSIS -> Allows for Connection to delimited data files via SSIS. (uses Microsoft.Jet.OLEDB.4.0 Provider) More here: Flat File Source

JSON files via PowerShell -> JSON Data Source

Microsoft Access SSIS -> Allows for Connection to a Microsoft Access database via SSIS. (mdb files uses Microsoft.Jet.OLEDB.4.0 Provider, accdb files uses Microsoft.ACE.OLEDB.12.0 Provider)

MSSQL Linked Server -> Allows for Connection to an MSSQL database via linked server.

MSSQL SSIS -> Allows for Connection to an MSSQL database via SSIS.

MSSQL MBS NAV-> Allows for Connection to an MSSQL NAV (Navision) database. (NAV uses Company names as prefixes on tables. This Connection template has customized metadata SQLs to accommodate this by, via the use of Tags and a custom table prefix, enabling looping over several/all tables simultaneously).

MSSQL Multiple Databases -> Allows for Connection to an MSSQL ERP system that are split into multiple databases. (i.e. Visma Business can use company names as database names. This Connection template has customized metadata SQLs to accommodate this by, via the use of Tags and a custom table prefix, enabling looping over several/all databases simultaneously).

MSSQL Multiple Schemas-> Allows for Connection to an MSSQL ERP system that have tables separated in multiple schemas. (i.e. AdventureWorks or WorldWideImporters dw demo databases group tables by schema. This Connection template has customized metadata SQLs to accommodate this by, via the use of a custom table prefix).

ODBC -> Allows for Connection to a data Source via ODBC. More here: ODBC Source

Oracle Linked Server -> Allows for Connection to an Oracle database via linked server. More here: Oracle Source

Oracle ODBC -> Allows for Connection to an Oracle database via ODBC. More here: Oracle Source

Oracle SSIS -> Allows for Connection to an Oracle database via SSIS. More here: Oracle Source

XML files via PowerShell  -> XML Data Source

 

MSSQL:

Authentication

There are two security authentication options. They are: 

Use Integrated Security

When Integrated Security is selected then your Microsoft Windows security profile will be authenticated with the selected SQL Server where the Profitbase solution is stored. Integrated Security will not work when the SQL Server is located on a Server machine that does not have domain users or trust. When this problem occurs, use User name and password option to log on.

User name and Password

When User name and password is selected then the User name and password that you enter will be authenticated with the selected SQL Server.

 

Oracle, DB2 or ODBC:

If Linked Server is used, all existing linked servers are listed in the drop down.

If ODBC is used, all existing ODBC DSNs are listed in the drop down. (NB! Profitbase does not have options of creating ODBC DSN’s so this has to be added manually beforehand)

If SSIS is used on source types other than MSSQL the server name points to different things depending on the source type:

Oracle:

Server name refers to a Net Service Name (TNS Name) or Connection string (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[HostName])(PORT=[PortName]))(CONNECT_DATA=(SERVICE_NAME=[OracleSID]))). (NB! Profitbase does not have options of creating TNS names so this has to be added manually beforehand)

DB2:

Server name refers to a local iSeries Connection set up through an iSeries Client.

 

File, Excel or SSIS Package:

Choose a valid File Folder Path and optionally "View" Default File to validate that the settings.