Shared Metadata Tables

Shared Metadata Tables

This guide explains how to use shared metadata tables in Azure Synapse Analytics to enable advanced data access scenarios for your Business Central data.

📋 Table of Contents

• Overview
• What are Shared Metadata Tables?
• Advantages
• Setup Requirements
• Configuration Steps
• Using Shared Metadata Tables
• Advanced Topics

Overview

Shared metadata tables provide an additional way to access your consolidated Business Central data in Azure Synapse Analytics, enabling:

• ✅ SQL queries via Serverless SQL pool
• ✅ Spark notebook processing
• ✅ Power BI DirectQuery connections
• ✅ Multi-table joins and complex queries
• ✅ No additional data storage (references existing Parquet files)

Note: This feature is only available for the Azure Data Lake Storage export option.

What are Shared Metadata Tables?

Definition

Azure Synapse Analytics allows different computational engines to share databases and tables:

• Apache Spark pools can create and manage tables
• Serverless SQL pool can query these same tables
• Tables are automatically synchronized between engines
• Data remains on the data lake (no duplication)

How It Works

1. Spark job creates a table from Parquet files
2. Table metadata is registered in lake database
3. Serverless SQL pool automatically discovers the table
4. Both Spark and SQL can query the same table
5. Data stays in data lake (no copying)

📖 Learn more about shared metadata

Advantages

SQL Querying

✅ Standard SQL syntax for familiar querying ✅ Serverless - no database compute costs ✅ Fast - optimized for analytics ✅ Familiar tools - use SQL Server Management Studio, Azure Data Studio, etc.

Power BI Integration

✅ DirectQuery support - real-time data access ✅ Import mode - for smaller datasets ✅ Multi-table models - join tables like a traditional database ✅ Similar to API entities - familiar data modeling patterns

📖 Connect Power BI to Serverless SQL

Spark Analytics

✅ Python, Scala, C#, or SQL notebooks ✅ Big data processing capabilities ✅ Machine learning integration ✅ Advanced transformations

📖 Spark notebooks in Synapse

Cost Efficiency

✅ No database compute - data stays in lake ✅ Serverless SQL - pay per query ✅ Reduced storage - single copy of data ✅ Optimized format - Parquet compression

Setup Requirements

Before enabling shared metadata tables:

Prerequisites

• ✅ Azure Data Lake Storage option configured (setup guide)
• ✅ Azure Synapse workspace with pipelines deployed
• ✅ Parquet format selected for CDM data format
• ✅ Apache Spark pool created in Synapse

Resources Needed

1. Serverless Apache Spark pool

   • Small compute size is sufficient
   • Used only during table creation
   • Auto-pauses when not in use

2. CreateParquetTable notebook

   • Imported into Synapse workspace
   • Located at: synapse/notebook/CreateParquetTable.ipynb

3. Permissions

   • Synapse workspace managed identity has Storage Blob Data Contributor role on data lake

---

Configuration Steps

Step 1: Create Apache Spark Pool

1. Open Azure Portal > Your Synapse workspace
2. Navigate to Apache Spark pools
3. Click + New
4. Configure:
   • Name: e.g., "sparkpool01"
   • Node size: Small (4 vCores, 32 GB)
   • Number of nodes: 3 (minimum)
   • Autoscale: Enabled
   • Automatic pausing: Enabled (15 minutes)
5. Click Review + create > Create

📖 Create a Spark pool

Step 2: Import CreateParquetTable Notebook

1. Open Synapse Studio
2. Navigate to Develop tab
3. Click + > Import
4. Select the file: synapse/notebook/CreateParquetTable.ipynb
5. The notebook appears in your workspace
6. Click Publish all to save

📖 Import a notebook

Step 3: Grant Permissions to Managed Identity

The notebook runs using the Synapse workspace's managed identity, which needs read access to the data lake.

1. Open Azure Portal > Your Storage Account (BC data)
2. Navigate to Access Control (IAM)
3. Click Add > Add role assignment
4. Select role: Storage Blob Data Contributor
5. Click Next
6. Click Select members
7. Search for your Synapse workspace name
8. Select it (this is the managed identity)
9. Click Select > Review + assign

