Power BI Integration

For Data Analysts

Power BI is Microsoft's business analytics platform. Olytix Core provides native Power BI integration through its DAX query engine and XMLA protocol support, enabling Power BI to query Olytix Core's semantic layer directly using familiar DAX syntax.

Integration Methods

Method	Features	Best For
DAX/XMLA	Native measures, DirectQuery	Enterprise deployments
REST API	Custom data connector	Flexible integration
ODBC	Standard database access	Import mode

Prerequisites

• Power BI Desktop (latest version) or Power BI Service (Premium/Premium Per User)
• Olytix Core API server with DAX endpoint enabled
• Network access from Power BI to Olytix Core

DAX/XMLA Integration

Olytix Core exposes an XMLA-compatible endpoint that allows Power BI to connect directly and execute DAX queries against the semantic layer.

Enable DAX Endpoint

Configure the DAX endpoint in your Olytix Core deployment:

# olytix-core_project.yml
api:
  dax:
    enabled: true
    endpoint: /xmla
    port: 443

Connection Setup

1. Open Power BI Desktop
2. Click Get Data > Analysis Services
3. Enter the Olytix Core XMLA endpoint:

   https://your-olytix-core-server.com/xmla

4. Select Connect live for DirectQuery mode
5. Enter your credentials (API key or Azure AD)

Authentication Options

API Key Authentication

Server: https://your-olytix-core-server.com/xmla
Authentication: Basic
Username: api
Password: YOUR_API_KEY

Azure AD Authentication

Configure Azure AD integration in Olytix Core:

# olytix-core_project.yml
security:
  authentication:
    azure_ad:
      enabled: true
      tenant_id: ${AZURE_TENANT_ID}
      client_id: ${AZURE_CLIENT_ID}
      client_secret: ${AZURE_CLIENT_SECRET}

Then in Power BI:

Server: https://your-olytix-core-server.com/xmla
Authentication: Organizational account

DAX Query Examples

Olytix Core's DAX engine supports standard DAX queries that map to the semantic layer.

Basic Query

EVALUATE
SUMMARIZECOLUMNS(
    'orders'[region],
    "Total Revenue", [total_revenue],
    "Order Count", [order_count]
)

Filtered Query

EVALUATE
FILTER(
    SUMMARIZECOLUMNS(
        'orders'[region],
        'orders'[product_category],
        "Revenue", [total_revenue]
    ),
    [Revenue] > 10000
)

Time Intelligence

EVALUATE
ADDCOLUMNS(
    VALUES('orders'[order_date].[Month]),
    "Current Revenue", [total_revenue],
    "Prior Year Revenue", CALCULATE(
        [total_revenue],
        SAMEPERIODLASTYEAR('orders'[order_date])
    ),
    "YoY Growth", DIVIDE(
        [total_revenue] - CALCULATE([total_revenue], SAMEPERIODLASTYEAR('orders'[order_date])),
        CALCULATE([total_revenue], SAMEPERIODLASTYEAR('orders'[order_date]))
    )
)
ORDER BY 'orders'[order_date].[Month]

Multiple Tables with Relationships

EVALUATE
SUMMARIZECOLUMNS(
    'customers'[segment],
    'orders'[region],
    "Revenue", [total_revenue],
    "Customer Count", DISTINCTCOUNT('customers'[customer_id])
)

Supported DAX Functions

Olytix Core supports a comprehensive set of DAX functions:

Aggregation Functions

Function	Support	Notes
SUM	Full	Maps to cube measures
COUNT	Full
COUNTROWS	Full
AVERAGE	Full
MIN / MAX	Full
DISTINCTCOUNT	Full

Filter Functions

Function	Support	Notes
FILTER	Full
ALL	Full
ALLEXCEPT	Full
CALCULATE	Full	Core DAX function
CALCULATETABLE	Full
VALUES	Full
DISTINCT	Full

Time Intelligence

Function	Support	Notes
SAMEPERIODLASTYEAR	Full
PREVIOUSMONTH	Full
DATEADD	Full
DATESYTD	Full
DATESMTD	Full
TOTALYTD	Full
TOTALMTD	Full

Table Functions

Function	Support	Notes
SUMMARIZE	Full
SUMMARIZECOLUMNS	Full	Primary query function
ADDCOLUMNS	Full
SELECTCOLUMNS	Full
TOPN	Full

For a complete list, see DAX Function Reference.

Cube Mapping

Olytix Core maps Power BI concepts to the semantic layer:

Power BI Concept	Olytix Core Equivalent
Table	Cube
Column	Dimension
Measure	Measure
Relationship	Join
Hierarchy	Dimension with granularities

Example Mapping

Olytix Core cube definition:

