{"id":5491,"date":"2023-01-24T06:30:57","date_gmt":"2023-01-24T14:30:57","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=5491"},"modified":"2023-01-24T06:28:32","modified_gmt":"2023-01-24T14:28:32","slug":"cosmos-db-power-bi-incremental-refresh","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/cosmos-db-power-bi-incremental-refresh\/","title":{"rendered":"Azure Cosmos DB &#038; Power BI Incremental Refresh"},"content":{"rendered":"<h2>Abstract<\/h2>\n<p>Data Collection has become a daunting task with the ever-growing amount of data.\u00a0 The growth of data makes reporting tasks an operational challenge due to the processing time requirements imposed by technical limitations in many business intelligence tools. The paper outlines the<em> incremental refresh capability of Microsoft\u2019s Power BI<\/em> and its impact on analytics processing and reporting.<\/p>\n<hr \/>\n<p aria-level=\"2\"><span style=\"font-size: 24pt;\">Scenario Overview\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">In this blog post, we want to document a customer engagement we worked on where we learned about a unique feature in Power BI that can be used when using Azure Cosmos DB\u2019s SQL API. The result of the solution here provided performance benefits that were impressive, and we wanted to share our learnings.\u00a0\u00a0<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">We worked with a large IoT customer that was doing daily reports on all of their networks in a NoSQL database.\u00a0 As the NoSQL database grew, it caused massive increases in processing time by the Business Intelligence tools they were using.\u00a0 Not much different from many state-of-the-art BI tools, an incremental refresh was not possible.\u00a0 In short, the complete contents of the database were queried and fetched for daily reporting.<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">To address this challenge, the recommendation was to leverage Azure Cosmos DB SQL API along with Power BI and implement <\/span><a href=\"https:\/\/learn.microsoft.com\/en-us\/power-bi\/connect-data\/incremental-refresh-configure\"><span data-contrast=\"none\">incremental refresh<\/span><\/a><span data-contrast=\"auto\">. Power BI can connect directly to Azure Cosmos DB to build analytics and visualize information.\u00a0 With Power BI, there are 2 modes of connectivity: 1. direct query and 2. import.\u00a0 Each has its pros and cons and depending on your scenario, one might make more sense than the other if you don\u2019t fall neatly into one or the other, then you can leverage a combination of both modes known as composite models.\u00a0 <\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<p aria-level=\"2\"><span style=\"font-size: 24pt;\">Import Mode\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">In this article, we will focus on import mode to unlock all the features of Power BI. The contents below will provide a step-by-step on how to set up Power BI incremental refresh when connecting to an Azure Cosmos DB. Please note, at the time of writing this post the ability to do incremental refresh is only available with the Azure Cosmos DB SQL API.\u00a0 Azure Synapse Link for Azure Cosmos DB is a good use case for overcoming the API limitations plus it can also be used to set up incremental loads to Power BI if needed.\u00a0 <\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\"><em>The value of Power BI incremental refresh is that it can avoid doing a full refresh of the data<\/em>. It will only fetch data that has been changed or added from the previous data load. There are many advantages to implementing the Power BI incremental refresh including:<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<ul>\n<li data-leveltext=\"\uf0d8\" data-font=\"Wingdings\" data-listid=\"1\" data-list-defn-props=\"{&quot;335552541&quot;:1,&quot;335559684&quot;:-2,&quot;335559685&quot;:720,&quot;335559991&quot;:360,&quot;469769226&quot;:&quot;Wingdings&quot;,&quot;469769242&quot;:[8226],&quot;469777803&quot;:&quot;left&quot;,&quot;469777804&quot;:&quot;\uf0d8&quot;,&quot;469777815&quot;:&quot;hybridMultilevel&quot;}\" aria-setsize=\"-1\" data-aria-posinset=\"1\" data-aria-level=\"1\"><span data-contrast=\"auto\">More efficient processing and faster refresh operations\u00a0<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/li>\n<li data-leveltext=\"\uf0d8\" data-font=\"Wingdings\" data-listid=\"1\" data-list-defn-props=\"{&quot;335552541&quot;:1,&quot;335559684&quot;:-2,&quot;335559685&quot;:720,&quot;335559991&quot;:360,&quot;469769226&quot;:&quot;Wingdings&quot;,&quot;469769242&quot;:[8226],&quot;469777803&quot;:&quot;left&quot;,&quot;469777804&quot;:&quot;\uf0d8&quot;,&quot;469777815&quot;:&quot;hybridMultilevel&quot;}\" aria-setsize=\"-1\" data-aria-posinset=\"1\" data-aria-level=\"1\"><span data-contrast=\"auto\">Faster PBI publishing after a refresh\u00a0<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/li>\n<li data-leveltext=\"\uf0d8\" data-font=\"Wingdings\" data-listid=\"1\" data-list-defn-props=\"{&quot;335552541&quot;:1,&quot;335559684&quot;:-2,&quot;335559685&quot;:720,&quot;335559991&quot;:360,&quot;469769226&quot;:&quot;Wingdings&quot;,&quot;469769242&quot;:[8226],&quot;469777803&quot;:&quot;left&quot;,&quot;469777804&quot;:&quot;\uf0d8&quot;,&quot;469777815&quot;:&quot;hybridMultilevel&quot;}\" aria-setsize=\"-1\" data-aria-posinset=\"1\" data-aria-level=\"1\"><span data-contrast=\"auto\">More reliability since there are fewer long-running connections<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/li>\n<\/ul>\n<p aria-level=\"2\"><span style=\"font-size: 24pt;\">Getting started\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">Let&#8217;s begin with a step-by-step incremental refresh setup between Power BI and Azure Cosmos DB.<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<table style=\"width: 95.788%; height: 248px;\">\n<tbody>\n<tr style=\"height: 248px;\">\n<td style=\"width: 44.426%; height: 248px;\">These are the major steps we will go through:<\/p>\n<ul>\n<li>Step 1 \u2013 View Source Azure Cosmos DB<\/li>\n<li>Step 2 \u2013 Setup Power BI Connectivity &amp; Incremental Refresh<\/li>\n<li>Step 3 \u2013 Validate Power BI Incremental Setup<\/li>\n<\/ul>\n<\/td>\n<td style=\"width: 71.0213%; height: 248px;\"><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport.png\"><img decoding=\"async\" class=\"alignnone wp-image-5499\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport.png\" alt=\"Image CosmosImport\" width=\"671\" height=\"306\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport.png 1436w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport-300x137.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport-1024x466.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/CosmosImport-768x350.png 768w\" sizes=\"(max-width: 671px) 100vw, 671px\" \/><\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-size: 24pt;\">Resources<\/span><\/h2>\n<p>If you want to reproduce the architecture in this blog, you can set up your own incremental refresh with Azure Cosmos DB by following <a href=\"https:\/\/github.com\/Azure-Samples\/PowerBI-Incremental-update\">this GitHub Repo<\/a> that contains files using a demo of Power BI\u2019s Incremental Refresh capability.\u00a0 The following tools were used:<\/p>\n<ul>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Data Simulation: <a href=\"https:\/\/github.com\/rueckstiess\/mgeneratejs\">mgenerate.js\/Chance.js<\/a> (template to generate this is provided)<\/li>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/data-factory\/introduction\">Azure Data Factory<\/a>: Loaded Azure <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/cosmos-db\/introduction\">Cosmos DB<\/a> with simulated data<\/li>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Azure Blob Storage: Staging for data to be loaded into Azure Cosmos DB<\/li>\n<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Azure <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/cosmos-db\/choose-api#coresql-api\">Cosmos DB: Utilized Cosmos SQL API for our Document Model<\/a><\/li>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <a href=\"https:\/\/powerbi.microsoft.com\/en-us\/desktop\/\">Power BI Desktop<\/a>: Created Data Model<\/li>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <a href=\"https:\/\/powerbi.microsoft.com\/\">Power BI System<\/a>: Deployed Data Model, could be used for Data Flow<\/li>\n<li>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <a href=\"https:\/\/daxstudio.org\/\">DAX Studio<\/a>: Used to display partitions proving incremental<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-size: 24pt;\">Step 1 \u2013 View Source Azure Cosmos DB<\/span><\/h2>\n<p>Go to the Azure Portal and into the Azure Cosmos DB blade. The following is a sample of a single document on August 3<sup>rd<\/sup>, 2022:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5502\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit.png\" alt=\"Image Cosmos Edit\" width=\"1385\" height=\"1099\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit.png 1385w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit-300x238.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit-1024x813.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-Edit-768x609.png 768w\" sizes=\"(max-width: 1385px) 100vw, 1385px\" \/><\/a><\/p>\n<p>We first created a historical\/initial load with the August 3<sup>rd<\/sup>, 2022, date in Power BI.\u00a0 After the August 3<sup>rd<\/sup>, 2022, PBI load was completed, we manually added documents from August 10<sup>th<\/sup> to August 17<sup>th<\/sup> before setting up the incremental refresh. After manually adding the future dated documents from August 10<sup>th<\/sup> to August 17<sup>th<\/sup>, the data distribution of documents by date in Azure Cosmos DB could be summarized as follows:<\/p>\n<p>On August 9<sup>th<\/sup>, 2022, we had the following distribution of documents in our source Azure Cosmos DB.<\/p>\n<table style=\"height: 422px; width: 30.4305%; background-color: #67d9eb; border-color: #434391;\" width=\"30.4305%\">\n<tbody>\n<tr style=\"height: 70px;\">\n<td style=\"height: 70px; width: 41.5205%; text-align: center;\" width=\"143\">Date<\/td>\n<td style=\"height: 70px; width: 66.9591%; text-align: center;\" width=\"200\">Number of documents added<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 3rd, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">1000<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 10th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">1<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 11th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">2<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 13th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">1<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 14th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">1<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 15th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">3<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">August 16th, 2022<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">4<\/td>\n<\/tr>\n<tr style=\"height: 44px;\">\n<td style=\"height: 44px; width: 41.5205%; text-align: center;\" width=\"143\">\n<p style=\"text-align: center;\">August 17th, 2022<\/p>\n<\/td>\n<td style=\"height: 44px; width: 66.9591%; text-align: center;\" width=\"200\">3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>Here is an example of the document we created for August 16<sup>th<\/sup> &#8211; note all other manually added documents are similar:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5503\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs.png\" alt=\"Image Cosmos AddedDocs\" width=\"1488\" height=\"1211\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs.png 1488w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs-300x244.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs-1024x833.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Cosmos-AddedDocs-768x625.png 768w\" sizes=\"(max-width: 1488px) 100vw, 1488px\" \/><\/a><\/p>\n<h2><span style=\"font-size: 24pt;\">Step 2 \u2013 Setup Power BI Connectivity &amp; Incremental Refresh<\/span><\/h2>\n<p>&nbsp;<\/p>\n<p>Let\u2019s open up Power BI desktop and click on Get Data.\u00a0 After that, let\u2019s enter the Azure Cosmos DB URL which you can get from the Azure Cosmos DB Overview blade.<\/p>\n<p aria-level=\"2\"><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-5504\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh-1024x535.png\" alt=\"Image Desktop setuprefresh\" width=\"640\" height=\"334\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh-1024x535.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh-300x157.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh-768x401.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh-1536x802.png 1536w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-setuprefresh.png 1974w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p>Once connected to our Azure Cosmos DB, we moved to transform the data in Power Query to prepare the document.\u00a0 In our case, the Document Model was used with 70+ embedded arrays and sub-documents in this data model for this IOT sink repository.\u00a0 We leveraged Power BI to flatten embedded JSON by creating a quasi-normalization with tables. \u2014 See below.\u00a0 After connecting to our Azure Cosmos DB SQL source, we clicked on the double arrows and clicked on OK to flatten the \u201cRecord\u201d view into a tabular view. In order to work with the data and create reports, we need to flatten the JSON document into rows and columns \u2014 See below.<\/p>\n<p>After connecting to our Azure Cosmos DB SQL source, we clicked on the double arrows and clicked on OK to flatten the \u201cRecord\u201d view into a tabular view. In order to work with the data and create reports, we need to flatten the JSON document into rows and columns.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dataSource.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-5505\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dataSource-768x1024.png\" alt=\"Image Desktop dataSource\" width=\"640\" height=\"853\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dataSource-768x1024.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dataSource-225x300.png 225w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dataSource.png 1090w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p>After going through the flattening process, the initial JSON document was deconstructed as follows:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5507\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat.png\" alt=\"Image Desktop 8211 flat\" width=\"1436\" height=\"631\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat.png 1436w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat-300x132.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat-1024x450.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-flat-768x337.png 768w\" sizes=\"(max-width: 1436px) 100vw, 1436px\" \/><\/a><\/p>\n<p>Once we had the JSON document denormalized, we followed the instructions on how to set up incremental refresh. Please note, we first did setup the initial load with the August 3<sup>rd<\/sup> date and published this to the Power BI service.\u00a0 After the initial load, we went back to Power BI in order to configure the incremental refresh portion.\u00a0 In order to achieve that, we first created 2 variables in the form of<\/p>\n<ul>\n<li>RangeStart &#8211; set to August 3<sup>rd<\/sup>, 2022<\/li>\n<li>RangeEnd &#8211; set to end on September 30<sup>th<\/sup>, 2022<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5508\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges.png\" alt=\"Image Desktop 8211 dateranges\" width=\"1252\" height=\"793\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges.png 1252w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges-300x190.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges-1024x649.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/Desktop-dateranges-768x486.png 768w\" sizes=\"(max-width: 1252px) 100vw, 1252px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>After creating the RangeStart and RangeEnd variables, we applied them to the source data as shown below so that only records greater than RangeStart would be selected but less than RangeEnd.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5509\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect.png\" alt=\"Image desktop tableselect\" width=\"1696\" height=\"292\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect.png 1696w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect-300x52.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect-1024x176.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect-768x132.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-tableselect-1536x264.png 1536w\" sizes=\"(max-width: 1696px) 100vw, 1696px\" \/><\/a><\/p>\n<p style=\"text-align: center;\"><span style=\"font-size: 10pt;\"><strong>= Table.SelectRows(#&#8221;Filtered Rows&#8221;, each [date] &gt;= RangeStart and [date] &lt;= RangeEnd)<\/strong><\/span><\/p>\n<p>After creating the logic described above on August 9<sup>th<\/sup>, we published our Power BI model and set up an incremental refresh to run daily starting on August 10<sup>th<\/sup> at 6am PST.\u00a0 See the screenshot below:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5510\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap.png\" alt=\"Image desktop refreshrecap\" width=\"1111\" height=\"580\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap.png 1111w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap-300x157.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap-1024x535.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/desktop-refreshrecap-768x401.png 768w\" sizes=\"(max-width: 1111px) 100vw, 1111px\" \/><\/a><\/p>\n<h3><span style=\"font-size: 18pt;\">Recap &#8211; Data Distribution in Azure Cosmos DB<\/span><\/h3>\n<p>Once again, as of August 9<sup>th<\/sup>, there were only the 1000 documents that were loaded\/available on August 3<sup>rd<\/sup>, 2022.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-1.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5511\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-1.png\" alt=\"Image table 1\" width=\"314\" height=\"254\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-1.png 314w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-1-300x243.png 300w\" sizes=\"(max-width: 314px) 100vw, 314px\" \/><\/a><\/p>\n<p>We set up the initial incremental load on August 10<sup>th,<\/sup> 2022, and so on August 11<sup>th<\/sup> at 6am PST, the scheduled refresh would kick off every day at the same time. Before the August 11<sup>th<\/sup> run, we added the following documents to Azure Cosmos DB:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-2.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5512\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-2.png\" alt=\"Image table 2\" width=\"664\" height=\"398\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-2.png 664w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/table-2-300x180.png 300w\" sizes=\"(max-width: 664px) 100vw, 664px\" \/><\/a><\/p>\n<h2><span style=\"font-size: 24pt;\">Step 3 <\/span><span style=\"font-size: 24pt;\">\u2013 Validate Power BI Incremental Setup<\/span><\/h2>\n<p><span data-contrast=\"auto\">There are a few ways we can validate that our incremental refresh is working as we had intended.\u00a0 One of those tools is a free download in the form of <\/span><a href=\"https:\/\/daxstudio.org\/downloads\/\"><span data-contrast=\"none\">DAX Studio<\/span><\/a><span data-contrast=\"auto\">.\u00a0 We can leverage DAX Studio and connect to the power BI XMLA endpoint. We want to see the partitions created and the row counts associated with each partition. The partitions and row counts need to match what we saw in our original source data (please see screenshots earlier in this blog).\u00a0 The XMLA endpoint can be found in the Power BI workspace and in our case, resembles:<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<ul>\n<li><span style=\"font-size: 10pt;\">\u00a0<strong>powerbi:\/\/api.powerbi.com\/v1.0\/myorg\/yourworkspace;initial catalog=BlogArticle<\/strong>\u00a0<\/span><\/li>\n<\/ul>\n<p><span data-contrast=\"auto\">Connecting to your Power BI XMLA endpoint in DAX Studio appears as follows:<\/span><span data-ccp-props=\"{}\">\u00a0<\/span><\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5513\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect.png\" alt=\"Image pbi connect\" width=\"1402\" height=\"910\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect.png 1402w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect-300x195.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect-1024x665.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/pbi-connect-768x498.png 768w\" sizes=\"(max-width: 1402px) 100vw, 1402px\" \/><\/a><\/p>\n<p>Once in DAX Studio, we can click on View Metrics of our BlogArticle database and then click on partitions to see what our model looks like:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5514\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio.png\" alt=\"Image DAX studio\" width=\"1696\" height=\"1319\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio.png 1696w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio-300x233.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio-1024x796.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio-768x597.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/DAX-studio-1536x1195.png 1536w\" sizes=\"(max-width: 1696px) 100vw, 1696px\" \/><\/a><\/p>\n<p>We can see our partitions for the dates between August 10<sup>th<\/sup> to August 17<sup>th<\/sup> along with the row counts are a match to our source.\u00a0 Let\u2019s take the final step and confirm that these were indeed created daily and on an incremental basis. In order to do that, we will leverage SSMS (SQL Server Management Studio)<\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"font-size: 24pt;\">SSMS \u2013 SQL Server Management Studio<\/span><\/h3>\n<p>Let\u2019s now validate that those partitions were indeed incremental. We want to see the processed time when those newly created partitions were created. For that, we go to SQL Server Management Studio and connect to the XML endpoint of our PBI model. In our case \u2013 the endpoint looks something like this:<\/p>\n<ul>\n<li><span style=\"font-size: 10pt;\"><strong>powerbi:\/\/api.powerbi.com\/v1.0\/myorg\/yourworkspace;initial catalog=BlogArticle<\/strong><\/span><\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5515\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms.png\" alt=\"Image ssms\" width=\"1696\" height=\"931\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms.png 1696w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-300x165.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-1024x562.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-768x422.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-1536x843.png 1536w\" sizes=\"(max-width: 1696px) 100vw, 1696px\" \/><\/a><\/p>\n<p>After connecting to our model via SSMS, we can click on the BlogArticle database and right click on the Blogger collection, and click on \u201cpartitions\u201d as shown below:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5516\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions.png\" alt=\"Image ssms partitions\" width=\"1225\" height=\"909\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions.png 1225w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions-300x223.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions-1024x760.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-partitions-768x570.png 768w\" sizes=\"(max-width: 1225px) 100vw, 1225px\" \/><\/a><\/p>\n<p>We can now see that each of these partitions between August 10<sup>th<\/sup> and August 17<sup>th<\/sup> was created on the day indicated by the \u201cLast Processed\u201d time at around 6am PST as per our initial schedule.\u00a0 This confirms that the loads are indeed incremental and full refreshes are not being executed.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-5517\" src=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail.png\" alt=\"Image ssms part detail\" width=\"1696\" height=\"1171\" srcset=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail.png 1696w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail-300x207.png 300w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail-1024x707.png 1024w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail-768x530.png 768w, https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2023\/01\/ssms-part-detail-1536x1061.png 1536w\" sizes=\"(max-width: 1696px) 100vw, 1696px\" \/><\/a><\/p>\n<h2><span style=\"font-size: 24pt;\">Conclusion<\/span><\/h2>\n<p>Incremental refresh is a powerful capability for a business analytics tool.\u00a0 In the case where there are relatively larger amounts of data, there is a significant improvement in the processing required to refresh the data into Power BI memory.\u00a0 In addition to incremental refresh, processing complex document model sources can be done in place removing the need for complex queries to reassemble the data for report processing.<\/p>\n<h3><\/h3>\n<h2>About Azure Cosmos DB<\/h2>\n<p><a href=\"https:\/\/azure.microsoft.com\/services\/cosmos-db\/\">Azure Cosmos DB<\/a> is a fast and scalable distributed NoSQL database, built for modern application development. Get guaranteed single-digit millisecond response times and 99.999-percent availability, <a href=\"https:\/\/azure.microsoft.com\/support\/legal\/sla\/cosmos-db\/\">backed by SLAs<\/a>, <a href=\"https:\/\/docs.microsoft.com\/azure\/cosmos-db\/scaling-throughput\">automatic and instant scalability<\/a>, and open-source APIs for MongoDB and Cassandra. Enjoy fast writes and reads anywhere in the world with turnkey data replication and multi-region writes.<\/p>\n<p>To easily build your first database, watch our <a href=\"https:\/\/youtube.com\/playlist?list=PLmamF3YkHLoLLGUtSoxmUkORcWaTyHlXp\">Get Started videos<\/a> on YouTube and explore ways to <a href=\"https:\/\/docs.microsoft.com\/azure\/cosmos-db\/optimize-dev-test\">dev\/test free<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Abstract Data Collection has become a daunting task with the ever-growing amount of data.\u00a0 The growth of data makes reporting tasks an operational challenge due to the processing time requirements imposed by technical limitations in many business intelligence tools. The paper outlines the incremental refresh capability of Microsoft\u2019s Power BI and its impact on analytics [&hellip;]<\/p>\n","protected":false},"author":94427,"featured_media":5498,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"link","meta":{"_acf_changed":false,"footnotes":""},"categories":[14],"tags":[499,1810],"class_list":["post-5491","post","type-post","status-publish","format-link","has-post-thumbnail","hentry","category-core-sql-api","tag-azure-cosmos-db","tag-power-bi","post_format-post-format-link"],"acf":[],"blog_post_summary":"<p>Abstract Data Collection has become a daunting task with the ever-growing amount of data.\u00a0 The growth of data makes reporting tasks an operational challenge due to the processing time requirements imposed by technical limitations in many business intelligence tools. The paper outlines the incremental refresh capability of Microsoft\u2019s Power BI and its impact on analytics [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/5491","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/94427"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=5491"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/5491\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/5498"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=5491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=5491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=5491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}