Note: The managed identity has the same name as your Synapse workspace.

Step 4: Configure Business Central

1. Open Export to Azure Data Lake Storage page in BC
2. Ensure CDM data format is set to Parquet
3. This is required - CSV format is not supported for shared metadata tables

Step 5: Run Pipeline with Spark Pool Parameter

When executing the Consolidation_AllEntities pipeline:

1. Open Synapse Studio > Integrate > Consolidation_AllEntities
2. Click Add trigger > Trigger now
3. Provide parameters:
   • containerName: Your container (e.g., "businesscentral")
   • deleteDeltas: true or false
   • sparkpoolName: Your Spark pool name (e.g., "sparkpool01")
4. Click OK

Important: If sparkpoolName is blank, the pipeline runs but no shared metadata tables are created.

---

Using Shared Metadata Tables

Viewing Tables in Synapse Studio

After successful pipeline execution:

1. Open Synapse Studio
2. Navigate to Data tab (database icon)
3. Expand Lake databases
4. Find database named after your container (in lowercase)
5. Expand Tables to see all exported entities

Naming conventions:

• Database name: Container name in lowercase
• Table names: Table names in lowercase
• Non-alphanumeric characters (e.g., -) become underscores (_)

Querying with SQL

Option 1: Synapse Studio SQL Script

1. Right-click a table
2. Select New SQL script > Select TOP 100 rows
3. Script generates automatically
4. Click Run to execute

SELECT TOP 100 *
FROM [businesscentral].[customer]

Option 2: Azure Data Studio / SSMS

1. Open Azure Data Studio or SQL Server Management Studio
2. Connect to Serverless SQL endpoint:
   • Server: <workspace-name>-ondemand.sql.azuresynapse.net
   • Authentication: Azure Active Directory
3. Select database (your container name)
4. Query tables:

-- Query customer data
SELECT 
    No_ AS CustomerNo,
    Name,
    City,
    Country_Region_Code
FROM [businesscentral].[customer]
WHERE Country_Region_Code = 'US'

Multi-Table Joins

-- Join customers with sales orders
SELECT 
    c.No_ AS CustomerNo,
    c.Name AS CustomerName,
    s.No_ AS OrderNo,
    s.Order_Date,
    s.Amount
FROM [businesscentral].[customer] c
INNER JOIN [businesscentral].[sales_header] s
    ON c.No_ = s.Sell_to_Customer_No_
WHERE s.Order_Date >= '2024-01-01'

Connecting Power BI

DirectQuery Mode (Real-Time)

1. Open Power BI Desktop
2. Click Get Data > Azure > Azure Synapse Analytics SQL
3. Configure connection:
   • Server: <workspace-name>-ondemand.sql.azuresynapse.net
   • Database: Your container name (e.g., "businesscentral")
   • Data Connectivity mode: DirectQuery
4. Click OK
5. Select tables to include
6. Build your report

Advantages of DirectQuery:

• Always shows latest data
• No data refresh needed
• Suitable for large datasets
• Queries execute on-demand

Import Mode

1. Follow same steps as DirectQuery
2. Select Import as Data Connectivity mode
3. Data is loaded into Power BI model
4. Schedule refreshes in Power BI Service

Advantages of Import:

• Faster report performance
• Offline access
• More DAX capabilities
• Smaller datasets

Using Spark Notebooks

Query in PySpark

# Read table into Spark DataFrame
df = spark.read.table("businesscentral.customer")

# Show first 10 rows
df.show(10)

# Filter and aggregate
df_filtered = df.filter(df.Country_Region_Code == "US")
df_grouped = df_filtered.groupBy("City").count()
df_grouped.show()

Query in Scala

// Read table
val df = spark.read.table("businesscentral.customer")

// Show schema
df.printSchema()

// Run query
df.filter($"Country_Region_Code" === "US")
  .groupBy("City")
  .count()
  .show()

Query in SQL (Spark)

%%sql
SELECT 
    City,
    COUNT(*) as CustomerCount
FROM businesscentral.customer
WHERE Country_Region_Code = 'US'
GROUP BY City
ORDER BY CustomerCount DESC

