Build your first app — categories, tables, and joins
Categories, tables, joins. The technical skeleton of your app — what your users won't see directly, but what makes everything they do see actually work.
By the end of this lesson
- A working semantic layer — categories, tables, and joins
- A foundation Composer users can build reports on
You'll need
- App Builder role on the app
- A connected data source whose tables you understand
- A category plan (which subject-area buckets you'll create)
Background
This is where Module 3 stops being abstract and becomes a click-by-click. By the end of this lesson, you'll have an app with at least one category (a subject-matter bucket), a database view that pulls in the right tables and joins them correctly, and DashboardFox dynamically generating valid SQL behind the scenes for every report a Composer touches. The user-facing layer — folders, friendly field names — is the next lesson.
Three concepts to anchor before you click anything:
- Category (also called report type). A logical grouping inside the app. One app can have many categories — for instance, a Tickets category and a Customer Master category in the same app. They're distinct universes inside one app, with their own tables and their own joins. Composers pick "Select an App and Category" before building any report.
- Database View. The technical inside of one category — which tables (or DB views) are in play, and how they relate. This is what we configure in this lesson.
- Joins. The relationships between tables. Without them, DashboardFox can't combine data across tables in the same report. With them, it dynamically writes the SQL for whatever the Composer asks.
The two types of database objects you can pull in: regular tables and database views (pre-built, named queries your DBA may have set up for performance or simplification). DashboardFox treats them the same way and can join views to tables, views to views, or any mix. If your DBA has built reporting views over a complex schema, use them — they often make the rest of this lesson dramatically simpler.
A common shape: the star schema
Most operational data fits a "star": one central fact table (the events — tickets, orders, transactions) joining outward to dimension tables (the lookups — customer, product, location, channel). It's worth recognizing because it tells you which table to pick first when you build joins.
(fact table)
Tickets is the primary table; everything else hangs off it.
If your data isn't a clean star (a "snowflake" with dimensions joining to other dimensions, or a transactional schema with no obvious fact table), don't force it. The same approach works — you'll just have more "primary" tables in different categories, or you'll lean more on database views to flatten things first.
Stuck on whether to use one category or several, or how your tables should join? Email team@dashboardfox.com with a quick description of your data — we'll talk it through. Real human, same business day.
Do it
-
Open App Builder and select your app
From the left sidebar, click App Builder. The page is a top-to-bottom accordion with four sections — App List, Categories, Database View, Report Tree. Each section gates the next: Categories stays dim until you've picked an app, Database View stays dim until a category is selected, and Report Tree until a database view is in place.
Expand App List. You'll see the apps where you have App Builder permission, with platform (e.g.,
mssql), server, and type. Click Configure on the app you registered in Module 2. The app name now appears in the App List status (e.g., "Academy") and Categories becomes active. If your app isn't here, it's a permissions issue — see Block 5. -
Create your categories
Expand the Categories section. It's empty for a new app. Click Add Category and give it a friendly name — this is what Composers will see when they "Select an App and Category." Examples:
Tickets,Customers,Sales,Finance. Add as many categories as your plan calls for; each is its own universe of tables and joins.Each category row has a Configure button. This is what scopes the rest of App Builder. Click Configure on the category you want to work on, and the Database View and Report Tree sections below now apply to that category. To work on a different category, come back to Categories and click Configure on a different row.
Tip: the action menu (the three dots) on each category has a Clone option. Useful before heavy editing — clone the category, work on the clone, and you have a known-good rollback if something goes sideways.
-
Expand Database View
With a category configured, expand the Database View section. Initially, it's empty — no tables, no joins. The status on the right reads NA until you've added something. Three buttons sit above an empty table list: Add Database Tables, Join Database Tables, and Configure Report Tree (a shortcut that jumps you to the next section once joins are done).
-
Add database tables and views
Click Add Database Tables. A picker opens listing every table and view the integration's database account can
SELECTfrom. If a table you expected isn't here, it's a database-grant issue — see Block 5.Check the boxes next to what you want in this category. For a star-schema database, that's typically your fact table plus all the dimensions you'll need to filter and group by. For most categories, Select All and then uncheck the few tables that belong to a different subject area is the fastest path.
Each row has a table alias field. DashboardFox auto-generates one (e.g.,
service_8066) — leave the default in 99% of cases. The only reason to override is if you want the alias to match aliases used in raw SQL queries you'll reference elsewhere.If your tables live under a non-default schema, set the table owner here (e.g.,
finance). For default schemas (dbo,public), leave it blank.Click Save. The tables now appear in the Database View list, with columns for Table Name, Alias, Owner, Join Clause, and Actions. Click the action menu (three dots) on any row to edit the alias/owner, see fields, or delete the table — useful for sanity-checking which keys you'll use to join.
-
Build joins — primary table FIRST, then secondary
This is the highest-stakes step. Get the click order right and joins fall into place; get it wrong and the join lands on the wrong table.
✓ Right click order1. Check primary table (e.g., service_data) first.
2. Check secondary table (e.g., channel_type) second.
3. Click Join Database Tables.
Join lands on the secondary table — primary stays clean and can keep accumulating joins.✗ Wrong click order1. Check secondary first.
2. Check primary second.
3. Click Join Database Tables.
Join lands on the primary table — and now you can't add any more joins to it. Delete and redo.Mechanics, step by step:
- Check the primary table first (your fact table in a star schema — the one most others relate to).
- Check the secondary table.
- Click Join Database Tables. The join sidebar opens.
- Join Type: leave as Left Join unless you specifically need Inner, Right, or Full Outer. Left is the safe default for reporting — all rows from the primary, matched values from the secondary, NULLs where no match exists.
- Parent Alias: auto-populates with the primary table's alias (e.g.,
service_8066). Leave it. - Field 1: pick the join key on the primary (e.g.,
channel_key). - Operator:
=in nearly all cases. - Alias: auto-populates with the secondary table's alias (e.g.,
channel_5967). Leave it. - Field 2: pick the matching key on the secondary (e.g.,
channel_key). - Save.
The join now shows in the secondary table's Join Clause column on the database view list (e.g.,
service_8066.channel_key = channel_5967.channel_key). Repeat for every dimension.When you need more than one key — compound joins
Some schemas join on more than one column at a time, or need an extra filter applied during the join itself. The join sidebar handles this with two fields below the main join: a second Operator dropdown (set to
andoror) and an Additional Join Filters text input. You write the extra condition there as you would in a SQLWHEREclause.Examples of what goes in Additional Join Filters:
- Compound key:
service_8066.region_id = channel_5967.region_id— joins on a second column in addition to the primarychannel_key = channel_keymatch. - Restricted match:
channel_5967.is_active = 1— only join to active rows of the secondary table. - Literal filter:
channel_5967.region_code = 'US'— restrict to a single region during the join.
Most categories don't need this. When you do, the typical case is two-column compound keys (common in older transactional schemas where the primary key is a composite). Use the Additional Join Filters field, save, and it folds into the dynamic SQL as an
ANDclause on the join.If you make a mistake on any join, delete it (action menu on the join row → Delete) and rebuild. Don't try to fix in place.
-
Verify all tables are connected
Once joins are built, scan the database view. Every table in the category should be in at least one join. Outliers — tables that aren't joined to anything — will cause SQL errors the moment a Composer combines their fields with anything else. The next lesson covers what to do when you genuinely have unjoined tables that belong in the same category (the finance example: balance sheet and income statement, with no valid join between them). For now: if you see an outlier and it shouldn't be one, find its join key and add the join.
If you can't figure out how a table is supposed to join, email team@dashboardfox.com with the schema and we'll trace it. The first few apps are the hardest; after that, the pattern is muscle memory.
-
Repeat for each category
To work on a different category, scroll back up to the Categories section and click Configure on the next one. The Database View section below now applies to that category — different tables, different joins, possibly different schemas. When all categories have a working database view, you're done with the technical layer. The user-facing layer (folders, friendly names) comes in the next lesson.
You have a working connection. Below is what to tighten before real users see it. Skip if you're just testing.
Make it real
Plan categories around access boundaries
Granting an app to a user grants every category in it. That makes "what goes in which app vs. which category" a permission decision, not just an organizational one. Same audience, related subject matter → categories in one app. Different audience, sensitive data → different app. The Settings → Security → Apps page is where you assign access; the apps you create here become the granularity available there.
If you find yourself wanting to grant "this category but not that one in the same app" — that's the signal to split into two apps.
Lean on database views for performance and clarity
Pre-built database views earn their keep three ways. Performance: a view that pre-aggregates hot data runs faster than re-joining raw tables on every report. Flattening: a view that joins six tables into one wide result simplifies your joins in App Builder dramatically. Surface area: a view exposes only the columns it selects, so sensitive columns on the underlying table never reach DashboardFox.
Worth knowing: DashboardFox can join views to other views, or views to tables, in any combination. Some BI tools restrict views to standalone use; we don't. If your DBA has been holding back on building reporting views because they didn't think you could use them, that conversation is worth having.
Use a read-only DB user with scoped grants
The DB account in your integration determines the table picker's contents. A read-only account with SELECT grants only on the tables and views you want apps to touch is your second-line defense — even if an App Builder is curious, they can't pull in tables the integration's account can't see.
This pairs with the semantic layer's role: DashboardFox's app controls what Composers see, the DB account controls what App Builders can pull in. Two layers, both useful.
Test joins in Composer before declaring victory
Right-click the Composer icon and open it in a new tab. Pick your app, pick the category, and start adding fields from multiple tables — one from the fact table, one from each dimension. If you get a preview, your joins are valid. If you get a SQL error, something's wrong with the joins (or there's an outlier table). Fix in App Builder, refresh Composer, repeat.
Don't move on to the report tree until this loop produces clean previews. Bad joins discovered after you've built fifty fields in the report tree mean rework.
Same table playing two roles — alias it twice
Common scenario: you have one contact table that the schema joins to in two different roles — once as the customer contact, once as the partner contact. Or one employee table that's joined as both the assignee and the manager.
The pattern: add the same table to the database view twice, with two distinct aliases. Examples — cust_contact and part_contact, or assignee and manager. You set this in the table-alias field when you add tables (overriding the auto-generated alias). Each alias is independent; they each get their own join.
The joins themselves are routine: service.customer_contact_id = cust_contact.contact_id for the customer side, service.partner_contact_id = part_contact.contact_id for the partner side. DashboardFox treats them as separate tables in the dynamic SQL, exactly as you'd write it by hand.
In the report tree (next lesson), you'll then build separate folders — Customer Contact and Partner Contact — pulling fields from the appropriate alias. Critical: rename the display names to disambiguate. Name appearing in two folders is a Composer trap; Customer Name and Partner Name tells the story without anyone having to guess. The Report Tree lesson covers the disambiguation pattern in more depth.
When you need compound or filtered joins
The default join clause is one equation: field on primary = field on secondary. That covers most of what you'll build. When the schema requires more — a two-column compound key, joining only to active rows, restricting to a region — the join sidebar's Additional Join Filters field is where it goes.
Three patterns that come up:
- Compound key — when the schema's relationship requires matching on two columns:
service_8066.region_id = channel_5967.region_idin Additional Join Filters, on top of the primarychannel_key = channel_keymatch. Older transactional schemas with composite keys hit this often. - Active-only join — restrict the matched rows to a subset:
channel_5967.is_active = 1. The dynamic SQL becomesLEFT JOIN ... ON ... AND channel_5967.is_active = 1, which is different from filtering in the report's WHERE clause — left-joining with an inactive secondary returns the primary row with NULL secondary values rather than dropping the primary entirely. - Tenanted schemas — if your data has a tenant or environment column on every table, you'll often want it inside the join:
secondary.tenant_id = primary.tenant_id. Combined with row-level security in Module 4, this guarantees cross-tenant rows can't accidentally match.
Don't reach for Additional Join Filters when a regular WHERE-clause filter (added at report time, in Composer) would do. Use it when the condition is part of defining the relationship between the two tables, not when it's a per-report user choice.
Multiple integrations to the same data source — when it makes sense
You can register the same database multiple times. Most of the time you don't need to — one integration per database is plenty. The exceptions: permission boundaries (separate audiences need separate apps; easier to manage as separate integrations), different DB accounts (one read-only account scoped to operational data, another scoped to financial data — you want each app's table picker to enforce that scope), and app simplification (a "lite" version of an app for one audience, a "full" version for another, both pointing at the same underlying data).
Clone before risky changes
The clone option on each category is your safety net. Before you reorganize joins, change a primary table, or refactor a category, clone it first. If the change goes badly, delete the new one and you're back where you started. Tiny investment, real peace of mind.
If you're stuck
The classic stumbles, in roughly the order they show up.
My join landed on the wrong table and now I can't add more joins
You clicked the secondary table before the primary. The join attaches to whichever table you selected second — pick secondary first and your primary table now has the join, blocking further joins from being built on it. Fix: delete the bad join (action menu on the join row → Delete), then redo with primary table selected first. After a few tries this becomes automatic.
A table I expected isn't in the picker
The integration's database account doesn't have SELECT on it. Your DBA or whoever set up the account needs to grant access. This is a database-side fix, not a DashboardFox-side fix. Verify by trying to query the table directly with that account; if it fails outside DashboardFox, that confirms the issue.
My finance schema tables joined fine but query as missing
You added them with the table-owner field blank. SQL Server will look for them under dbo by default and not find them. Edit each table in the database view, set Table Owner to your schema name (e.g., finance), save. The dynamic SQL will then qualify the table name correctly.
I have unjoined tables in my category and reports break
Two cases. Case 1: the tables should be joined and you missed it — find the join key (often a foreign key like customer_id), build the join. Case 2: the tables genuinely have no valid relationship (the classic example: a balance sheet and an income statement — different subjects, no shared key). The fix isn't to force a join; it's to handle them carefully in the report tree (separate folders + user education) or to split into separate categories. The next lesson walks through this.
App Builder doesn't appear in my left sidebar
You don't have the App Builder role on any app yet. Go to Settings → Security → Apps, find the app, click Edit, assign yourself App Builder, save. Refresh the page; App Builder appears in the sidebar menu. Roles & permissions covers the model.
Composer test fails with "incorrect syntax" or a SQL error
Almost always a join issue. Open the database view, look for tables that aren't in any join. If everything looks joined, try removing fields one at a time from the Composer report — the field that breaks it points you at the broken join. Worst case, clone the category, delete its joins, and rebuild from scratch — sometimes faster than debugging.
None of these match my situation
Email team@dashboardfox.com with a screenshot of your database view (the table list and the joins) and what's failing. We'll trace it. Same business day.
