Connect Northwind and build the model

Connect to Northwind and build a small sales model: customers and products describe each sale, and one sales row represents one order line. The model will supply the warehouse tables, loading pipeline and Power BI calculations.

Have NorthwindTutorial open, with the Northwind connection tested in Connect to SQL Server and prepare Northwind. Keep your setup record nearby. This procedure starts with the new warehouse settings created on the previous page.

Save the Northwind connection

1. Open Sources → Connectors, right-click Connectors and choose Add connector. Enter Northwind in Connector name and choose MSSQL in Connector type. If this tutorial connector already exists, use Edit connector to check it.

2. Enter the tested source settings in Connection string. Choose the example matching your server location and sign-in method. Each uses the prepared OLE DB Driver 19.4.2 and its provider identifier, MSOLEDBSQL19.

   Windows sign-in — SQL Server on this computer

   Replace YOUR_INSTANCE with the named instance you tested. For a default instance, use localhost without an instance suffix.

   Provider=MSOLEDBSQL19;Data Source="localhost\YOUR_INSTANCE";Initial Catalog="Northwind";Integrated Security=SSPI;Use Encryption for Data=Mandatory;Trust Server Certificate=False;

   Windows sign-in — SQL Server on the local network

   Replace SQLHOST\YOUR_INSTANCE with the tested server and instance, or use your recorded tcp:SERVER,PORT. Windows sign-in must be accepted by that server. localhost would connect to this computer instead.

   Provider=MSOLEDBSQL19;Data Source="SQLHOST\YOUR_INSTANCE";Initial Catalog="Northwind";Integrated Security=SSPI;Use Encryption for Data=Mandatory;Trust Server Certificate=False;

   SQL login — local or network SQL Server

   Replace the server and login with your tested values, and enter that account's real password directly in AnalyticsCreator. IloveAnalyticsCreator_ is an illustrative password only; this example does not create or change an account.

   Provider=MSOLEDBSQL19;Data Source="YOUR_TESTED_SERVER";Initial Catalog="Northwind";User ID="YOUR_SQL_LOGIN";Password="IloveAnalyticsCreator_";Use Encryption for Data=Mandatory;Trust Server Certificate=False;

   Keep the straight double quotes around replacement values. If a value contains a double quote, write that character twice inside the outer quotes. Keep the certificate choice that passed the connection test; use Trust Server Certificate=True only for the isolated test-server exception agreed during setup. Do not record passwords in the setup record.

3. Keep Do not store connection string in CFG.SSIS_Configurations clear. Click Test connection. After success, click Save, reopen the connector and test its saved settings again.

   Result: the saved connector reaches Northwind, rather than repo_NorthwindTutorial, dwh_NorthwindTutorial or NorthwindWarehouse.

Choose four tables and their roles

4. Check Sources → Sources and ETL → Imports for an earlier attempt. If the four tutorial imports and their model already exist, continue at Check the imports and model. If the setup is incomplete, use the recovery steps before creating anything else.

5. Right-click the Northwind connector and choose DWH Wizard. Confirm Connector, select Read metadata from connector, enter dbo in Schema filter and Customers,Products,Orders,Order Details in Comma-separated list of table filters, then click Apply.

6. Select the four returned tables by their exact schema and names. Click the down-arrow button and check that only those four tables appear in the lower list. Exist in DWH is status information, not the selection checkbox.

7. Set DWH type to Classic. Set the individual rows as follows; keep Trans and Hist clear throughout.

   Four tables, one sales model

   Source table	Select	Role
   Customers	Import, Dimension	Customer names and attributes. Keep Fact clear.
   Products	Import, Dimension	Product names and attributes. Keep Fact clear.
   Orders	Import	Links each line to its customer and order date. Keep Dimension and Fact clear.
   Order Details	Import, Fact	One row per order line, with its sale price, quantity and discount. Keep Dimension clear.

   An import copies a source table. A dimension describes the customer or product. The fact holds the sales rows that the report measures.

8. Click Next and enter these naming settings. Keep the braces in the templates.

   Names for this tutorial

   Setting	Value
   Tables per package	4
   Import Package names	IMP_{CONNECTOR_NAME}{NR}
   Table names	{SRC_NAME}
   Dimension names	DIM_{SRC_NAME}
   Fact names	FACT_{SRC_NAME}
   Key field name	FK_{TableName}

   For example, DIM_{SRC_NAME} requests DIM_Customers for Customers. Package numbering is assigned by AnalyticsCreator; record the resolved name after the wizard finishes. The grouping setting requests up to four imports together, so check the actual package membership.

9. Click Next. Set Field names appearance to No changes, select Retrieve relations and set Include tables in facts to N:1 direct and indirect related. This includes the order line's links to Orders and Products, and the Orders link to Customers.

   Keep Create Calendar dimension, Create Snapshot dimension and Use friendly names in transformations as column names clear. Set Default transformations to No defaults and leave the selected Stars list empty.

10. Under Schemas for the generated objects, choose IMP for Import tables and Import transformations, and DWH for Facts and dimensions. Use Back and Next to review the four rows, their roles and names, then click Finish. Read any reported error before continuing.

