Skip to main content

Use SQLite DB in Desktop Agent analyze local data

SQLite is a lightweight and open-source database management system that stores data in a local file, making it a popular choice for local data analysis.

We used SQLite in desktop agent to store and retrieve data for data analysis, that allowing for efficient local data analysis without the need for a separate database server.

Your can download and install Metad Desktop Agent:

  • Go to the Metad website at https://mtda.cloud/en/ and click the "Download Agent" button for your operating system (Windows, Mac).
  • Double-click the downloaded file to begin the installation process.
  • After the installation is complete, launch Metad Desktop Agent from your applications menu or by double-clicking the icon on your desktop.
  • If you already have a Metad account, sign in to your account. If not, create a new account.
  • Once you're signed in, you can start using Metad Desktop Agent to access your data sources, build semantic model, and create story shared with your team.

Create DataSource​

First, go to the Data Sources page in Metad website, then click the New Data Source button to create new data source. Select the type LocalAgent in DB type list, then input the Name of your new data source. Toggle Use local agent, input the Database name for sqlite3 file or let it default. You can click the ping button to check the db avaliability. Then click Crreate to save the new data source.

Build Model​

To create a semantic model involves the following steps:

  1. Goto the models page and click the "New" button to open new model wizard.
  2. Select the data source name created in prev step.
  3. Select a catalog in the data source, in sqlite it has a default.
  4. Input model name then click create to save the model.

Open the new model.

Upload Table​

In the model workspace you can upload your data files (Excel, csv files) to create table in data source db.

For example, we use the data Most Subscribed 1000 Youtube Channels to build a model.

Click the Upload Table button in the tables area, the drag the data file into files area, click next button. You can change table name in the tab title field, and set key and type for columns. Then click next to preview the table list, and click Import to upload. When the progress done you can go back model to build semantic.

Create a Cube​

We imported data into new table, then we can drag the table name into SQL Models area to open the new cube wizard. Select the type Cube, input the cube Name and Caption, used the table name, then apply it to new cube.

The page navigated to the cube builder page, where we could see the dimensions and measures fields in the source fields area. We can drag them into each other to change their role (dimension or measure). Then select all or part of them and click One-key Generation to build cube. Now we have created a basic cube, and can use it to analyze data in the table.

On the calculation page, we can drag dimensions and measures into the rows and columns in the preview area to get a preview of the data.

If you need to set detailed information for the cube, you can click on the dimension or measure node to open the attributes panel and set all its attributes.

Create Story​

To create a story dashboard based on the semantic model, you can click menu General -> Story List -> Create Story in the semantic model workspace, or click New button in the story list page.

In the created story dashboard, add new page then add widgets in the page. You can add a wide variety of widgets to your story dashboard, depending on the specific requirements of your analysis or reporting needs. Here are some of the most common types of components you can add:

  1. Charts and Graphs: These are perhaps the most common components of a dashboard and include bar charts, pie charts, line charts, scatterplots, and many other types of visualizations that help to summarize and analyze data.
  2. Tables: Tables are another important component of a dashboard and provide a way to view detailed data in a structured and organized way.
  3. Maps: Maps can be used to visualize geographic data and to help identify patterns or trends that may be related to location.
  4. Text Boxes and Labels: These components provide a way to add annotations, labels, or narrative elements to your dashboard, helping to provide context and meaning to the data.
  5. Filters and Selections: These components allow users to interact with the data in real-time, by selecting or filtering specific data elements or dimensions of the data.
  6. Images and Media: You can also add images, videos, or other multimedia components to your dashboard to help illustrate or convey specific points or insights.

Using the SQLite database in the Desktop Agent, you can store and analyze your data locally, without having to rely on external servers or cloud services. The advantage of this approach is that it can be faster, more secure, and more reliable, especially if you are working with sensitive or proprietary data.

In addition, the semantic models and story dashboards that you create using the Desktop Agent are typically saved in metad cloud server, which means that they are not lost or deleted unless you intentionally delete them. This provides an additional layer of security and control over your data and analysis artifacts.