SaganDocs
sagan.vn
VIEN

Databases and Power BI ​

Questions such as "What were Da Nang's sales last month?" are not answered by documents: the figures live in your ERP or in Power BI. A Databases connection lets Genie and your agents read such a database with read-only SQL, limited to the tables and columns you describe, and with rows narrowed to what the person asking may see. A Power BI data source does the same for Power BI semantic models with read-only DAX queries. People see the query and the figures in the answer. Admins and Owners set this up; everyone else only asks questions.

Before you start ​

  • A read-only database account. Ask your database administrator for an account that can only read the tables or views you want to expose. See Use a read-only account.
  • Network access. Genie connects only to database servers it is allowed to reach. If yours is on your company network, a system administrator adds it to the SSRF protection allowlist (the internal addresses Genie may reach) in Settings → System settings → Security & privacy, and your firewall must let the Genie server reach the database port. See Network and antivirus. If you are not sure where Genie runs, ask your Genie provider.
  • Member attributes, if rows should depend on who asks (for example each branch manager sees only their branch). See Filter rows by member.

Add a connection ​

Contoso keeps its ERP in the PostgreSQL database contoso_erp (branches, products, customers, sales orders, order lines, monthly targets). To connect it:

  1. In the sidebar, open Toolbox and choose the Databases tab.
  2. Click Add database at the top of the page.
  3. On the Connection tab, under Basics, fill in Name (for example "Contoso ERP (read-only)") and Description ("Sales orders, customers, products and monthly targets of every branch"). Genie reads the description to decide when to use this database, so say what it holds.
  4. Under Connection, pick the Database type, then fill in Host, Port (leave it empty for the usual port), Database (contoso_erp), Username and Password of the read-only account. Choose an SSL mode; ask your database administrator which one the server supports.
  5. Under Limits, check Max rows per query and Query timeout (seconds).
  6. Click Test connection. A working connection shows Connected with the server version and the round-trip time.
  7. Click Save. You can now add tables on the Tables tab: see Describe tables and columns.
Screenshot coming soonToolbox → Databases: each connection with its type, address, exposed tables and who may query it through Geniewebsite-docs/public/screenshots/en/databases-list.png
Toolbox → Databases: each connection with its type, address, exposed tables and who may query it through Genie

The list shows each connection's type, address, number of exposed tables, who may query it through Genie, and whether it is Enabled. Test checks a saved connection again.

Connection settings ​

SettingWhat it does for users
Name, DescriptionShown in the agent editor and read by Genie to pick the right database for a question
On/off switch (under Basics)When off, nobody can query the database, through Genie or an agent. The settings stay
Database typePostgreSQL, MySQL (8.0 or later), SQL Server, Oracle Database, SAP HANA, Google BigQuery, Amazon Redshift or Apache Spark. Some types show extra fields, for example the Oracle service name, the BigQuery project and key file, or the Spark authentication
Host, Port, DatabaseWhere the database is. Ask your database administrator
Username, PasswordThe read-only account. The password is kept secure and never shown again; when you edit the connection, an empty password keeps the stored one
SSL modeWhether the connection is encrypted and whether the server certificate is checked
Allowed schemasOptional. Limits the tables you can expose (and Import from database) to these schemas
Max rows per query1 to 5,000 rows (200 by default). Larger results are cut, and the answer shows that they were
Query timeout (seconds)1 to 300 seconds (120 by default). Slower queries are cancelled

Use a read-only account ​

Genie never writes to your database, and it checks this itself:

  • It runs exactly one SELECT per query and refuses anything else: changes, schema commands, several statements, comments, and functions that write, wait or read files.
  • It only reads the tables and columns you expose, and applies the row filters of the person asking.
  • Each query stops after Max rows per query rows and Query timeout (seconds). Where the database supports it, the query also runs in a read-only session that is rolled back afterwards.

A read-only account is still the strongest guarantee, because it does not depend on Genie at all. Give it SELECT on the exposed tables or views only, ideally views that already join and filter what the business needs. For Contoso ERP on PostgreSQL, the database administrator runs:

sql
CREATE ROLE genie_ro LOGIN PASSWORD 'choose-a-strong-password';
GRANT CONNECT ON DATABASE contoso_erp TO genie_ro;
GRANT USAGE ON SCHEMA public TO genie_ro;
GRANT SELECT ON branches, products, customers, sales_orders, order_lines, monthly_targets TO genie_ro;
ALTER ROLE genie_ro SET default_transaction_read_only = on;

Cảnh báo

SQL Server and Apache Spark have no read-only session. On them, the account's rights are the only barrier on the database side: grant SELECT on the exposed objects and nothing else (for Spark, for example with an Apache Ranger policy). Never use an administrator account such as sa, SYS or SYSTEM.

Describe tables and columns ​

