Below is a quick guide on how to use BuildSchema to improve access to your SQL database by selecting only the tables you need to reference.
Please take a look at the attached schema (below). This has been reduced down to two simple examples.
- Orders has 1..N [Order Details]. So in the <keys> node for each there is a single step <trail> to the other table. So this defines that 1:N relationship in both directions.
- Employees are mapped to Territories using the EmployeeTerritories join table. In both Employees and Territories there is a two step <trail> where each step is seperated by a semicolon.
So under <table name="dbo.Employees" type="user"> you have:
Which means to get from dbo.Employees (the <table>) to dbo.Territories (the trail dest table=), you use the two joins dbo.Employees.EmployeeID=dbo.EmployeeTerritories.EmployeeID and dbo.EmployeeTerritories.TerritoryID=dbo.Territories.TerritoryID.
If you use the attached file and have Northwind on a SQL Server there named MSSQL.WINDWARDREPORTS.COM; username demo; password demo, then AutoTag will show it as having 5 tables and will be able to auto-join the order pair and the employee/territory triplet. And that is all it will see, because it will pull no metadata from the database, it gets this info solely from the schema file.
To use it you then add a data source, select "Sql Schema Datasource" (near the bottom) as the vendor, then give it this filename. Please note that the connection information is in the schema.xml file and must be correct. The best way to get that is look at the connection string created in AutoTag to connect to your database.