Creating Connections
One of the first things you will want to do in your project is add a connection to one or more source databases etc. A Connection defines the connection string and other properties to a Source database, file, SaaS provider etc.. Dimodelo Data Warehouse Studio supports many different types of Sources Systems including databases (Any database type that has an OLEDB driver, DB2, Oracle, SQL Server etc), Excel Files, Flat Files and XML files, ODBC sources including those that connect to SaaS providers like Dynamics and Salesforce etc.
In the below video you will see a demonstration of adding a database and ODBC connection to a Dimodelo Data Warehouse Studio project.
To define a new Database Connection do the following
- Expand the ‘Connections’ Node in the Solution Explorer tool window.
- Right click the Database folder and click ‘Create New’.
- Enter a Name into the Connection Name field. The Connection names ‘Staging’ and ‘Warehouse’ are reserved and can’t be used.
- Click the Configure button.
- Select a data source (e.g. Microsoft SQL Server) in the Data Source list, and then a Data Provider (e.g. ‘.NET Framework Data Provider for OLE DB‘) in the Data Provider drop-down. The dimodelo.desk.com site contains information about selecting the correct parameters for different types of Databases including Oracle.
- Click OK.
- Another dialog appears particular to the type of Data Provider selected. Fill in the details for you connection.
- Click Ok.
- For some providers (like Oracle) it is necessary to supply the Owner/Schema and Password details.
- Click the Save icon in the Dimodelo Menu.
- There should now be a Name.db node under Connections>Database in the solution.
After you create a connection, you must also create a new Source System. The Source System contains a little more meta-data information about the Source System.
Creating Source Systems
In addition to a Connection Manager, a Source System record needs to be created. The Source System record contains additional information about the nature of the Source system.
The most important aspect of creating a Source System record is to define a ‘Source System Abbreviation’. The ‘Source System Abbreviation’ is used to prefix table names in the Staging database. This ensures there is no naming conflicts in the Staging database between tables with the same name from different Source Systems.
Without a Source System, a Staging table can’t be defined, and the Source System won’t appear in the Sources tool window.
To Create a Source System, right click the Source Systems folder in the Solution explorer and click Create New. The following dialog appears.
- Source System Name. Give the source system a unique name. This is the name that appears in the Solution Explorer.
- Source System Abbreviation. Give the Source System a two or three letter abbreviation that is unique within the project. The ‘Source System Abbreviation’ is used to prefix table names in the Staging database. This ensures there is no naming conflict when staging tables with the same name from more than one Source System.
- Connection. Associate the Source System with one of the existing Connection Managers. This is mandatory.
In the future Source System records will be removed, and the information combined into the Connection information.
Important Note:
As you create, delete and change Connections, DA will create, delete and change the equivalent connection strings in all DA Config files associated with the project via a Visual Studio project configuration. However if a Connection is changed and it’s connection string is modified, DA will only modify the equivalent connection string in the DA Config file associated with the currently active project configuration.
There is more specific information about Connections tips and tricks at our support site dimodelo.desk.com.
Links: