{"id":98016,"date":"2023-09-06T19:28:58","date_gmt":"2023-09-06T19:28:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98016"},"modified":"2024-07-11T16:17:21","modified_gmt":"2024-07-11T16:17:21","slug":"microsoft-fabric-lakehouse-and-data-factory-in-power-bi-environment","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/microsoft-fabric-lakehouse-and-data-factory-in-power-bi-environment\/","title":{"rendered":"Microsoft Fabric: Lakehouse and Data Factory in Power BI environment"},"content":{"rendered":"<p>Microsoft is merging <strong>Data Factory<\/strong> and <strong>Power BI Dataflows<\/strong> in one single ETL solution. It&#8217;s not a simple merging, but still these ETL tools are easier to use then ever.<\/p>\n<p>In this article, I will demonstrate how to create a lakehouse and ingest data using a step-by-step walkthrough that you can follow along with. You will see that it is not very difficult and what features are available.<\/p>\n<p>If you need a refresher on what Microsoft Fabric is, you can read the first article I wrote here <a href=\"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/data-intelligence-on-light-speed-microsoft-fabric\/\">overview of Microsoft Fabric<\/a> and this other one to <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-enabling-your-free-trial\/\">enable a free preview<\/a> so you can try it out without spending money.<\/p>\n<h2>Creating the Lakehouse<\/h2>\n<p>Let\u2019s create a new lakehouse, step by step.<\/p>\n<ul>\n<li>Create a new workspace, let&#8217;s call it <strong>lakehouse demo<\/strong>. Use the trial resources explained <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-enabling-your-free-trial\/\">here<\/a><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"567\" height=\"868\" class=\"wp-image-98017\" style=\"border: black solid 2px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-e.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, Email\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Select the Data Engineering Experience<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"332\" height=\"283\" class=\"wp-image-98018\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"914\" height=\"275\" class=\"wp-image-98019\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/tela-de-computador-com-texto-preto-sobre-fundo-bra.png\" alt=\"Tela de computador com texto preto sobre fundo branco\n\nDescri\u00e7\u00e3o gerada automaticamente com confian\u00e7a m\u00e9dia\" \/><\/p>\n<p>Synapse is present everywhere. The title of the window appears as <strong>Synapse Data Engineering<\/strong><\/p>\n<ul>\n<li>Click the Lakehouse button and give it a name, <code>demolake <\/code>(or whatever you want to, but in the article it will be named <code>demolake<\/code>)<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"340\" height=\"213\" class=\"wp-image-98020\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-c.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, chat ou mensagem de texto\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<h2>Ingesting Files into the Lakehouse<\/h2>\n<p>After creating the lakehouse, it\u2019s time to create a data factory pipeline to ingest the data.<\/p>\n<ul>\n<li>Click the menu <strong>Get Data <\/strong><code>-&gt; <\/code><strong>New Data Pipeline<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"542\" height=\"328\" class=\"wp-image-98021\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Give a name to the pipeline as <strong>Ingesting<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"341\" height=\"271\" class=\"wp-image-98022\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-c-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, chat ou mensagem de texto\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The wizard for data ingestion is one of the new features. It makes the work much easier than in previous iterations of these tools. The next image shows the first window of the wizard.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1536\" height=\"748\" class=\"wp-image-98023\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-e-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, Email\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>We will load information from a sample azure blob storage provided by Microsoft and with anonymous access.<\/p>\n<ul>\n<li>Select <strong>Azure Blob Storage<\/strong> as the source and click the <strong>Next <\/strong>button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"576\" height=\"238\" class=\"wp-image-98024\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-descricao-gerada-aut.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>On the following screen, you can choose between an existing connection or a new one. The <strong>Power BI<\/strong> environment saves connections to be reused by different objects and this includes <strong>Microsoft Fabric<\/strong> objects. You can read more about <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/power-bi-connections-management\/\">Power BI and Fabric connection management<\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"887\" height=\"729\" class=\"wp-image-98025\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-2.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>On the <strong>Account Name<\/strong> or <strong>URL<\/strong> textbox, type the following address: <strong>https:\/\/azuresynapsestorage.blob.core.windows.net\/sampledata<\/strong><\/li>\n<li>On the <strong>Connection Name<\/strong> textbox, type <strong>wwisample<\/strong><\/li>\n<li>Click the <strong>Next <\/strong>button.<\/li>\n<\/ul>\n<p>Here we can find how easy a wizard can be: After noticing we don&#8217;t have anonymous access direct to the container, the wizard gives us another window to type the precise path we would like to access and get the data from:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1097\" height=\"717\" class=\"wp-image-98026\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-teams-d.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo, Teams\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>On the textbox in the middle of the wizard, type the following path: <strong><em>\/sampledata\/WideWorldImportersDW\/parquet<\/em><\/strong><\/li>\n<li>Click the <strong>Retry<\/strong> button<\/li>\n<\/ul>\n<p>After the retry, we will be able to see the list of folders available for us and the options about the format for ingestion.<\/p>\n<ul>\n<li>Select the folder <strong>full<\/strong><\/li>\n<li>Mark the checkbox <strong>Schema agnostic (binary copy)<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"767\" height=\"730\" class=\"wp-image-98027\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-3.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The binary copy option, like the one existing in <strong>Data Factory&#8217;s<\/strong> <strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/data-factory\/copy-activity-overview\">Copy Activity<\/a><\/strong>, allow us to copy not only multiple files but multiple folders to the <strong>Files<\/strong> area of the lakehouse.<\/p>\n<p>The same couldn&#8217;t be done to the <strong>Tables<\/strong> area of the lakehouse, we would need to import the tables one by one with the correct schema.<\/p>\n<ul>\n<li>Click the <strong>Next<\/strong> button.<\/li>\n<li>Choose the option <strong>Existing Lakehouse<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"888\" height=\"728\" class=\"wp-image-98028\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>Choosing the lakehouse is the first option on the destination choice.<\/p>\n<ul>\n<li>Click the <em>Next<\/em> button<\/li>\n<\/ul>\n<p>We need to select the destination of the files being copied. The <strong>Tables<\/strong> destination is disabled because we selected the binary copy.<\/p>\n<p>The <strong>Browse<\/strong> button allow you to organize the files you are copying according to your folder structure. In our example, we don&#8217;t have a folder structure yet, so we don&#8217;t need to click the <strong>Browse<\/strong> button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"970\" height=\"734\" class=\"wp-image-98029\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-e-2.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, Email\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <strong>Next<\/strong> button<\/li>\n<\/ul>\n<p>At this point, all the configuration needed for the ingestion is done. We only need to conclude the Wizard and ask for the execution.<\/p>\n<ul>\n<li>Mark the checkbox Start data transfer immediately.<\/li>\n<li>Click the <strong>Save + Run<\/strong> button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1020\" height=\"718\" class=\"wp-image-98030\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-4.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The pipeline will be executed, and you may notice it&#8217;s a simple <strong>Copy Activity<\/strong>, which is usual for a <strong>Data Factory Pipeline<\/strong>. As explained, the pipelines and dataflows in <strong>Microsoft Fabric <\/strong>are a merge between <strong>Power BI<\/strong> and <strong>Data Factory<\/strong> features.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1295\" height=\"201\" class=\"wp-image-98031\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1366\" height=\"192\" class=\"wp-image-98032\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-2.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <em>demolake<\/em> button on the left bar.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"70\" height=\"69\" class=\"wp-image-98033\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/icone-descricao-gerada-automaticamente.png\" alt=\"\u00cdcone\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The left bar adapts itself with icons of recently opened resources, allowing you to navigate among them.<\/p>\n<p>On the lake explorer, you will notice the folders for each table inside the <strong>Files<\/strong> area. The shows you that the data is ingested.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"263\" height=\"335\" class=\"wp-image-98034\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-descricao-gerada-aut-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<h2>Ingest as Table<\/h2>\n<p>We ingested the data as binary files. Let\u2019s analyse how would it be to ingest the data as a <strong>Table<\/strong>.<\/p>\n<p>Back to selecting the source, during the <strong>Copy Data into Lakehouse wizard<\/strong>, if we select one single folder, which contains one single table, and select the correct data format, we can ingest the information to the <strong>Table<\/strong> area of the lakehouse.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1065\" height=\"726\" class=\"wp-image-98035\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-5.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>When selecting tables as destination, we can select the following options:<\/p>\n<ul>\n<li><strong>The table name<\/strong><\/li>\n<li><strong>The Action<\/strong>: We can Append the data to an existing table or overwrite the data of an existing table.<\/li>\n<li><strong>Partitioning<\/strong>: All the storage is kept in Delta format. This format allows to partition the data according to field values. The partitioning will be done breaking the data in different folder. For example, if you choose to partition by Year, a different folder will be created for each year.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"612\" height=\"731\" class=\"wp-image-98036\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-email-d.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo, Email\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<h2>Files and Tables<\/h2>\n<p>The <strong>Files<\/strong> area in the lakehouse is dedicated to RAW data, the data just ingested. After the ingestion, we should load the data in the <strong>Tables<\/strong> area. The Tables area allow the following:<\/p>\n<ul>\n<li>The data will be specially optimized, being able to reach a performance up to 10x the regular delta table performance.<\/li>\n<li>The data is still 100% compatible with delta format.<\/li>\n<li>The data is available for <strong>SQL Queries<\/strong> using the <strong>SQL Endpoint<\/strong><\/li>\n<\/ul>\n<p>Even if the data in the <strong>Files<\/strong> area is already in Delta format, the tables will only be available for the <strong>SQL Endpoint<\/strong> if converted to the <strong>Tables<\/strong> area.<\/p>\n<p>There are two different methods to convert the data form the <strong>Files<\/strong> area to the <strong>Tables<\/strong> area: We can use the UI or we can use a spark notebook.<\/p>\n<h3>Missing Point<\/h3>\n<p>In tools such as <strong>Synapse Serverless<\/strong>, we can use SQL to access data from storage in many different formats using the <code>OPENROWSET<\/code> function. However, this feature is not available in <strong>Microsoft Fabric<\/strong>, at least yet.<\/p>\n<h2>Converting the Files into Tables<\/h2>\n<p>Let\u2019s analyse some differences in relation to the usage of the UI and the usage of a spark notebook:<\/p>\n<table>\n<tbody>\n<tr>\n<td>UI Conversion<\/td>\n<td>Spark Notebook<\/td>\n<\/tr>\n<tr>\n<td>No writing options configuration<\/td>\n<td>Custom writing options configuration<\/td>\n<\/tr>\n<tr>\n<td>No partitioning configuration<\/td>\n<td>Custom partitioning configuration<\/td>\n<\/tr>\n<tr>\n<td>Manual Process<\/td>\n<td>Schedulable process<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The process of conversion is very simple:<\/p>\n<ul>\n<li>Right-Click the folder containing the files.<\/li>\n<li>Select <strong>Load To Tables -&gt; New Table<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"569\" height=\"408\" class=\"wp-image-98037\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-6.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The <strong>Existing Table<\/strong> option allow us to add more data in an existing table manually. However, the process will be too manual, it would be better to schedule a notebook for this.<\/p>\n<ul>\n<li>On the <strong>Load new folder to table<\/strong> window, type the name of the table in the <strong>New table name<\/strong> textbox<\/li>\n<li>On the File type drop down, select <strong>parquet<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"594\" height=\"336\" class=\"wp-image-98038\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-e-3.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, Email\" \/><\/p>\n<ul>\n<li>Repeat the steps 21-24 for each table<\/li>\n<li>On the lakehouse explorer, right click <strong>Tables<\/strong> and click <strong>Refresh<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"355\" height=\"208\" class=\"wp-image-98039\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-7.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>The tables are now available in the <strong>Tables<\/strong> area.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"307\" height=\"312\" class=\"wp-image-98040\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-8.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>On the left bar, click the demolake button.<\/li>\n<\/ul>\n<p><strong>Let&#8217;s look on the storage behind the tables in the lakehouse.<\/strong><\/p>\n<ul>\n<li>Right click the <em>fact_sale<\/em> table<\/li>\n<li>On the context menu, click <strong><em>View files<\/em><\/strong> menu item.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"571\" height=\"593\" class=\"wp-image-98041\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-9.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>Mind how the files are stored in delta format.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"538\" height=\"175\" class=\"wp-image-98042\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-3.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<h2>Using a SQL Endpoint<\/h2>\n<p>The lakehouse provides a <strong>SQL Endpoint<\/strong> which allow us to use <strong>SQL<\/strong> language over the data.<\/p>\n<p>Let&#8217;s make some experiments with the <strong>SQL Endpoint<\/strong>. We can use the box on the top right of the window to change the view from the lakehouse to the <strong>SQL Endpoint<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"380\" height=\"189\" class=\"wp-image-98043\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-c-2.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, chat ou mensagem de texto\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>The <strong>SQL Endpoint<\/strong> changes the <strong>Explorer<\/strong> view to a database format, showing only the <strong>Tables<\/strong> content as a database.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"321\" height=\"708\" class=\"wp-image-98044\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-4.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>On this view, we have the option to create queries, either <strong>SQL Queries<\/strong> or <strong>Visual Queries<\/strong>. The capability to create queries is available in many different places in <strong>Power BI<\/strong> and <strong>Microsoft Fabric<\/strong> environment:<\/p>\n<h3>Power BI Datamarts<\/h3>\n<p>I wrote about them and query creation here (<a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/datamarts-and-exploratory-analysis-using-power-bi\/\">https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/datamarts-and-exploratory-analysis-using-power-bi\/<\/a>) when the UI was still <strong>Design Tab<\/strong> and <strong>SQL Tab<\/strong>. The UI is slightly different today, but the meaning is the same &#8211; query creation, with two types of queries.<\/p>\n<h3>Fabric Data Warehouse<\/h3>\n<p>I wrote about it here (<a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more\/\">https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more\/<\/a>) at that point, I only wrote about using the <strong>SQL Queries<\/strong> to execute <strong>DDL<\/strong> statements.<\/p>\n<p>This is, in fact, one of the differences between a <strong>Data Warehouse<\/strong> and a <strong>SQL Endpoint<\/strong>: The <strong>Data Warehouse<\/strong> has full <strong>DDL<\/strong> support, while the <strong>SQL Endpoint<\/strong> has limited <strong>DLL<\/strong> support.<\/p>\n<h3>Fabric Lakehouse<\/h3>\n<p>This is the example illustrated on this article.<\/p>\n<p>Am I missing other places where the same UI was implemented? Probably. Add on the comments, please.<\/p>\n<h2>Creating a Visual Query<\/h2>\n<ul>\n<li>Click the <strong><em>New Visual Query<\/em><\/strong> button.<\/li>\n<li>Drag the <em>Fact_Sale<\/em> table to the <strong>Visual Query<\/strong> area.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"868\" height=\"226\" class=\"wp-image-98045\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-descricao-ger-1.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto\n\nDescri\u00e7\u00e3o gerada automaticamente com confian\u00e7a m\u00e9dia\" \/><\/p>\n<ul>\n<li>Drag the Dimension_Customer table to the Visual Query area.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"347\" class=\"wp-image-98046\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-e-4.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo, Email\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <em>\u2018<\/em><strong>+<\/strong><em>\u2019<\/em> button on <strong>Fact_Sa<\/strong><em>le<\/em> in the <strong>Visual Query<\/strong> area and select <strong>Merge queries as new<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"498\" height=\"577\" class=\"wp-image-98047\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-10.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Select the <code>Dimension_Customer<\/code> table on the <code>Right table for merge<\/code> drop down.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1062\" height=\"735\" class=\"wp-image-98048\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-tabela-descricao-ge.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Tabela\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <strong>Ok<\/strong> button.<\/li>\n<li>Click on the <strong>Data Display<\/strong> area and move the focus to the last column on the right.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"183\" height=\"194\" class=\"wp-image-98049\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/tabela-descricao-gerada-automaticamente.png\" alt=\"Tabela\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <strong>Expand<\/strong> button and select only the <code>BuyingGroup<\/code> column.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"381\" height=\"394\" class=\"wp-image-98050\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-11.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<p>After making a join between the tables, the columns from the first table are kept and we choose which columns from the second table we would like to include in the join as well.<\/p>\n<ul>\n<li>Click the <em>\u2018<\/em><strong>+<\/strong><em>\u2019<\/em> button on the merged table and select <strong>Group By<\/strong><img loading=\"lazy\" decoding=\"async\" width=\"843\" height=\"456\" class=\"wp-image-98051\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-descricao-gerada-aut-2.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio\n\nDescri\u00e7\u00e3o gerada automaticamente com confian\u00e7a m\u00e9dia\" \/><em>\u00a0<\/em><\/li>\n<\/ul>\n<ul>\n<li>On the <strong>Group By<\/strong> window, Select <code>BuyingGroup<\/code> field in the <strong>Group By<\/strong> dropdown.<\/li>\n<li>On the <strong>New column name<\/strong> textbox type <code>TotalWithTaxes<\/code><\/li>\n<li>On the <strong>Operation<\/strong> dropdown, select <strong>Sum<\/strong>.<\/li>\n<li>On <strong>Column<\/strong> dropdown, select <code>TotalIncludingTax<\/code> field.<\/li>\n<\/ul>\n<p>We have only used the <strong>Basic<\/strong> option on this example. The <strong>Advanced<\/strong> option would allow us to add multiple grouping fields and multiple aggregations.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"667\" height=\"361\" class=\"wp-image-98052\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-37.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<ul>\n<li>Click the <strong>Ok<\/strong> button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"840\" height=\"508\" class=\"wp-image-98053\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-descricao-gerada-aut-3.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>On the left window, under <strong>My Queries<\/strong>, click the expand <em>\u201c<\/em><strong>..<\/strong><em>\u201d<\/em> button close the <strong>Visual Query 1 <\/strong>and select <strong>Rename<\/strong>.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"482\" height=\"264\" class=\"wp-image-98054\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-12.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>On the <strong>Rename<\/strong> window, on the <strong>Name<\/strong> textbox, type <code>Totals By Customer<\/code><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"339\" height=\"187\" class=\"wp-image-98055\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-5.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>Click the <strong>Rename<\/strong> button.<img loading=\"lazy\" decoding=\"async\" width=\"237\" height=\"100\" class=\"wp-image-98056\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-texto-aplicativo-d-6.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Texto, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/li>\n<\/ul>\n<h2>What to do with the queries<\/h2>\n<p>After creating a visual query, we can use it in many ways:<\/p>\n<ul>\n<li>We can click the <strong>View SQL<\/strong> button and use the SQL to create a view or a materialized object.<\/li>\n<\/ul>\n<p>At the time of publishing this article, the preview version has a bug and only shows the initial SQL query, not the final query with all the transformations. We hope this is fixed soon.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"207\" height=\"60\" class=\"wp-image-98057\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/interface-grafica-do-usuario-aplicativo-descrica-13.png\" alt=\"Interface gr\u00e1fica do usu\u00e1rio, Aplicativo\n\nDescri\u00e7\u00e3o gerada automaticamente\" \/><\/p>\n<ul>\n<li>We can download an excel file and continue our exploration on the Excel file.<\/li>\n<\/ul>\n<p>The Excel is dynamically configured to execute the query against the Lakehouse. As a result, the data is dynamically load from the lake every time we open the Excel. We can save this file and use it to analyse the same query as many times as we would like.<\/p>\n<p>These are the steps to download and use an Excel file:<\/p>\n<ol>\n<li>Click the <em>Download Excel File<\/em> button on the <em>Display area<\/em>.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"350\" height=\"41\" class=\"wp-image-98058\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-98016-42.png\" \/><\/p>\n<ul>\n<li>Open the downloaded excel file.<\/li>\n<li>Click the <em>Enable Editing<\/em> button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"869\" height=\"101\" class=\"wp-image-98059\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-98016-43.png\" \/><\/p>\n<p>This first warning is caused because the Excel file was downloaded from the web. This is the first level of security.<\/p>\n<ul>\n<li>On the <strong>Security Warning<\/strong>, click <strong>Enable Content<\/strong> button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"537\" height=\"198\" class=\"wp-image-98060\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-38.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>The Excel file opened contains dynamic content, including a SQL query to build an Excel table. This is active content which needs to be enabled. This is the 2nd level of security.<\/p>\n<ul>\n<li>On the <strong>Native Database Query<\/strong> window, click the <strong>Run<\/strong> button.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"904\" height=\"600\" class=\"wp-image-98061\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-39.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>We will be running a SQL query pointing to a server. Excel shows the query and asks for confirmation. This is the 3rd level of security.<\/p>\n<ul>\n<li>On the <strong>SQL Server Database<\/strong> window, choose <strong>Microsoft Account<\/strong> on the left side.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"701\" height=\"379\" class=\"wp-image-98062\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-40.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<ul>\n<li>Click the Sign In button and login with your Microsoft Fabric account.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"703\" height=\"380\" class=\"wp-image-98063\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-41.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>Mind the SQL Server address on this window: It\u2019s in fact the SQL Endpoint address from the lakehouse.<\/p>\n<ul>\n<li>Click the <em>Connect<\/em> button.<\/li>\n<\/ul>\n<p>The SQL query will only be executed if the user opening the Excel file has permissions on the <strong>Microsoft Fabric SQL Endpoint<\/strong>. This is the 4th level of security.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"258\" height=\"112\" class=\"wp-image-98064\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-42.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<ul>\n<li>We can turn the query into a shared query and allow other team members to continue the data exploration.<\/li>\n<\/ul>\n<p>On the context menu for the query, we choose <strong>Move to Shared Queries<\/strong>. We can move back to <strong>My Queries<\/strong> if we would like so.<\/p>\n<p>There are only these two options: Or the query is private to the user, or it\u2019s accessible to every user who has access to the lakehouse.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"485\" height=\"302\" class=\"wp-image-98065\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-43.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>Modelling the Tables<\/h2>\n<p>The SQL Endpoint in a lakehouse allow us to model the tables in the same way it does in a <strong>Data Warehouse<\/strong> and I illustrated on my <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more\/\">article about Data Warehouse<\/a>.<\/p>\n<p>In the same way, the model is stored in the default dataset. By doing so, we establish a default modelling we would like the consumers to have when accessing the lakehouse. This is not only about table relationships, but also the creation of measures.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1187\" height=\"748\" class=\"wp-image-98066\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-44.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>Creating a Report<\/h2>\n<p>I already explained the process of building a report on my <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more\/\">article about Data Warehouse<\/a>, it\u2019s very similar. I will approach details about the access from Power BI to <strong>OneLake<\/strong> in future articles.<\/p>\n<p>The only difference worth mentioning on this point is that clicking on the button <strong>Visualize Results<\/strong> will create a dataset and a report from the query we visually generated. We can download and inspect the resulting dataset. It\u2019s built with a single SQL query towards the lakehouse <strong>SQL Endpoint.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1473\" height=\"216\" class=\"wp-image-98067\" style=\"border: black solid 1px;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-white-rectangular-object-with-colorful-text.png\" alt=\"A white rectangular object with colorful text\" \/><\/p>\n<h2>Summary<\/h2>\n<p>Microsoft Fabric is a great tool, as I highlighted on my overview. It provides plenty of options about how to manage our data, either using a Data Warehouse, explained in a previous article, or a lake house, explained on this article. All the options linked by a common environment, the Power BI, going way beyond the BI area.<\/p>\n<p>In future articles I will approach how to choose the option to use and how to organize these scenarios on an enterprise level.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft is merging Data Factory and Power BI Dataflows in one single ETL solution. It&#8217;s not a simple merging, but still these ETL tools are easier to use then ever. In this article, I will demonstrate how to create a lakehouse and ingest data using a step-by-step walkthrough that you can follow along with. You&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":103101,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137091,53],"tags":[],"coauthors":[6810],"class_list":["post-98016","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-featured"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98016","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=98016"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98016\/revisions"}],"predecessor-version":[{"id":103112,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98016\/revisions\/103112"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103101"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98016"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98016"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98016"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98016"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}