---

Advanced Topics

Schema Changes

When BC table schema changes (add/remove fields):

Problem: Shared metadata table schema becomes out of sync

Solution:

1. Drop the table (via Spark notebook or SQL):

   DROP TABLE IF EXISTS businesscentral.customer;

2. Re-run consolidation pipeline with sparkpoolName

3. Table is recreated with new schema

📖 Spark DROP TABLE syntax

Existing Deployments

If you already have data exported before enabling shared metadata tables:

Problem: Notebook only runs for new tables, not existing ones

Solutions:

Option 1: Trigger notebook manually

1. Open CreateParquetTable notebook in Synapse Studio
2. Set parameters:
   • container_name: Your container
   • entity_name: Table name to create
3. Attach Spark pool
4. Configure session: Run as managed identity = Enabled
5. Run the notebook
6. Repeat for each existing table

Option 2: Modify pipeline logic

1. Edit Consolidation_OneEntity pipeline
2. Find SparkTableConditions activity
3. Modify condition to trigger notebook for existing tables
4. Republish pipeline

Performance Optimization

Partition Tables (Advanced)

For very large tables, consider partitioning:

# Create partitioned table
df = spark.read.parquet("abfss://container@storage.dfs.core.windows.net/data/Customer/*.parquet")
df.write.partitionBy("Country_Region_Code").saveAsTable("businesscentral.customer")

Statistics

Collect statistics for query optimization:

-- Analyze table
ANALYZE TABLE businesscentral.customer COMPUTE STATISTICS;

Security

Row-Level Security

Implement in views for fine-grained access:

CREATE VIEW businesscentral.customer_secure AS
SELECT *
FROM businesscentral.customer
WHERE Country_Region_Code IN (
    SELECT CountryCode FROM user_permissions WHERE UserID = USER_NAME()
)

Column-Level Security

Create views with limited columns:

CREATE VIEW businesscentral.customer_public AS
SELECT 
    No_,
    Name,
    City,
    Country_Region_Code
FROM businesscentral.customer
-- Exclude sensitive fields

---

Troubleshooting

Table Not Created

Problem: Pipeline succeeds but no table in lake database

Checks:

• ✅ Verify sparkpoolName parameter was provided
• ✅ Ensure CDM format is Parquet (not CSV)
• ✅ Check Spark pool is running
• ✅ Review pipeline activity logs for notebook execution
• ✅ Verify managed identity has data lake permissions

Permission Errors

Problem: "Access denied" when notebook runs

Solutions:

• ✅ Grant Storage Blob Data Contributor to workspace managed identity
• ✅ Verify role assignment on correct storage account
• ✅ Check managed identity is enabled on Synapse workspace

Schema Mismatch

Problem: Table columns don't match current BC table

Solution:

• Drop and recreate table (see Schema Changes above)

Query Performance Issues

Problem: Slow SQL queries

Solutions:

• ✅ Ensure Parquet format is used (better than CSV)
• ✅ Add WHERE clauses to filter data
• ✅ Use appropriate indexes in views
• ✅ Consider partitioning large tables
• ✅ Collect statistics

---

Best Practices

✅ Always use Parquet format for shared metadata tables ✅ Grant minimum permissions to managed identity ✅ Drop and recreate tables when schema changes ✅ Use views for security and simplified access ✅ Monitor Spark pool costs - enable auto-pause ✅ Test queries in Synapse Studio before using in Power BI ✅ Document table naming conventions for your team

---

Summary

Shared metadata tables provide:

• ✅ SQL access to BC data without database compute
• ✅ Power BI DirectQuery for real-time reporting
• ✅ Spark analytics for advanced scenarios
• ✅ Single source of truth (no data duplication)
• ✅ Cost-efficient data access

Next Steps:

1. Create Spark pool if not already done
2. Import CreateParquetTable notebook
3. Grant permissions to managed identity
4. Run pipeline with sparkpoolName parameter
5. Connect Power BI or query with SQL
6. Build reports and analytics

---

Related: Azure Data Lake Setup | Execution Guide | FAQs | Back to Home