# cubes/orders.yml
cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"

    measures:
      - name: total_revenue
        type: sum
        sql: total_amount
        format: currency

      - name: order_count
        type: count
        description: "Number of orders"

    dimensions:
      - name: order_id
        type: number
        primary_key: true

      - name: region
        type: string

      - name: order_date
        type: time
        granularities: [day, week, month, quarter, year]

Corresponding Power BI model:

Table: orders
├── Columns:
│   ├── order_id (Number)
│   ├── region (Text)
│   └── order_date (DateTime, with hierarchy)
└── Measures:
    ├── total_revenue ($)
    └── order_count

DirectQuery Mode

For real-time data access, use DirectQuery mode:

Benefits

• Always current data
• No import/refresh needed
• Row-level security inherited from Olytix Core

Configuration

# olytix-core_project.yml
api:
  dax:
    direct_query:
      enabled: true
      max_rows: 1000000
      timeout: 300

Performance Considerations

1. Enable Pre-aggregations: Reduce query latency

pre_aggregations:
  - name: powerbi_daily
    measures: [total_revenue, order_count]
    dimensions: [region, product_category]
    time_dimension: order_date
    granularity: day

2. Query Reduction: Enable query folding

api:
  dax:
    query_folding: true

3. Caching: Enable result caching

api:
  dax:
    cache:
      enabled: true
      ttl: 300  # 5 minutes

Import Mode

For smaller datasets, use Import mode with scheduled refresh:

Power Query Connection

// Power Query M code
let
    Source = Json.Document(Web.Contents(
        "https://your-olytix-core-server.com/api/v1/query",
        [
            Headers = [
                #"Authorization" = "Bearer YOUR_API_KEY",
                #"Content-Type" = "application/json"
            ],
            Content = Text.ToBinary("{
                ""metrics"": [""total_revenue"", ""order_count""],
                ""dimensions"": [""region"", ""order_date.month""]
            }")
        ]
    )),
    Data = Source[data],
    Table = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"region", "order_date.month", "total_revenue", "order_count"})
in
    Expanded

Scheduled Refresh

Configure refresh in Power BI Service:

1. Publish report to Power BI Service
2. Go to dataset settings
3. Configure gateway connection (if needed)
4. Set refresh schedule (up to 8x daily, or 48x with Premium)

Security Integration

Row-Level Security

Olytix Core enforces RLS based on Power BI user context:

# Olytix Core security policy
security:
  policies:
    - name: region_access
      type: row_level
      filter: "region IN ({{ user.allowed_regions }})"

The user context is automatically passed from Power BI to Olytix Core:

{
  "user_id": "user@company.com",
  "user_email": "user@company.com",
  "roles": ["Sales", "US-Region"],
  "claims": {
    "allowed_regions": ["US-East", "US-West"]
  }
}

Data Masking

Sensitive columns are automatically masked:

security:
  masking:
    - column: customer_email
      type: partial
      show_first: 3
      show_last: 0

IntelliSense Support

Olytix Core provides IntelliSense metadata to Power BI:

Available Metadata

• Table/Cube names and descriptions
• Column/Dimension names and types
• Measure names and formats
• Relationships between cubes

Custom Tooltips

dimensions:
  - name: region
    type: string
    description: "Sales region (US-East, US-West, EMEA, APAC)"
    meta:
      powerbi_tooltip: "Geographic sales territory"

Troubleshooting

Connection Failed

Error: Unable to connect to the Analysis Services server

Solutions:

1. Verify Olytix Core XMLA endpoint is running
2. Check network connectivity and firewall rules
3. Verify authentication credentials
4. Ensure SSL certificate is valid

Query Timeout

Error: Query execution exceeded the allowed time limit

Solutions:

1. Increase timeout in Olytix Core configuration
2. Enable pre-aggregations for common queries
3. Optimize cube SQL definitions
4. Add appropriate filters to reduce data volume

Unsupported DAX Function

Error: Function 'USERELATIONSHIP' is not supported

Solutions:

1. Check supported functions list
2. Rewrite query using supported alternatives
3. File a feature request for the function

Authentication Error

Error: The credentials provided for the data source are invalid

Solutions:

1. Verify API key or Azure AD configuration
2. Check API key hasn't expired
3. Ensure user has required permissions
4. Test credentials using curl or Postman

Best Practices

1. Use DirectQuery for Large Datasets: Avoid importing large volumes
2. Align Pre-aggregations: Match Olytix Core pre-aggs to common Power BI queries
3. Leverage Native Measures: Use Olytix Core measures instead of DAX calculations
4. Enable Caching: Reduce load on Olytix Core for repeated queries
5. Monitor Performance: Track query duration and optimize slow queries

Next Steps

• DAX Function Reference
• Configure pre-aggregations
• Set up row-level security