Genie can only read what you list here, under the names you give. Good descriptions matter more than anything else for correct answers: they tell the model what each column means and how to compute the figures people ask for.

  1. Open the connection (click it in the list) and go to the Tables tab.
  2. Click Import from database, pick the tables and views to expose and confirm with Add selected. Each one is added with all its columns, and descriptions are filled from the comments stored in the database. You can also click Add table to add one by hand.
  3. For each table, check Name for the model (for example sales_orders), Source table or view and Description ("One row per sales order; branch is the branch that took the order").
  4. Under Columns, remove the columns people must not see, such as cost prices or personal data. When the list has columns, only those columns are readable; when it is empty, every column is. Give each column a description with its meaning, unit and example values ("net_amount: net amount in VND after discount").
  5. Tick Dimension on name columns with few values, such as branch names or product categories. Their values help Genie send questions that name them ("sales in Da Nang") to this database.
  6. On the Metrics tab, click Add metric for each figure people ask about, so it is always computed the same way. For example Business term revenue, SQL expression SUM(net_amount), Default condition status = 'completed'.
  7. On the Instructions tab, add business notes the model reads before writing SQL: "The fiscal year starts on 1 January. Amounts are in VND. Cancelled orders have status = 'cancelled'."
  8. Click Save.
Screenshot coming soonEditing Contoso ERP: exposed tables with column descriptions, and the tabs for metrics, instructions, access and previewwebsite-docs/public/screenshots/en/database-edit.png
Editing Contoso ERP: exposed tables with column descriptions, and the tabs for metrics, instructions, access and preview

Check what Genie sees ​

The Preview tab is the admins' query console:

  • What the model sees shows exactly the description of this database that Genie and agents receive.
  • Try a query runs your SQL through the same checks as Genie: read-only, exposed tables only, row filters applied. Choose a person in Run as member to see which rows their row filters allow; leave it empty to run as yourself.

The preview uses the last saved version, so save before you test a change.

Who can query a connection ​

There are two ways a database reaches people, and row filters apply in both.

Through Genie, the default assistant. On the Access & routing tab, Who may query it through Genie decides who Genie offers this database to:

OptionWho can ask Genie about this database
Only agents an administrator connected it toNobody through Genie; only agents set up with it
AdministratorsAdmins and Owners. New connections start with this option
Everyone in the workspaceEvery active member
Specific roles, groups, departments or peopleThe roles (and higher roles), groups, attribute values such as department = Sales, and people you list, plus admins

The default assistant's own settings can narrow this further: in Genie's Default assistant settings (on the Agents page), Databases can be set to all permitted databases, selected ones, or off. See Agents and the default assistant. The Access & routing tab also shows the connection's route card, the short summary Genie matches questions against.

Through an agent. In the agent editor (an agent in Smart Reasoning mode), open Databases and choose the connections under Databases the agent may query. Everyone who may run that agent can then query those databases, each with their own row filters.

  • Only Admins and Owners can add a database to an agent, because doing so opens its tables to everyone who runs the agent. Anyone who may edit the agent can remove one.
  • Agents connected by an administrator keep working whatever you choose under Who may query it through Genie.

Databases are offered only to people signed in to Genie with their own account. People who chat through Microsoft Teams, Zalo or the website widget, and calls made with API keys, never get them: there is no member behind them for row filters to apply to.

Filter rows by member ​

A row filter limits a table to the rows the person asking may see. At Contoso, sales staff should only see their own branch's figures, so Nam Vo, who sells in Da Nang, gets the attribute branch = Da Nang.

  1. Set the attribute: open Settings → Members, find the person and edit their attributes (Key branch, Values Da Nang). Someone who covers several branches gets several values, for example Hanoi, Da Nang. See Members and sign-in.

  2. Open the connection, go to Tables, expand sales_orders and fill in Row filter:

    sql
    branch IN ({{user.attribute.branch}})

  3. Add a filter to every other table that holds branch data, for example monthly_targets with the same condition, and order_lines with a condition that looks up the order:

    sql
    order_id IN (SELECT id FROM sales_orders WHERE branch IN ({{user.attribute.branch}}))

  4. Click Save, then check on Preview → Try a query with Run as member set to Nam Vo.

When Nam asks "What were our sales last month?", Genie only reads Da Nang's orders, and the answer says the rows were restricted for him.

A row filter is an SQL condition on the real table. It can use these placeholders, which Genie fills in for the person asking:

PlaceholderValue
{{user.email}}The person's email address, in lower case
{{user.id}}The person's Genie user ID
{{user.username}}The person's user name
{{user.role}}Their role in this workspace: owner, admin, contributor or viewer
{{user.attribute.<key>}}A member attribute, for example {{user.attribute.branch}}. It can hold several values, so always use it inside IN (...)
  • Missing values block the table. If the person has no value for a placeholder (no branch attribute, for example), the table is blocked for them and Genie tells them to ask an admin. It never runs the query unfiltered. Give head-office staff who need every branch the values of all branches.
  • Tables that share a filtered column are filtered too. A table or view without a filter of its own that holds the same column, for example a sales view with branch, gets the same condition. One Genie can't filter safely is blocked. When you click Save, Genie lists these tables; Add suggested filters writes the filters in for you to check.
  • Mark shared tables "Everyone sees all rows". For example the list of branches, which everyone may read in full.
  • Turn on "Strict row filters" if a view adds several branches up without a branch column: Genie can't see that it holds restricted rows. People can then query only tables with their own row filter or marked "Everyone sees all rows".
  • Values are sent safely. Placeholders are passed as query parameters, never pasted into the SQL, and the filter itself is never shown to the model.
  • Only attributes set on Members count. Attributes that arrive from Microsoft Entra ID at sign-in (shown with an SSO tag on Members) are used for the asker's profile in agents, not for row filters.

Power BI semantic models ​

A Power BI data source in a knowledge base indexes your Power BI reports and semantic models: one document per report (pages, visuals, link) and one per semantic model (tables, columns, measures and how each measure is calculated). People can then ask "Which report shows margin by branch?" and get the link, or "What does Total Revenue include?". The data inside the models is not copied. Optionally, Genie and agents can also run read-only DAX queries on the models to answer with live figures.

What your Microsoft 365 and Power BI administrator prepares ​

  1. An app registration in Microsoft Entra ID with a client secret. The app you use for SharePoint or Exchange data sources can be reused. Put it in a security group.
  2. The Power BI admin portal, under Developer settings: turn on "Service principals can use Fabric APIs" for that security group, and keep "Dataset Execute Queries REST API" on. If you will read tables with the admin scanner, also allow service principals to use the read-only admin APIs with detailed metadata and DAX expressions.
  3. Each Power BI workspace to index: Manage access → add the app (or its security group) as a Member, or as a Contributor to read visuals and table schemas.
  4. No Microsoft Graph permission is needed, except GroupMember.Read.All if you mirror the Power BI workspace permissions.

Add the Power BI data source ​

  1. Open the knowledge base that should hold the Power BI documents (Contoso uses Sales), go to Data Sources and click Add Data Source → Power BI. Enter the app's details and pick the Power BI workspaces as for any data source: see Data sources.
  2. Under What to index, choose how Tables and measures are read: Read with DAX INFO.VIEW queries (the app must be a Contributor), Read with the admin scanner API or Do not read them. Leave Include measure DAX on, and turn on Index visual titles and fields if the app is a Contributor. Include hidden tables, columns and measures stays off unless people should see what report authors hid.
  3. To let Genie and agents query the models, turn on Let agents run read-only DAX queries under DAX queries for agents. Set Rows per query (200 by default, at most 1,000) and, if needed, list the allowed models under Only these semantic models.
  4. Turn on Mirror source permissions if each report and model document should be visible only to the people and Entra ID groups with access to its Power BI workspace. Report sharing and Power BI app audiences are not mirrored.
  5. Save the data source. Then decide who may query: in the data source, Who may query it through Genie has the same options as for databases (and the Power BI setting in Genie's Default assistant settings applies too); in the agent editor, Databases → Power BI semantic models the agent may query (Admins and Owners only).

Cảnh báo

Power BI row-level security does not apply. DAX queries run as the data source's app, so the semantic model's row-level security does not narrow what they return. Allow only semantic models whose data every person who may query them is allowed to see.

People ask about Power BI in the chat like any other question, for example "Revenue by branch for the last quarter from the Sales model". Genie or the agent writes one DAX query, and the answer shows the DAX and the rows, like a database answer. See Ask your data.

Audit ​

Every database query, whether from Genie, an agent or Try a query, is recorded in the workspace audit log as Database queried: who asked, the connection, the SQL, the tables read and filtered, the number of rows and the duration, never the row values. Power BI queries appear as Power BI queried by an agent, and changes to connections and member attributes are recorded too. See Usage, analytics and audit.

Good to know ​

  • Only Admins and Owners see the Toolbox and its Databases tab. Contributors who edit an agent see the connections in the agent editor but cannot add them to the agent.
  • A stored password is reused only for the same database type, host, port and username. After changing any of them, enter the password again.
  • Rows a query returns are sent to the chat model. For sensitive data, use a model your company has approved; see Models and data protection.
  • Row filters rewrite the query; they are not the database's own row-level security. Where isolation must be strict (salaries, personal data), also use the database's row-level security or separate views and accounts per group.
  • Databases and Power BI are only used by agents in Smart Reasoning mode, and not by an agent shared from another workspace.
  • SQL written from a question can be wrong. The answer always shows the query, so people can check it; better descriptions and metrics give better queries.
  • Google BigQuery bills by the data a query reads. Each query is estimated first and refused above Max data scanned per query (GB) (1 GB by default).
  • Deleting a connection takes it away from every agent at once.