Power BI Integration

For Data Analysts

Connect Power BI Desktop and Power BI Service to Olytix Core's semantic layer using the XMLA endpoint. This enables native Power BI experiences while querying your unified semantic model.

Prerequisites

• Olytix Core server running with DAX API enabled
• Power BI Desktop (latest version recommended)
• API key or service account credentials
• Network access to Olytix Core server from Power BI

Connection Overview

Power BI connects to Olytix Core via the XMLA protocol endpoint:

http://your-olytix-core-server:8000/api/v1/dax/xmla

For production environments, use HTTPS:

https://your-olytix-core-server.com/api/v1/dax/xmla

Power BI Desktop Connection

Step 1: Open Get Data

1. Open Power BI Desktop
2. Click Get Data on the Home ribbon
3. Search for Analysis Services or select Azure > Azure Analysis Services

Step 2: Configure Connection

Enter your Olytix Core XMLA endpoint in the server field:

Field	Value
Server	http://localhost:8000/api/v1/dax/xmla
Database	Olytix Core (or your project name)

Step 3: Authentication

Select the authentication method:

Anonymous (Development)

For local development without authentication:

1. Select Anonymous authentication
2. Click Connect

API Key Authentication

For API key authentication:

1. Select Basic authentication
2. Enter any username (e.g., api)
3. Enter your API key as the password
4. Click Connect

Azure AD / OAuth

For enterprise SSO:

1. Select Microsoft account or Organizational account
2. Sign in with your credentials
3. Olytix Core validates the token and applies user-specific security

Step 4: Select Data

After connecting:

1. The Navigator window displays available cubes
2. Expand a cube to see dimensions and measures
3. Select the tables/cubes you want to import
4. Click Load or Transform Data

Import vs DirectQuery

Import Mode

Data is imported into Power BI's in-memory model:

Pros:

• Fastest query performance
• Full DAX functionality
• Works offline

Cons:

• Requires scheduled refresh
• Data may be stale
• Memory constraints for large datasets

DirectQuery Mode

Queries are sent to Olytix Core in real-time:

Pros:

• Always current data
• No memory constraints
• Centralized governance

Cons:

• Requires network connectivity
• Query latency depends on Olytix Core
• Some DAX features limited

Enabling DirectQuery

1. In Get Data, after entering server details
2. Check Connect live option
3. This establishes a DirectQuery connection

Building Reports

Using Cubes as Tables

Olytix Core cubes appear as tables in Power BI:

1. Drag dimensions to rows/columns
2. Drag measures to values
3. Power BI generates DAX queries automatically

Example: Revenue by Region Chart

1. Add a Clustered Bar Chart visual
2. Drag orders[region] to Axis
3. Drag orders[total_revenue] to Values
4. Power BI generates:

EVALUATE
SUMMARIZECOLUMNS(
    'orders'[region],
    "total_revenue", SUM('orders'[amount])
)

Creating Custom Measures

Add calculated measures in Power BI:

Revenue Growth =
VAR CurrentRevenue = SUM(orders[amount])
VAR PriorRevenue = CALCULATE(
    SUM(orders[amount]),
    PREVIOUSYEAR(orders[order_date])
)
RETURN
DIVIDE(CurrentRevenue - PriorRevenue, PriorRevenue)

Power BI Service

Publishing Reports

1. In Power BI Desktop, click Publish
2. Select your workspace
3. The report and dataset are uploaded

Gateway Configuration

For on-premises Olytix Core servers, configure a data gateway:

1. Install Power BI Data Gateway on a server with network access to Olytix Core
2. Open Power BI Service > Settings > Manage gateways
3. Add a new data source:

Setting	Value
Data Source Type	Analysis Services
Server	http://your-olytix-core-server:8000/api/v1/dax/xmla
Database	Olytix Core
Authentication	Basic or OAuth2

Scheduled Refresh

For Import mode datasets:

1. Go to dataset settings in Power BI Service
2. Expand Scheduled refresh
3. Configure refresh frequency (up to 8x/day for Pro, 48x/day for Premium)
4. Add gateway credentials if required

Troubleshooting

Connection Failed

Error: Cannot connect to server

Solutions:

1. Verify Olytix Core server is running: curl http://localhost:8000/api/v1/health
2. Check network connectivity and firewall rules
3. Verify the XMLA endpoint URL is correct
4. Ensure API key is valid

Authentication Error

Error: Access denied or invalid credentials

Solutions:

1. Verify API key is correct
2. Check API key has DAX API permissions
3. For OAuth, ensure user is authorized in Olytix Core
4. Try regenerating API key

Cube Not Found

Error: The cube 'name' does not exist

Solutions:

1. Verify cube name matches exactly (case-sensitive)
2. Ensure project is compiled: POST /api/v1/unified/compile
3. Check cube is defined in your Olytix Core project
4. List available cubes: GET /api/v1/dax/cubes

Query Timeout

Error: Query execution timeout

Solutions:

1. Simplify the query or reduce data volume
2. Add filters to limit result set
3. Increase server timeout settings
4. Use pre-aggregations for common patterns

Unsupported DAX Function

Error: Function 'XYZ' is not supported

Solutions:

1. Check Olytix Core's supported function list
2. Use alternative supported functions
3. Create a custom measure in Olytix Core instead
4. Request function support via feature request

Best Practices

Performance Optimization

1. Use DirectQuery for large datasets to avoid memory limits
2. Create aggregations in Olytix Core for common query patterns
3. Limit initial data load with query parameters
4. Use incremental refresh for historical data

Security

1. Use service accounts for production connections
2. Enable RLS in Olytix Core rather than Power BI
3. Rotate API keys regularly
4. Use HTTPS for all production connections

Governance

1. Centralize measures in Olytix Core for consistency
2. Document custom Power BI measures that reference Olytix Core
3. Version control your Olytix Core semantic definitions
4. Monitor query patterns to optimize pre-aggregations

Advanced Configuration

Custom Connection String

For advanced scenarios, use a custom connection string:

Data Source=http://your-olytix-core-server:8000/api/v1/dax/xmla;
Initial Catalog=Olytix Core;
Persist Security Info=True;
Integrated Security=False;
User ID=api;
Password=YOUR_API_KEY

Environment-Specific Connections

Use Power BI parameters for environment switching:

1. Create a parameter: ServerUrl
2. Set values for each environment:
   • Development: http://localhost:8000/api/v1/dax/xmla
   • Staging: https://staging.olytix-core.example.com/api/v1/dax/xmla
   • Production: https://olytix-core.example.com/api/v1/dax/xmla
3. Reference parameter in connection settings

Row-Level Security

Olytix Core enforces RLS based on user context:

1. Configure RLS policies in Olytix Core cube definitions
2. Use OAuth authentication to pass user identity
3. Olytix Core automatically filters data based on user roles

Example Olytix Core RLS policy:

# In cube definition
security:
  row_level:
    - name: region_filter
      sql: "region = '{{ user.region }}'"
      roles: ["regional_manager"]

Excel Integration

Connect Excel to Olytix Core via XMLA:

Excel 2016+

1. Go to Data > Get Data > From Database > From Analysis Services
2. Enter Olytix Core XMLA endpoint
3. Select authentication method
4. Choose cubes to connect

Pivot Tables

Create PivotTables connected to Olytix Core:

1. Insert PivotTable from Olytix Core connection
2. Drag dimensions to Rows/Columns
3. Drag measures to Values
4. Excel generates DAX queries automatically

Next Steps

• DAX Query Reference
• DAX API Overview
• Security Configuration