Datasets can be used connect to and build queries to extract the information you need from a database or excel file. It is used in various parts of IntelliFront BI where database information is needed.
Library - Datasets
Datasets can be used to connect and build queries to extract the information you need from a database or excel file. It is used in various parts of IntelliFront BI where database information is needed.
How do I setup Datasets in IntelliFront BI?
- Go to Library and select Datasets.
- Or select Create Dataset from the card on the home screen.
- Click New Dataset.
- Import Dataset: This can be used to import a Dataset.
For more information about the import dataset, click here.
- Add dataset name: Give the dataset a unique name.
Dataset Type
Excel File
- Click this option if the dataset has an Excel file.
- Groups: Enter any group that should have access to the dataset. If no group is selected, the dataset will be accessible by all users.
- Excel Filepath: Type in the file path.
- Click Gather the worksheets! to connect.
- Select a worksheet: Select the worksheet from the drop-down list.
- Append to Existing Data: Upon cashing, select this option if new data is to be added to the existing data.
- Overwrite Existing Data: select this option if new data is to overwrite the existing cached data each time it caches.
Database Query
- Database Query: click on this option if the data set is in SQL.
- Groups: Enter any group that should have access to the dataset. If no group is selected, the dataset will be accessible by all users.
- Select An Account: Select an account to connect to.
The Accounts must have been set up from within Library Account first.
- Select a Database Type: Select the database type from the drop-down list. SQL or Oracle.
- Use Query Builder: This will assist you in writing a SQL Query to bring back the required data.
- Select a Schema: Click this option to select a schema to use for your query.
- Select a Table: Use this option to select a table to assist with your query.
- Add Rule: Use this to add a rule for the query by selecting what factors are needed to complete the rule.
- Hide Query Builder: Click this option to write your own SQL Query without the aid of the Query builder.
- Click Validate Query to test it and save the data driver.
- Edit Query: This allows you to make changes to the query.
- Preview Query: Preview the data that the Query brings back.
- Dataset Caching: This option will be checked by default since the imported Dataset was checked in a previous IntelliFront BI. This option allows IntelliFront BI to cache the dataset at regular intervals throughout the day to speed up Dashboard Load times.
- Overwrite Existing Data: select this option if new data is to overwrite the existing cached data each time it caches.
- Append to Existing Data: Upon cashing, select this option if new data is to be added to the existing data.
- Retry Running the Query: Dataset Caching now includes an option to set a maximum number of retries within a specified time frame. This allows you to configure how many times the caching process will attempt to run if there are any issues, and the maximum duration for these retries.
Set up the Dataset Caching Frequency
- Select a start date and time for the caching.
- Start Date: Enter the desired starting date for the schedule. This section can be the current date (providing schedule time has not already passed) or a date in the future.
- Execution time: The gizmo will run at this time.
- Use the following account: Select the windows account to send the report.
In this section, you will decide when the dataset will execute. There are a variety of options:
- Daily: Run a dataset every day or at a frequency of days.
- Sub options: Repeat every X Days.
Example: Run the dataset every 3 days.
- Weekly: Run a dataset on a weekly time frame.
- Sub options: Repeat every X weeks.
Example: Run the dataset every 2 Weeks.
- On: Select the specific days of the week the dataset will run. If only once a week, select only the day of the week it will run.
Example: Run every Monday, Wednesday, and Friday.
- Monthly: Run the dataset on a monthly time frame.
- Frequency: Select frequency options such as the “last Thursday of the month.” Also, you can include or exclude specific months from the dataset.
Add Options Menu
- End Date: If the dataset is due to end after a certain date enter that here. If the dataset is to run indefinitely, then leave it blank.
- Repeat Interval: Rerun the dataset every x minutes from the scheduled time until your specified time.
For example, you can set up a dataset to run every day at 8 am and to run every hour until 5 pm.
- CPU Priority: Set the priority level of the schedule. This will come into play when other datasets are running at the same time. If the priority is set to High then the dataset will take priority over other datasets in the queue.
- If the priority level is set to BelowNormal then it will be at the bottom of the queue and the other datasets will be running first.
- Timeout: Stop if the task is running for more than X minutes or hours.
- Once finished, click Save to save the dataset.
Caching a Dataset
You can now cache a dataset and view it's caching history in Intellifront BI.
Dataset caching history can only be done for query datasets.
- Under a completed and saved query dataset, click Cache Now.
- Select Yes, Do it!
- A history log will now be visible for your dataset.
Exporting Dataset
In order to export a dataset, you must have the dataset created.
You can only import Database Query dataset type in IntelliFront BI.
- Once the Dataset is saved, click the Export button at the bottom of the Dataset window.
- The Dataset will be exported successfully.