What is the semantic layer and why it matters
The app is your translation layer. It turns raw tables into a friendly field list — and it's the foundation everything else in DashboardFox sits on. Module 3 is how you build one.
By the end of this lesson
- You'll know which of the three semantic-layer paths matches your data source
- You'll know what App Builder builds and why every app needs it
Background
An app in DashboardFox is a translation layer. Underneath, you have a database with cryptic table names, foreign keys, and columns called cust_id_fk. On top, your team has a friendly list of folders and fields they can click on to build reports — without writing any SQL or knowing how the tables relate. The app is what bridges the two. It's also called the semantic layer: same thing, two names you'll hear used interchangeably.
This is the foundation Module 3 builds. Once your app is set up, your data shows up the same friendly way regardless of where it actually lives — a SQL Server database, an Excel workbook, a Snowflake warehouse, a REST API. The Composer who's writing reports doesn't need to know the difference.
Vocabulary you'll see across this module
App — the whole semantic layer, top to bottom. Has a name, has permissions, has data inside.
Category (also called report type internally) — a logical grouping of related tables inside one app. An app can have one category or many.
Database View — the technical inside of a category: which tables are pulled in and how they join.
Report Tree — the user-facing inside of a category: the folders and friendly field names Composers actually click on.
App Builder — the role (and the screen) where you configure all of the above.
Composer — the role that builds reports on top of your finished app.
Visually, the app sits between your data and your team. Configure the layer once, and everything above it just works.
Three ways to query data — why we recommend the app
DashboardFox actually offers three paths to get from a SQL database to a report. The semantic layer is one. The other two are Direct SQL (write your query, get a dataset) and Stored Procedures (call an existing SP, get a dataset). All three work. Most of the time, the app is the right choice — but knowing why matters, especially if you're migrating from a tool where you wrote raw SQL for everything.
App (semantic layer) Recommended
Best for: giving non-technical users a self-serve, no-code report builder. Smart prompts, cascading filters, drill-downs, between-date ranges all work naturally.
Cost: upfront setup time. You're investing in a model now to save your users time forever.
Bonus: row-level security is enforced automatically, builders can't bypass it. The metadata is also what makes future AI-assisted report building possible — we're building toward that.
Direct SQL
Best for: migrating an existing query from a legacy tool. Fastest path from "I have a query" to "I have a report."
Cost: the builder needs SQL skill and full table access. Aggregations, formulas, and visualizations require an extra step (a Virtual App wraps the saved query so report-builder features work on top).
Watch out: row-level security at the builder level depends on them remembering to add data-tag filters in their WHERE clauses. Easy to forget.
Stored Procedures
Best for: wrapping an SP your DBA already wrote, especially when business logic lives in the database.
Cost: SP parameters are rigid — date ranges become two separate prompts (Start / End), filter value lists need their own queries to populate, drill-down becomes harder.
Watch out: same Virtual App pattern needed for visualizations and aggregations — column types are guessed from the result set, not declared.
The practical recommendation: if you're brand-new, build the app first — it's the path that scales. If you're migrating from another tool and have working queries, use Direct SQL to land your most-used reports quickly, then plan to recreate them in the semantic layer over time so non-technical users can branch out and so RLS is enforced naturally. Direct SQL and stored procedures are covered in Module 9 when you get there.
Stuck on what an app is or whether you need one? Email team@dashboardfox.com with what you're trying to do — we'll point you at the right path.
Do it
-
Plan before you click
The biggest predictor of a good app is fifteen minutes of planning before you open App Builder. Sketch on paper or in a doc:
- Subject-matter buckets. What are the major topics your team reports on? Tickets, Sales, Finance, HR — each is a candidate for a category. If two topics share an audience and a permission boundary, they can live in one app together.
- Permission boundaries. Who should see what? Everyone in the app sees every category. If finance data shouldn't be visible to support agents, finance is a separate app, not a category.
- Field shortlists. For each category, which 10-30 fields do users actually need? Not "all of them" — almost no app exposes every column. IDs, audit columns, internal flags usually stay hidden.
- Sensitive data. What absolutely can't be exposed? PII, salaries, internal scoring fields. The semantic layer is your access control surface — fields you don't add, users can't see.
This planning carries you through the next three lessons. The build itself is mechanical once the plan is in place.
-
The four-step build
Module 3 walks the build in four moves. Each step is its own lesson:
1Categoriessubject-matter buckets2Database Viewtables & joins3Report Treefolders & fields4Polishformulas & cleanup- Step 1 — Categories. Create the subject-matter buckets inside your app. Each becomes a folder Composers select. Build your first app covers it.
- Step 2 — Database View. Inside each category, pull in the database tables and views you need, and define how they join. Same lesson.
- Step 3 — Report Tree. Build the user-facing layer — folders, friendly field names, sub-folders for grouping. Build your app's report tree covers it.
- Step 4 — Polish. Add formula fields for things like month-name conversions, calculated columns, casts when a database type is wrong. For Excel-imported and API-imported apps, there's also some cleanup work — auto-generated apps need reorganizing, and API-imported apps need data-type fixes since JSON gives no type information.
If your subject matter spans wildly different audiences or permission boundaries, you may need more than one app. The first pitfall below covers when to split.
-
Test as you go — open Composer in a second tab
Once you have any folder with fields in it, open Composer in a separate browser tab and pick fields from your in-progress app. You see exactly what your users will see. This is the fastest way to spot anything that's wrong — wrong join, missing field, awkward folder structure, cryptic name you forgot to rename. Iterate in App Builder, switch tabs, refresh, repeat. Every lesson in this module relies on this loop.
If something doesn't look right and you can't tell why, email team@dashboardfox.com with a screenshot. Real human, same business day.
If you're stuck
Conceptual stumbles new App Builders hit before they get to the first click.
I connected my data source and there are no fields anywhere
Connecting and configuring are deliberately separate. The connection (Module 2) registered an integration with DashboardFox. The app (this module) is what shapes that integration into something Composers can actually use. Until you build the categories, database view, and report tree, there's nothing for them to see — that's normal. Build your first app is the next step.
I'm fuzzy on app vs. category vs. integration — they sound similar
One way to keep them straight: an integration is a connection to a data source (a credential and a server, or an uploaded file). An app is the semantic layer on top of one integration — categories, joins, friendly fields, permissions. A category is one subject-matter section inside an app. An app can have one category or twenty; a single integration can have multiple apps registered against it (covered below).
Should this be one app or two?
The rule of thumb: one app = one permission boundary. Granting someone access to an app grants every category in it. If two subject areas need different audiences (finance data vs. ticket data, for example), they should be separate apps — not separate categories in the same app.
Worked example. You're a SaaS company with usage data. Customers should see only their own data when they log in (row-level security on customer ID). But you also want every customer to see anonymous, aggregated benchmarks across all customers — "average response time across all customers your size." If both lived in one app, applying RLS would scope the benchmarks to just that customer's own data — defeating the point. The answer: two apps against the same integration. App A — Customer Detail with RLS enforced. App B — Benchmarks with no RLS, exposing only pre-aggregated views. Composers build reports in each separately and combine them in dashboards (or via Linked/Joined reports) for the customer-facing view. Two apps, one data source, completely different permission stories.
You can register the same data source multiple times and stand up as many apps as you need. Build your first app shows the mechanics; the strategic choice is yours.
Why not just write the SQL? Direct SQL is faster
Direct SQL is faster — for the first report. The cost shows up later: every Composer who builds reports needs SQL skill, row-level security depends on them remembering to add filter conditions in their WHERE clauses, smart cascading prompts and drill-downs need extra setup, and visualizations require the Virtual App wrapper. The semantic layer pays back the upfront time the second your team has more than one report-builder or anyone who's not technical. Migrating from another tool? Use Direct SQL to land your existing reports quickly, then rebuild in the semantic layer over time — that's the recommended pattern.
None of these match my situation
Email team@dashboardfox.com with what you're trying to model and what's tripping you up. We'll talk it through. Same business day.
