{"id":15180,"date":"2026-07-24T12:27:44","date_gmt":"2026-07-24T17:27:44","guid":{"rendered":"https:\/\/www.mrc-productivity.com\/docs\/?post_type=ht_kb&p=15180"},"modified":"2026-07-24T15:17:34","modified_gmt":"2026-07-24T20:17:34","slug":"m-power-data-analytics-with-ai","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/docs\/knowledge-base\/m-power-data-analytics-with-ai","title":{"rendered":"m-Power Data Analytics with AI"},"content":{"rendered":"\n

Data Analytics is designed to allow end-users to request data via a chatbot (such as What are my top 10 items sold this year, Compare profit margin by Customer for this YTD vs Last Year, <\/em>or What departments have the highest callout rate<\/em>, etc…) and for AI to do all of the leg work behind the scenes to compute the answers.<\/p>\n\n\n\n

Why this Approach?<\/h1>\n\n\n\n

End-users continue to get more and more sophisticated. As m-Power developers, this requires more and more time to construct the necessary reports to answer these questions. With enough users asking these complicated questions, it can quickly take up and more development time.<\/p>\n\n\n\n

How does it Work?<\/h1>\n\n\n\n

Developers register tables to their Data Dictionary, just as they always have. Developers then add additional pieces of information to the table’s metadata. Information provided includes table descriptions, field descriptions, how objects should be joined together, and other instructions that AI can use to better understand the tables provided.<\/p>\n\n\n\n

At-run time, via a chatbot, an end-user asks a question. That question, along with the System Prompt<\/a><\/strong>, and Dictionary metadata is sent to AI. AI then determines the best SQL statement to run to access the necessary information. That SQL statement is returned back to your m-Power server for processing. The accompanying result set is sent back to AI for analysis before the final answer is returned to your end-user.<\/p>\n\n\n\n

Data Analytics Configuration<\/h1>\n\n\n\n

Because Data Analytics involves executing AI-generated SQL against your database, a one-time global setup is required to establish a secure, dedicated connection.<\/p>\n\n\n\n

1. AI Datasource<\/h2>\n\n\n\n

Since AI is generating SQL statements that are being sent to m-Power to execute, a new AI specific datasource is needed. <\/p>\n\n\n\n

Go to Admin -> DataSources.<\/p>\n\n\n\n

Find your current datasource connector and click copy. <\/p>\n\n\n\n

On the following screen, append _AI<\/code> to the ID. <\/p>\n\n\n\n

This is a one-time global m-Power change.<\/p>\n\n\n\n

2. Dictionary Configuration<\/h2>\n\n\n\n

Once the new bean has been installed, you must link it within your dictionary. <\/p>\n\n\n\n

This step must be repeated for any dictionary you want to use with Data Analytics.<\/p>\n\n\n\n

Go to Admin -> Dictionary Configuration -> Runtime Application Settings. Click Property Options -> Text Mode<\/strong>.<\/p>\n\n\n\n

\"\"\/\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

Search for this line: <datasource\u00a0pdesc=\"datasource\"<\/code><\/p>\n\n\n\n

Immediately after this, paste this line:<\/p>\n\n\n\n

<datasource_ai\u00a0pdesc=\"datasource_ai\"\u00a0value=\"as400_remote1_ai\"\/><\/code><\/p>\n\n\n\n

Be sure to replace as400_remote1_ai<\/code> with the name of your newly create datasource bean from step 1 above.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

After this please restart Tomcat.<\/p>\n\n\n\n

Managing Data Dictionary Meta-Data<\/h1>\n\n\n\n

Managing the Dictionary Meta-Data can be found via Admin -> Tables -> Manage Tables -> Edit Meta Data<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

The purpose of these screens is that you are providing guidance to the Large Language Model on how your database is organized, how relevant data is connected, and where to find key data. <\/p>\n\n\n\n

Screen Organization<\/h2>\n\n\n\n

The Meta-Data management is comprised of three key areas. <\/p>\n\n\n\n

Table Listing<\/h3>\n\n\n\n

The left panel shows all registered tables in the Dictionary. clicking on any of them will allow you to manage various pieces of data, including the table purpose, field level information, and table joins.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Table\/Fields\/Relationship<\/h3>\n\n\n\n
\"\"\/\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

This section is displayed once you have selected a table from the left panel. Here you can provide key Meta-Data about Table level Info, Field Info, or Join Relationships.<\/p>\n\n\n\n

Table Info<\/h4>\n\n\n\n

It bears repeating that when a user makes a request using the Data Analytics AI helper, their request is sent to AI along with information about each available table. So, with this in mind, it is critical that you provide as much relevant and clear information about your selected table as possible. Don’t assume that AI knows how your database is structured.<\/p>\n\n\n\n

Select Dimension as your Table Type.<\/p>\n\n\n\n

For Grain, explain to AI what level of data is in this table. Is it one row per transaction? Is it one row per customer? Is it an order detail file with many rows from a single order?<\/p>\n\n\n\n

Providing an Approximate Row Count gives AI an idea if it is working with a table with 100 rows or 100 million rows.<\/p>\n\n\n\n

Freshness gives AI an idea of how frequently this data is updated.<\/p>\n\n\n\n

Fields<\/h4>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

Configuring Field level information is arguably one of the most important pieces of information you can provide AI. While column heading information is automatically inherited from the main dictionary, you should click on each field. Doing so will open up a side panel on the right where you can add notes about the field. Again, these notes are shared with AI and provide key insight on what type of information is stored in this field and why it is potentially important.<\/p>\n\n\n\n

The checkboxes found in the main window can be used if you want AI to be permitted to filter on this column or aggregate the data found. Finally, the Hide from AI<\/em> column is helpful if you know this data should never be sent to the LLM for consideration. This is particularly helpful in the event you have many many columns that are of an unknown purpose or aren’t relevant and you want to avoid including them and confusing the LLM. <\/p>\n\n\n\n

Relationships<\/h4>\n\n\n\n
\"\"\/\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

More than likely, all the data you may need to solve a user’s question comes from multiple tables that will need to be linked together. This screen allows you to map these joins for each table\/field combination. Be sure to select the appropriate join type and provide a description of the join that will be sent to the LLM for further context. In the event that you have a join that consists of multiple fields, simply click the “Add Relationship” button as necessary.<\/p>\n\n\n\n

Configuring the AI Studio<\/h3>\n\n\n\n

Once your Meta-Data has been configured, it is time to configure the AI Studio. Click AI Studio in the header.<\/p>\n\n\n\n

This document assumes you have already successfully configured your environment to
communicate with your LLM of choice. If not, please click here<\/a> to learn more.<\/p>\n\n\n\n

On the left side, click “New Assistant”<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Click “Data Analytics”, name your assistant, and press Create.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
\"\"\/\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

Your screen should look very similar to the above. Before you click “Run as Application,” there are a few customizations worthy of your consideration.<\/p>\n\n\n\n

System Prompt<\/h3>\n\n\n\n

System Prompts allow you, the developer, with a place to provide instructions to the LLM. Edit the system prompt to do so.<\/p>\n\n\n\n

\"\"\/\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

Within the Role and Purpose section, add any instructions necessary to the LLM. Example of what to provide might include: “Users might ask for product information. They have no knowledge of product numbers so if you ever include product information be sure to join out to the product master file to get descriptions.”<\/p>\n\n\n\n

LLM<\/h3>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

Your screen might look different from mine. Depending on how many LLMs you’ve setup, different options will be available in this dropdown. Select the LLM you wish to use for this Assistant.<\/p>\n\n\n\n

Metadata<\/h3>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

It is quite unrealistic to think you would want to include every<\/em> table defined in your Data Dictionary to the LLM for consideration. Instead, you can control the specific tables that are exposed to the LLM by selecting them here. You can also select specific data models for consideration in the 2nd tab.<\/p>\n\n\n\n

Configurations and Options<\/h3>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

Security<\/h3>\n\n\n\n

Since AI is generating SQL statements that are being sent to m-Power to execute, it is important to put certain safeguards in place to ensure data security.<\/p>\n\n\n\n

While m-Power has controls in place to make sure that only SELECT<\/code> statements are executed, we highly encourage that customers setup a separate database user that only has READ access to the database.<\/strong><\/p>\n\n\n\n

Once this read-only user has been created:<\/p>\n\n\n\n

    \n
  1. Go to Admin -> Datasources.<\/li>\n\n\n\n
  2. Find the _AI<\/code> datasource that you created during the configuration phase.<\/li>\n\n\n\n
  3. Update the credentials to use the new read-only user and password.<\/li>\n\n\n\n
  4. Save and restart Tomcat.<\/li>\n<\/ol>\n\n\n\n

    <\/p>\n","protected":false},"excerpt":{"rendered":"

    Data Analytics is designed to allow end-users to request data via a chatbot (such as What are my top 10 items sold this year, Compare profit margin by Customer for this YTD vs Last Year, or What departments have the highest callout rate, etc…) and for AI to do all…<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[332],"ht-kb-tag":[330],"class_list":["post-15180","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-end-user-ai","ht_kb_tag-runtime-ai"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/15180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/comments?post=15180"}],"version-history":[{"count":6,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/15180\/revisions"}],"predecessor-version":[{"id":15224,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/15180\/revisions\/15224"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/media?parent=15180"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-category?post=15180"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-tag?post=15180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}