Check the imports and model

11. Open each of the four imports under ETL → Imports. Record its actual source, target and package name. In Main, compare Source name and Target name for each column. The requested targets are IMP.Customers, IMP.Products, IMP.Orders and IMP.[Order Details].

    Preserve all source columns and these keys: Customers.CustomerID, Products.ProductID, Orders.OrderID and both Order Details.OrderID and Order Details.ProductID. An order ID alone does not identify a line. Open the recorded package under ETL → Packages and check that its imports are exactly the four selected tables. If the wizard created more than one package, record each distinct package and its membership.

12. Open ETL → Transformations and inspect the requested DWH.DIM_Customers, DWH.DIM_Products and DWH.[FACT_Order Details]. These are the intended names; record the actual names shown. The two dimensions should retain all source customers and products, including those with no sales.

    Before saving changes, check Transformations Createviews: saving can compile or create a view according to that setting. Confirm that the design database is dwh_NorthwindTutorial.

13. Open the fact's Definition and VIEW. Check that Order Details is its main input and that its joins follow these source relationships.

    Each sales line finds one related row

    From	To	Meaning
    Order Details.OrderID	Orders.OrderID	Many lines belong to one order.
    Orders.CustomerID	Customers.CustomerID	Many orders belong to one customer.
    Order Details.ProductID	Products.ProductID	Many lines refer to one product.

    Keep only the four selected tables in this fact. Extra one-to-many joins can multiply sales rows. Check the output fields for the full order-line key, customer and product links, order date, quantity, line unit price and discount. Use Reference and Statement in the Columns grid to identify each field's origin. The sales calculation uses Order Details.UnitPrice, not the current catalog price in Products.

14. Under DWH → Tables, open the two dimension definitions and the fact definition. Select Export to OLAP for those three analytical tables. This is the interface label used for the model metadata that supplies the Power BI output.

    Check each required column's in OLAP setting: include the dimension keys and customer/product names, the fact's relationship fields and the order-line fields used by the measures. Their analytical names must remain identifiable in the generated model.

    For the fact's customer and product fields, check Reference, Referenced column and OLAP reference. They must point to the corresponding unique key in the generated customer or product dimension. Keep 2-sided clear for this simple model. Record the actual fact-to-dimension column pairs; a column beginning with FK_ is not proof that the relationship is correct.

Add three sales calculations

The tutorial counts order lines and units, then totals sales after discount. It rounds each line's net value to two decimal places before adding the lines. That rule makes the report comparable with the SQL check after loading.

15. In the fact table definition, open Measures. Add Units as Measure name, choose the fact column originating from Order Details.Quantity in Column, and choose SUM in Aggregate. Leave Statement tabular empty and Hidden clear.

16. Add Order Lines as Measure name. Enter this expression in Statement tabular, without a measure-name prefix. Leave Hidden clear.

    COUNTROWS('{TableName}')

    {TableName} is an AnalyticsCreator template, replaced with the generated analytical table name. Keep its braces and surrounding single quotes.

17. Add Net Sales as Measure name. Enter the following in Statement tabular. Keep Hidden clear and set Format string to #,##0.00.

    SUMX(
        '{TableName}',
        ROUND(
            ROUND('{TableName}'[UnitPrice], 4)
            * '{TableName}'[Quantity]
            * (1 - ROUND('{TableName}'[Discount], 4)),
            2
        )
    )

    The three bracketed field names must match the fact's analytical names for the order-line fields checked above. If the generated names differ, replace only those field references with the names you recorded. Check FriendlyName as well as the technical column name. Rounding the source discount to four decimal places avoids carrying its floating-point storage approximation into the cent calculation.

18. Review Parsed measure name and Parsed statement tabular, then click Save. Reopen the fact and confirm the three saved measures and their field references. Record the model table and field names needed by the report.

    Result: four checked imports, two descriptive dimensions and a sales fact with Units, Order Lines and Net Sales. These definitions are ready to generate; their data will be checked after the first load.

If a connection or model check differs

1. For a connection error, compare the saved provider, server, database, account and certificate choice with the successful test. A shared network alone does not establish server access.
2. For a missing source table or relationship, check the dbo schema, exact names and metadata-reading permission. Check the three Northwind source relationships before changing the fact.
3. For a missing field, extra input or wrong join, inspect Definition, Columns and VIEW. Correct the tutorial definition before generation; do not compensate for duplicated sales with a report filter.
4. For a measure error, compare its field names with the fact's analytical names and review Parsed statement tabular. Keep the order-line price and rounding rule unchanged.

Continue after an incomplete wizard setup

1. Record the error and inspect Sources, Imports, Packages and the analytical definitions. If they are complete, use the checks above instead of running the wizard again.
2. If the definitions are incomplete or cannot be matched confidently, keep this attempt intact. Ask AnalyticsCreator support to help repair it, or reserve new unused tutorial database names through database preparation and create separate warehouse settings through Install AnalyticsCreator and start your data warehouse. Decline any offer to delete an existing database.
3. With the separate attempt open, reuse the unchanged Northwind source and repeat the model procedure. Check its generated names and relationships before creating loading files.