Skip to content

Tag: Limit

Exceed the 500,000 row limit in Application Insights and Log Analytics with Power BI

The combination of Power BI and Application Insights (AI)/Log Analytics (LA) is a powerful one. These tools provide a quick, convenient, and relatively cheap way to collect and analyze telemetry on a wide variety of applications. One drawback of AI/LA is that any data query will return a maximum of 500,000 rows, which can be quite constraining in some cases. This article describes a way to work around this limit.

In this example, we’ll be working with an Application Insights instance that is being populated by the WordPress Application Insights plugin – in fact, it’s the one used on this very blog. There are a couple of ways to connect Power BI Desktop to AI data. The Power Query code is downloadable directly from Application Insights, and you can also use the Azure Data Explorer proxy address as outline in my post on the topic here. This approach will work for both methods, and for our purposes, we’ll be using the generated Power Query code approach.

To begin, access your Application Insights instance, and open the Logs window. If necessary, dismiss the “Queries” window that pops up. Next, form your query using Kusto Query Language (KQL). In our case, we want a simple dump of all rows in the “pageViews” table, so the query is simple – just pageViews.

Once we have the query the way that we want it, we select the Export button, and choose “Export to Power BI (M query). M is the name of the language that Power Query uses. Once chosen, a text file will be downloaded that contains the Power Query that we will need in Power BI Desktop.

At this point, we launch Power BI Desktop, and choose “Get Data”. Since we already have the query that we need, we will choose “Blank Query”.

Next, we name our query “Page Views”, and select the Advanced Editor. This is where we can paste in the query generated by Application Insights above. At this point, we open the file that was downloaded above, copy the contents, and paste them in this window (the top comments can be excluded).

Of note here is the value that will be automatically set for timespan. By default, this will be set to P1D, which means data will be retrieved only for the previous day. In our example above, we have changed it to show data for the past 365 days.

Selecting “Done” will load a preview of our data into Power Query. However, if we want to then load it into the data model, it will do so in a single pull, and we will be subject to the 500,000 row limit. What we need to do is break up our query into multiple queries, and Power Query lets us do this through the use of functions.

The first thing that we’ll need to do is to decide on how to segment the AI data. In our case, it is unlikely that we will have more than 500,000 page views per month, so if we performed one query per month, we should be able to retrieve all of our data. In order to do this, we need to go back to Application Insights, and form up a query that will return a list of year and month for our data. In our case, this query is:

pageViews
| where timestamp > now(-365d)
| summarize by 
    Year = datetime_part('Year',timestamp), 
    Month = datetime_part('Month',timestamp)

Note that the number of days in the where clause above should match the number of days in the larger query above. Next, export this query to Power BI, and create another query in Power Query. Leave the name as default for now. Selecting Done should return a list of years and months for your data. These values are all numbers, and Power Query recognizes them as such. However, we need to work with them as text later on , so we change their types to text.

Now we will return to our original query, and modify it so that it only returns data for a single month. Reopen the advanced editor and replace the query “pageViews” with:

pageViews | where datetime_part('Month',timestamp) == 10 and datetime_part('Year',timestamp) == 2020

The values chosen don’t matter, but they should return data, In the end, the edited code should look as follows:

Selecting done, we verify that we have data restricted to the specified month. This is where the fun begins. We are now going to turn this query into a function. To do so, we right click on our pageViews Query, and select “Create Function”

We are then presented with a dialog box that asks if we want to create the function without parameters. We can go ahead and select “Create”. We are then prompted to name the function, and we’ll call it “GetViewsByMonthAndYear”. We now need to edit the function. To do so, with the function selected in the query pane, we select the Advanced Editor once again. We then dismiss the following warning, and then we edit the function in two places. First, we need to define two variables to be passed to the function Month and Year , and then we add them to our query.

In the function declaration we add “Month as text” and “Year as text”. We then replace the explicit month and year that we originally queried for with these new variables, Month and Year. Our function code now appears as below:

Now we are ready to use our function. We select our query that contains the list of years and months, select the “Add Column” tab from the ribbon, and choose “Invoke Custom Function”. We give the new column a name “Views”, select our function from the dropdown, and then we select our column containing years and the column containing months to be passed to the function.

At this point, selecting “OK” will cause the function to be executed for each of the listed months. These are individual queries to AI, not one large one. Each query is still subject to the 500,000 row limit, but provided that no specific month exceeds that limit, all of the data will be returned.

Initially, the data is returned as a single table per day, but selecting the expand icon at the right of the column header allows us to retrieve the row values. It’s also a good idea to turn off the “Use original column name” option.

