Skip to content
  • There are no suggestions because the search field is empty.

How do I link Power BI to an Acctivate database

Connecting Power BI to an Acctivate database involves linking Power BI to the underlying SQL Server database that Acctivate uses, as Acctivate is built on Microsoft SQL Server. Below is a step-by-step guide to establish this connection. Note that Acctivate’s database structure and access details may require coordination with your IT team or Acctivate support to ensure proper permissions and configurations.

 
Prerequisites
  1. Power BI Desktop: Install the latest version of Power BI Desktop (64-bit recommended) on your computer. Download it from Microsoft's official site.
  2. SQL Server Details: Obtain the following from your IT team or Acctivate administrator:
    • Server Name: The name or IP address of the SQL Server hosting the Acctivate database.
    • Database Name: The specific database name for Acctivate (e.g., Servername\ACCTIVATE).
    • Credentials: A SQL Server login with read permissions (e.g., db_datareader) to the Acctivate database. Avoid using an existing user; create a dedicated account like Acctivate_BI_User for security.
  3. SQL Server Management Studio (SSMS) (Optional): Useful for verifying database details and testing queries. Download from Microsoft.
  4. On-Premises Data Gateway (if using Power BI Service): Required for refreshing data in the Power BI Service if the Acctivate database is hosted on-premises.
  5. Acctivate Docs: Common database views can be reviewed here.  For any specific database schema details or restrictions, contact Acctivate Support as Acctivate’s database may have custom tables or stored procedures.
Step-by-Step Guide
Step 1: Launch Power BI Desktop
  1. Open Power BI Desktop.
  2. Click Get Data from the Home ribbon, then select SQL Server from the list of data sources.
Step 2: Enter SQL Server Connection Details
  1. In the SQL Server database dialog box, enter:
    • Server: The SQL server name or IP address (e.g., SERVERNAME\ACCTIVATE or 192.168.1.100).
    • Database (optional): The Acctivate database name (e.g., Acctivate$Demo). If you don’t know it, you can select it later in the navigator.
  2. Choose a Data Connectivity Mode:
    • Import: Copies data into Power BI, suitable for smaller datasets or periodic refreshes. Faster for report interactions but requires manual or scheduled refreshes.
    • DirectQuery: Queries the database in real-time, ideal for large datasets or when data freshness is critical. May be slower for complex queries due to live database calls.
  3. (Optional) Under Advanced options, you can:
    • Enter a specific SQL query to retrieve data (e.g., SELECT * FROM Orders) if you know the Acctivate table structure.
    • Adjust settings like command timeout for long-running queries.
  4. Click OK.
Step 3: Authenticate the Connection
  1. Select an Authentication Method:
    • Windows: Use Windows credentials if your account has database access (common in production environments).
    • Database: Use SQL Server authentication with a username and password (e.g., Acctivate_BI_User and its password). This is typical for test environments or dedicated BI users.
  2. If prompted for encryption, select OK to proceed with an unencrypted connection, or configure encrypted connections if required by your organization.
  3. Click Connect. If authentication fails, verify credentials with your database administrator or check firewall settings.
Step 4: Select and Load Data
  1. In the Navigator window, browse the Acctivate database’s tables, views, or stored procedures. Acctivate’s schema may include tables like SalesOrders, Customers, or Inventory, but names can vary based on customization.
  2. Preview the data by clicking on tables. Select the tables or views you need for your reports.
  3. (Optional) Click Transform Data to clean or shape the data in Power Query Editor (e.g., filter rows, rename columns, or join tables).
  4. Click Load to import the data into Power BI or proceed with DirectQuery.
Step 5: Configure On-Premises Data Gateway (for Power BI Service)
If you plan to publish your report to the Power BI Service and refresh data from an on-premises Acctivate database:
  1. Install the Gateway:
    • Download and install the On-Premises Data Gateway on a server or computer with access to the SQL Server (not a personal workstation for production use).
    • Sign in with your Power BI account and configure the gateway with a name and recovery key.
  2. Add Data Source to Gateway:
    • In the Power BI Service, go to Settings > Manage Gateways.
    • Add a new data source, selecting SQL Server as the connection type.
    • Enter the same Server and Database names used in Power BI Desktop, and configure authentication (Windows or Basic).
  3. Link Dataset to Gateway:
    • After publishing your Power BI report (File > Publish), go to the dataset settings in the Power BI Service.
    • Under Gateway Connection, map the dataset to the gateway data source. Ensure server and database names match exactly.
  4. Set Refresh Schedule:
    • Configure a refresh schedule to keep your data up-to-date (e.g., daily or hourly).
    • Test the refresh and review the refresh history for errors.
Step 6: Build Visualizations
  1. In Power BI Desktop, use the loaded data to create reports and visualizations (e.g., sales trends, inventory levels).
  2. Save the .pbix file and publish it to your Power BI Service workspace for sharing with others.
Step 7: Verify and Troubleshoot
  1. Test the Connection: Use SSMS to confirm the server and database names and ensure the credentials have read access.
  2. Check Permissions: The SQL user must have db_datareader permissions on the Acctivate database. If you need to execute stored procedures, additional permissions like db_executor may be required.
  3. Firewall Issues: Ensure the SQL Server port (default: 1433) is open for Power BI Desktop and the gateway.
  4. Performance: For large datasets, optimize queries in Power BI or use DirectQuery to avoid loading all data. Consider creating SQL views in the Acctivate database for simplified access.
  5. Contact Acctivate Support: If you’re unsure about the database schema or specific configurations, Acctivate’s support team can provide guidance on safe data access.
Additional Notes
  • Acctivate Database Schema: Acctivate’s database may include custom tables or fields based on your organization’s setup. Work with your Acctivate administrator to identify relevant tables or create views for reporting.
  • Security: Use a dedicated SQL user for Power BI to limit access and avoid using admin credentials. Configure privacy levels in Power BI to comply with data governance policies.
  • DirectQuery vs. Import: Choose DirectQuery for real-time data needs (e.g., live inventory tracking), but be aware it may impact performance with complex Acctivate queries. Import mode is better for static reports.
  • Alternative Connection: If direct SQL Server access is restricted, you can export Acctivate data to Excel or CSV and import it into Power BI, but this is less dynamic and requires manual updates.
Example SQL Query (Optional)
If you know the Acctivate table structure, you can use a custom SQL query in Power BI’s Advanced options. For example:
sql
 
SELECT CustomerName, OrderDate, TotalAmount
FROM SalesOrders
WHERE OrderDate >= '2024-01-01'
This retrieves specific columns from a hypothetical SalesOrders table. Consult Acctivate’s documentation for actual table names.