A01_Getting to know Fabric Link
863 Views
February 26, 25
スライド概要
Fabric CATのScott Sewell氏によるFabric Linkの始め方
関連スライド
各ページのテキスト
GLOBAL POWER PLATFORM BOOTCAMP 2025 Japan Intro to Microsoft Fabric: Drive insights & action from D365 and Power Platform Scott Sewell Principal Program Manager Microsoft Fabric CAT #GlobalPowerPlatformBootcamp #GPPB2025 #GPPB2025JP
The need for businesses to link people, data, and processes is greater than ever before 71% 77% 86% Businesses are not able to respond quickly enough to changing market conditions, with 71% reporting low agility1 Percentage of businesses that reported experiencing a skills shortage in 20232 See scalability and the ability to grow and adapt with the business, as key to success1 1Business Agility Institute 2Forbes 3 Microsoft Fabric
The starting line A complex, organically evolved data estate Several Business team Business team Business team Tech team Tech team Vendor 4: Compute and serving Vendor 4: Compute and serving Vendor 3: Data management Vendor 3: Data management Vendor 2: Storage Vendor 2: Storage Vendor 1: Ingestion Vendor 1: Ingestion Enterprise data sources 4 Microsoft Fabric 1 Data copies and infrastructure inefficiencies 2 Limited interoperability between vendor services 3 Data exposure risks
Customers enhancing their data estate face immense complexity 5 Microsoft Fabric
Value Application config BizApps: • Web Servers • Applications • SQL Servers • Authentication • Exchange Infrastructure 6 Microsoft Fabric Business Intelligence Config Data & AI : • ETL Tooling • Data Storage • Analytics Engine • Datawarehouse • Reporting Infrastructure
Value Business Intelligence Config Application config BizApps: • Web Servers • Applications 7 Microsoft Fabric • SQL Servers • Authenticatio n • Exchange SaaS Data & AI : • ETL Tooling • Data Storage • Analytics Engine • Datawarehouse • Reporting Infrastructure
Value Application config BizApps: • Web Servers • Applications 8 Microsoft Fabric • SQL Servers • Authenticatio n • Exchange SaaS Business Intelligence Config Data & AI : • ETL Tooling • Data Storage • Analytics Engine • Datawarehouse • Reporting SaaS
Microsoft Fabric Data Factory Data Engineering Data Warehouse Data Science Copilot in Fabric OneLake Microsoft Purview 9 Microsoft Fabric Real-Time Intelligence Power BI Partner & Industry workloads
Dataverse-Fabric Modules:
Dataverse to Fabric Options:
Three Options for Dataverse-Fabric Power Apps Dynamics 365 Sales Dataverse + Microsoft OneLake “Fabric Link” Dynamics 365 Customer Service Dynamics 365 Field Service TDS Endpoint (SQL) Dynamics 365 Marketing Dynamics 365 Finance & Supply chain Azure Storage + Azure Synapse + Spark “Synapse Link”
TDS Endpoint Fabric Dataverse App Layer Database Workspace
Azure Synapse Link – Delta Lake Fabric Dataverse Workspace Database App Layer Lakehouses CSV SparkSynapse Delta Parquet
Fabric Link Fabric Dataverse Workspace Database Delta Parquet App Layer Lakehouses • • • • • Compressed to 15%-33% of database Includes Long Term Retention Hourly incremental refresh Compute is included in ‘storage’ No Azure resources consumed
Costs associated with Fabric Link vs Synapse Link Dataverse Fabric Workspace Workspace Database Lakehouse Delta Parquet Billed as Dataverse “database” Storage (includes compute) Fabric Dataverse Lakehouse Database CSV Spark Synapse Delta Parquet Compute and Storage billed by Azure + costs of customer’s own management of Azure.
+ Dataverse - Fabric Link Rich Business Data for AI transformation Dataverse→Lakehouse Dataverse Shortcuts Import Semantic Models Raw Curated Power BI Reports
+ Dataverse - Fabric Link Rich Business Data for AI transformation Dataverse→Lakehouse Dataverse Shortcuts Views Import Semantic Models Raw Enriched Curated Power BI Reports
+ Dataverse - Fabric Link Rich Business Data for AI transformation Dataverse→DataWarehouse Fabric Data Agents Copy Job Dataverse Fast Fabric Short-cuts Raw Views Enriched Curated Tables Curated DirectLake Semantic Models Power BI Reports
+ Dataverse - Fabric Link Rich Business Data for AI transformation Engineering Workspace Dataverse Consumption Workspace(s) Fabric Data Agents Copy Job Fast Fabric Short-cuts Views Curated Tables DirectLake Semantic Models Raw Enriched Curated Power BI Reports
+ Dataverse - Fabric Link Rich Business Data for AI transformation Consumption Workspace(s) Fabric Data Agents Curated Tables DirectLake Engineering Workspace Semantic Models Copy Job Power BI Reports Consumption Workspace(s) Dataverse Fast Fabric Short-cuts Fabric Data Agents Views Curated Tables DirectLake Semantic Models Raw Enriched Curated Power BI Reports
+ Dataverse - Fabric Link Rich Business Data for AI transformation Consumption Workspace(s) Fabric Data Agents Curated Tables Engineering Workspace DirectLake Semantic Models Power BI Reports Consumption Workspace(s) Dataverse Copy Job Fast Fabric Short-cuts Fabric Data Agents Views Curated Tables DirectLake Semantic Models Raw Enriched Curated Power BI Reports
DataverseFabricLink Configuration
Creating the Microsoft OneLake / “FabricLink” Pre-Requisites oDataverse o You must have the System Administrator security role in the Dataverse environment. oFabric Capacity oYou must be an administrator of the Power BI workspace. o Currently, the system supports these premium capacity SKUs: "P1", "P2", "P3", "P4", "P5", "F2", "F4", "F8", "F16", "F32", "F64", "F128", "F256", "F512", "F512", "F1024", "F2048", "DCT1", "FT1."
Setup Fabric Link – Create the Workspace • Create a Fabric Workspace to hold the FabricLink Lakehouse. • Name it / add an icon • Ensure it’s set to ‘Trial’ or ‘Fabric’ • Pin your Workspace to your favorites
Pin the Azure Synapse Link in Maker App • Https://Make.PowerApps.Com • Add the Azure Synapse Link panel to your list of tools in the Maker Portal • You can use the “New Link” to determine your Dataverse’s Region / Geo • New link starts the “Azure Synapse Link” Wizard • This page will be where we manage Fabric Link
Launch the Wizard • Open the maker portal – ensure that you’re on the right Dataverse org. • From “Tables” go to the “Analyze” menu and choose “Link to Microsoft Fabric”
Setup Fabric Link – Connection Settings • Organizational Account • Sign in with your Entra ID
Setup Fabric Link – Choose Fabric Workspace • Select from drop-down
Setup Fabric Link – Review and create • This can take time to complete – be patient. • It will eventually open to the Fabric Workspace – but the shortcuts won’t be finished being created – give it time. • This first synch will take time – maybe even several hours if you have lots of data – Be patient. Let it ‘cook’
Setup Fabric Link – Back to the Maker Portal • On Azure Synapse Link, you’ll now see the link to “Microsoft OneLake” – This is your gateway to your FabricLink environment. • Click on Tables to view the list of tables and their status. – Again, this does take time if you’re first setting it up. • The toolbar here gives you • Refresh (this page) • Manage Tables • Refresh Fabric tables (update metadata in Fabric) • Unlink • View in Fabric
Setup Fabric Link – Refresh Fabric Tables • Once the Sync status is Active for tables, Click Refresh Fabric tables • This might take waiting on another refresh cycle before the metadata is fully ready for Fabric.
Setup Fabric Link – Refresh Lakehouse in Fabric • Unidentified = metadata hasn’t been provided or refreshed yet. • Click “Refresh” in the toolbar to have the lakehouse re-read the deltalake • Now you should start seeing data
Explore the data https://github.com /MScottSewell /Dynamics365-Fabric-in-a-Day
Explore the data in the Fabric SQL endpoint You can use the query editor inside the Fabric SQL analytics endpoint Things to note: • Field and table names are C a S e S e N S i T i Ve • Filter on (IsDelete IS NULL OR IsDelete =0) to eliminate deleted records • If you have LongTermRetention turned on you, can filter out archived rows with: (msft_datastate = 0 OR msft_datastate IS NULL)
Explore the data in the Fabric SQL endpoint • Click on the Blue Gear to open the settings • The Lakehouse “Name” ≈ SQL database • The Workspace’s SQL Connection string ≈ SQL Server
Explore the data in Visual Studio Code as SQL • Prerequisite: Visual Studio Code with the SQL Server (MSSQL) extension installed. • Create a new connection • Add the Server Name (SQL connection) • Add the Database Name (Lakehouse name) • Set the Authentication as Microsoft Entra Id and select your credentials. • Give it a friendly name for your convenience.
Explore the data in Visual Studio Code as SQL • Click on the connection and choose “New Query” • Write a quick query for a table to see the results SELECT Base.name , Base.address1_city , Base.address1_stateorprovince , Base.address1_country , Base.revenue FROM account AS Base WHERE Base.IsDelete IS NULL • Be sure to filter out the deleted rows (which are mostly blank) with the “IsDelete IS NULL OR IsDelete =0” clause • Copilot IntelliSense is, at times, so good it’s creepy
Bonus Topic – Logical field names for SQL Queries • Use the Power Apps Maker Portal. • Edit a field to see the logical name associated with a field.
Bonus, Bonus Topic – Logical field names for SQL • Use the “Level up for Dynamics 365/Power Apps” Browser Plugin • Click on the Rocket Icon in the browser • Choose to view Logical Field Names
Dataverse Security Considerations
Complex security configurations in Dataverse are like beautiful snowflakes… …as long as you’re not the one holding the shovel.
Three types of security in a Dataverse-Power BI report • Direct Query through TDS • Data retrieved from Dataverse at run-time using the user’s credentials • Row-Level in Semantic Layer • Visibility is controlled through attributes and rules in the report (RLS) • Test against a territory/owner/division etc. associated with the viewer. • Report Level • If the user can see the report, they can see any data in it. • Reports can be restricted to individual users or groups • Reports might only show data at aggregate levels Beware of any open-ended, unqualified “Just duplicate Dataverse security” Requirement
Row-Level Security Demo • Filters engaged by comparing the logged on user’s UserPrincipleName() to a value (domainname) in a table, then applying a rule with that value.
Security in Dataverse-Power BI Reports Report-Level Row-Level • Lowest Effort • Fastest Speed • Massive Datasets • “Executives” • Challenging • Complexity affects speed • Partitioned Results • “Teams” TDS/SQL Direct Query • Slowest • 1:1 match with Dataverse • Individualized Data • “Front-Line” employees • TDS/SQL connection only
Building Your Power BI Report on DataverseFabric Data
Demo Sales Report
Step 1 – Gather Requirements Focus, Focus, Focus Start small and iterate Try to anticipate re-usability
Step 2 – Create the views Build views to return only the data you want, formatted and named for end-user convenience • Limit the number of fields to only required ones • Any field presented to the user, alias it with the friendly name • Add DateTime conversion to a Date-Only in your Time zone • Ensure you’re using Currency _base fields • Join the stringmap to get choice values • Filters: • Exclude Deleted • Exclude Inactive Records as appropriate • Exclude Measures with no Facts • Date Boundaries for report scope • Include/Exclude LTR archived records
Create the Report from Power BI Desktop • OneLake data Hub • Select your lakehouse • Connect to SQL endpoint ---Supply Credentials and wait--- • Choose the views • Click Transform Data • Select Import or DirectQuery
Suggested – Create Parameter for Lakehouse and Database • Add 2 new parameters • FabricSQLConnection • FabricLakehouseName • Open the SQL Endpoint • Click on the Gear Icon • Update the 2nd line of each query to use the parameters instead of hardcoding the values.
Alternate Approach – Embed the Query directly in the Report • You can create a Semantic layer with the query directly in it. • Wrap the SQL query in a slightly different PowerQuery step. • The [EnableFolding=true] hint will try to wrap any subsequent PowerQuery steps into the main query and retrieve it all at one time. • Upside, it’s self-contained and doesn’t require anything on the server / downside
Add a Calendar -Download Bravo from https://bravo.bi • Disable Auto date/time in Options for report (and as a default) • Allow Bravo to create a date table • Create the Relationships between the Dates in the Fact Table and the Date Table • Connect ‘Created On’ first, since that should always have a value • The other two relationships will be inactive.
Add basic measures • Opportunity Count = countrows(opportunities) • Revenue Open • Sum estimatedvalue_base • Filter on statecode = 0 • Revenue Won • Sum • Filter on statecode = 1 • Use date relationship of estimatedclosedate
Cleanup / Copilot Pre-Flight • Hide Non-user-facing Fields • Validate Table and Field Names • Set field formatting options • Date format • Currency and numeric fields • Default summarization • Categorize Geo & Hierarchies • Add Synonyms • Add Descriptions • Row Labels and Key Columns • Use a Measures Table Sales Measures = {BLANK()} • Group measures into Folders
Publish and add to a Model Driven App • Publish report to a workspace • Set credentials and refresh • In model driven app solution, create “New” “Power BI Embedded” dashboard • Add to the report to the app • Publish and enjoy!
Bonus Add Report to Form and filter • Two Options • Use the “PowerBI Embedder” plugin for XrmToolBox • Add to a solution and use the native PCF Control • Strengths and weakness with each approach
DirectLake on D365 • Data is selectively pulled live from the Deltalake • Amazing use cases if your data can be used little or no modification • Using a view will cause it to fall back to DirectQuery. • You’ll need to deal with choice labels in your source.
Pipelines Data Factory in Fabric • You will need a SPN • Creating Service Principals Really Easily Using Pac CLI Carl de Souza • A Visual Guide To Power Platform Service Principal Setup (MatthewDevaney.com) • Create a query as your source • Map to a destination entity • Customer and other polymorphic Lookup fields are special – use @EntityReference to pass the record type.
Additional Resources & Special Topics
Resources For Customers & Insiders Yammer Forum hosted by the Dataverse PG: https://aka.ms/SynapseLinkforDynamics https://aka.ms/SynapseLinkforDynamics Product Documentation introduction: Link Dataverse to Microsoft Fabric | Microsoft Learn Link Dataverse to Microsoft Fabric | Microsoft Learn YouTube playlist of setup / configuration videos: https://aka.ms/FabricForDataverse https://aka.ms/FabricForDataverse Dataverse-Fabric resources (including this deck): https://github.com/mscottsewell/Dynamics365-Fabric-in-a-Day https://github.com/mscottsewell/Dynamics365-Fabric-in-a-Day Other Resources for F&O Migration to Fabric/Synapse Link: https://aka.ms/TransitionToSynapseLink https://aka.ms/TransitionToSynapseLink
Handling DateTime and Currency fields in data from Dataverse These seems like obscure topics, but almost everyone gets bitten by them the first time. Dates in Dataverse are, for the most part, stored as DateTime in UTC format but displayed to the user based on their current application settings. • Convert Date-Times to a timezone standardized Date-Only field • Filter/join/group on the Date-Only value • If you have significant timezone spans in your organization, consider adding a process to materialize non-timezone dependent dates for important metrics inside Dataverse. Currency fields are stored with a “base” value and displayed in a converted currency field. – Combining the values of a field with multiple currencies would be invalid. – So, always reference the ‘base’ fields for any type of calculation where different currencies might be included.
Choice Values Only the integer is stored in the row in Dataverse or FabricLink It’s joined at runtime in both places – smaller data and multi-language support
Dataverse Fabric - Choice Values Here's my go-to snippit: LEFT JOIN [dbo].[stringmap] AS [entityname_attributename] ON [entityname_attributename].langid = 1033 AND [entityname_attributename].objecttypecode = '' AND [entityname_attributename].attributename = '' AND [entityname_attributename].attributevalue = [Base].choicefieldname Guide to using the above snippit: langid objecttypecode attributename attributevalue choicefieldname = the language code of values needed - 1033 = US English = entity name = choice value field name = choice numeric value from the record = choice value field name In the list of fields in the query, just reference it in this form: [entityname_attributename].value AS [My Field Alias]
Multi-Select Choices A concatenated list of integers is stored in the row in Dataverse or FabricLink
Dataverse Fabric – Multi-Select Choice Values Dataverse to Fabric OneLake - Part 8 - Multi-Select Fields (youtube.com) WITH CTE_contact_channelactivity AS (SELECT Base.contactid , STRING_AGG(contact_channelactivity.value, ', ') AS channelactivities_string FROM [dbo].[contact] AS Base CROSS APPLY string_split (Base.pbi_channelactivities , ';') AS string JOIN stringmap contact_channelactivity ON contact_channelactivity.attributename = 'pbi_channelactivities' AND contact_channelactivity.objecttypecode = 'contact' AND contact_channelactivity.langid = 1033 AND contact_channelactivity.attributevalue = string.value WHERE Base.pbi_channelactivities IS NOT NULL GROUP BY Base.contactid ) SELECT Base.contactid , Base.fullname AS [Contact Name] , CTE_contact_channelactivity.channelactivities_string AS [Channel Activities] FROM [dbo].[contact] AS Base JOIN CTE_contact_channelactivity ON CTE_contact_channelactivity.contactid = Base.contactid WHERE Base.pbi_channelactivities IS NOT NULL;
Dataverse Fabric – Multi-Select Choice Values Dataverse to Fabric OneLake - Part 8 - Multi-Select Fields (youtube.com)
Thank you for watching. Could you Please provide your feedback on this session and this event. ご視聴ありがとうございました! フィードバックにご協力ください Session survey Event survey 本セッションに対するアンケート イベント全体に対するアンケート 本セッションついて参考になった点や 感銘を受けた点、もっと知りたかったことなどをお寄せください。 運営チームメンバー、登壇者、サポートメンバーに対する 暖かいフィードバックをお待ちしております。 #GlobalPowerPlatformBootcamp #GPPB2025 #GPPB2025JP