Selecting OK at this point displays all of the appropriate column values. We can then remove the “Year” and “Month” columns, as well as the original Page Views table that we used to create the function. We also need to set the data types for all of our columns because Power Query is unable to detect them using this approach.

Renaming our combined Query to Views, gives us the following result:

We still have a single table, but there is no longer a 500,00 row limit. At this point, we can load the data into the model and build our report.

Leave a Comment

Increase your OneDrive Storage Limit beyond 5 TB

OneDrive for Business offers “unlimited” storage. You would be excused if you were sure that the limit was 1 TB because that is what it is set to by default, Microsoft would prefer it if you didn’t exercise this particular option. Individual users can’t change their limits, and administrators can only up that limit to 5 TB. Increasing it beyond that limit requires extra steps. I have just gone through those steps for my own OneDrive for business, and thought that I would share the experience.

The stratification of the different OneDrive for Business storage options has been outlined effectively by Joel Oleson in his article Three Tiers to Increase to Unlimited Storage in OneDrive for Business.

The bottom line is that you must increase the quota limit for a OneDrive in stages. These stages are:

  1. Increase quota from 1 TB to 5TB
  2. Reach 90% of the OneDrive storage limit (4.5 TB)
  3. Open a support ticket to turn on “Boost Quota” for the tenant
  4. Increase quota from 5 TB to 25TB
  5. Reach 90% of the OneDrive storage limit (22.5 TB)
  6. Open a support ticket to turn create a new site collection with 25 TB quota
  7. Repeat steps 5 & 6 as necessary

I have just completed step 4, so that’s as far as this post will go. It likely goes without saying, but all of the operations below require tenant admin permissions.

Increase from the default limit to 5 TB

To increase a user’s OneDrive limit from the default of 1 TB to 5TB, it is a simple matter of running a PowerShell command, as documented in Change a specific user’s OneDrive storage space.

Set-SPOSite -Identity <user's OneDrive URL> -StorageQuota 5242880‬

The number 5,242,880‬ (5 TB) used for the storage quota must be precise.

The default value for the tenant itself can be changed as well, so that this first step isn’t necessary for new users. See Set the default storage space for OneDrive users for details. It should be noted that the maximum value that you can set as a default is 5120 (5TB). If it’s set any higher, it won’t be saved.

Increasing storage limit to 25 TB

Before you can set the limit any higher, you must first fill the OneDrive to 90% of its capacity, or 4.5 TB. This happened to me a few weeks ago and I started getting weekly “approaching your capacity” messages. At this point I opened a support ticket, and this is where the fun started.

I’ll spare you the back and forth email exchange, but a little snippet of the conversation went something like the following. Each line is an action or an email:

Me:          fills out form explicitly stating that I have hit 90% and need quota increased from 5 TB to 25 TB
Support: Please reply with a screenshot of the problem, and any troubleshooting steps
Me: I need my OneDrive quota increased from 5 to 25 TB
Support: You need OneDrive Plan 2 for that, and your OneDrive must be at 98% (both incorrect)
Me: I have E5, which includes unlimited storage (I ignored the 98% comment)
Support: What capacity is it at now?
Me: 91%
Support: Send the OneDrive URL
Me: sends OneDrive URL
Support: Your tenant may not have the Boost storage option enabled. Let me ask my supervisor to get that enabled.
Support (one day later): Would you like instructions?
Me: Yes please
Support: sends publicly available url listed above that increases quota with Powershell
Support: First change it to 10 TB, then change it to 25 TB (no idea where that came from)
Me: tries it, doesn't work for 10 or 25
Me: It didn't work
Support: Did you connect to the SharePoint Online module first? (seriously)
Me: Yes. This isn't my first rodeo. (I'm paraphrasing)
Support: Can you send a screenshot of your error message?
Me: There is no error message, the value simply does not save.
Me: This approach works up to 5 TB but not beyond
Support: Let me look further into this and get back to you.
Support: Your request had now been passed to our escalations team
Support (5 days later, different rep): We enabled boost storage. Can you try and let us know?
Me: Successful. Thank you.

I include the above partly because you might want the chuckle, but mostly to let you know not to give up in this. It’s advertised, and you paid for it.

The command that I used to enable this, once support had turned on the boost storage feature was:

Set-SPOSite -Identity <user's OneDrive URL> -StorageQuota 26214400

The number 26,214,400 (25 TB) must be used precisely.

In any event, after running the above command, my storage limit is now at 25 TB.

The particularly interesting thing to note here is that because the “Boost storage” feature is set at the tenant level, any other OneDrives in the tenant can have their limits increased without contacting support. All that is necessary is the PowerShell script above. However, the drive must still reach 90% capacity before it can be increased.

Should I hit the next limit, I’ll report back here.

1 Comment