CmdrKeene Tracker

Re: Scheduled Refresh 10x slower than on-demand?

CmdrKeene — Mon, 26 Aug 2024 14:06:08 GMT

To put it shortly, not really. Although I do think that most reports that use the data in my case were reflecting the query results by halfway through the refresh, I sort of gave up about the refresh showing progress for several more minutes along with adjusted some other scheduled report notifications to go out at different times to try to work around it. It still takes more minutes than a local refresh does, for sure.

Re: "Is in this day" filter

CmdrKeene — Tue, 31 Oct 2023 15:17:12 GMT

I did this and it looks correct if I make a visual to show my calendar table the only day with the measure=1 is today's date, but I can't move that measure into the filter pane in Power BI. What gives?

Re: Scheduled Refresh 10x slower than on-demand?

CmdrKeene — Fri, 06 Oct 2023 17:56:42 GMT

I totally understand that, but this is universal at any hour of the day, no matter what. And this is the only report our gateway and infrastructure supports. It's only the scheduled ones that are always without fail 14 minutes long.

Scheduled Refresh 10x slower than on-demand?

CmdrKeene — Fri, 06 Oct 2023 17:22:23 GMT

I have a dataset that is a very very optimal and fast query that returns data in about 5 seconds when I use PBI desktop.

On the service, when I refresh the data set on-demand, it refreshes in just a few seconds.

When the schedule refresh runs though, it takes 15 minutes every time.

Is there a reason to expect this? I have a process where a team wants updates on the hour, but this is complicating it.

Help with Running Total

CmdrKeene — Tue, 25 Jul 2023 19:12:30 GMT

I've reviewed several posts and this seems mostly the same as Solved: Re: DAX Question: Running Totals with Missing Valu... - Microsoft Fabric Community, but I can't seem to understand the formula well enough to adapt it.

Basically I have a running total of orders in each hour. It's in a single table (query result) that groups the results per hour and some other dimensions (obfuscated here but we can just say order type).

This is the same data as Stacked Bar and as matrix for comparison.

Notice the purple category (type 1) is present in hour 1 but missing from hours 2 and 3? But then it appears again in 4.

I want the running total to repeat the same total if it is unchanged, so that the chart only grows.

Basically the way Excel pivot chart would do it.

I don't really want to use a calendar table because then I'll need a literal table of every hour and that seems weird. Can I just modify the formula as Solved: Re: DAX Question: Running Totals with Missing Valu... - Microsoft Fabric Community did, perhaps by using unique order type from the same table?

Re: Help Managing Gateways

CmdrKeene — Thu, 06 Apr 2023 21:17:09 GMT

I could swear we originally did this (used "take over" when we added those two new machines) very recently. But maybe we used some other option.

I admin PowerBI service but can't touch the VM's the gateways run on myself. That top entry (the one I want to remove) is when I used to run the gateway on my workstation.

I'm going to ask our ops guys to try what you suggest (uninstall it from GatewayB, then set it up again and choose to take over the gateway that's currently primary). I'll report back how it goes.

Help Managing Gateways

CmdrKeene — Thu, 06 Apr 2023 19:21:12 GMT

I have a gateway cluster with 3 devices, but one no longer exists. Unfortunately it was the primary. What steps do I need to take to turn Gateway2 into the primary?

Re: SharePoint list query alternative or optimization

CmdrKeene — Tue, 20 Dec 2022 17:47:25 GMT

Are you just looking in the preview, or some other filter applied? Can you share your query?

Re: SharePoint list query alternative or optimization

CmdrKeene — Wed, 16 Nov 2022 16:50:05 GMT

Since The item count is accurate, that's not the number I get on the visuals... I think there must be a simple issue like a page or report-wide filter is at play, or perhaps the aggregation is set to count instead of sum (something like that nature).

However I want to also mention something I considered it could be -- this uses the ID number of the sharepoint records. If list entries ever get deleted from your sharepoint list, the ID numbers could be the issue.

Re: SharePoint list query alternative or optimization

CmdrKeene — Wed, 08 Jun 2022 14:35:02 GMT

I can't think of a great workaround for lookup field really. Is it possible to retreive the data and then use a merge or relationship to perform the lookup in the query?

Re: SharePoint list query alternative or optimization

CmdrKeene — Mon, 06 Jun 2022 21:30:14 GMT

That's really odd, for me all the text fields just show up. Is it maybe a lookup field on the front-end of SharePoint? Or you're sure it's either "single line of text" or "multiple lines of text" on the list itself?

Re: SharePoint list query alternative or optimization

CmdrKeene — Thu, 26 May 2022 15:24:02 GMT

I can try to poke around and see if I can find a solution. What type of field is missing?

I will mention that person fields did not come in in a format that was great for me, so in my power query I also pulled in the user information table and then used the user ID to relate to it.

Re: SharePoint list query alternative or optimization

CmdrKeene — Wed, 13 Apr 2022 16:48:29 GMT

Add a "Remove Duplicates" step to the end of your query, using the list item ID as the field to de-duplicate.

Re: SharePoint list query alternative or optimization

CmdrKeene — Wed, 16 Mar 2022 17:19:00 GMT

Great question. For the Author/Editor, those are really just references to another SharePoint table/list called "User Information List" that exists on all SharePoint sites. So for my purposes I retreive this list as well and then setup a relationship in my PowerBI report between the two.

SP Tickets (my main giant list with 200,000 records and growing)

SP UserMeta (just the User Information List with a custom name)

This could be handled in later steps of the query instead but I went this path.

Re: SharePoint list query alternative or optimization

CmdrKeene — Thu, 24 Feb 2022 17:32:45 GMT

Hello lcasey,

Yes that's basically the process. Create a new blank query, go to the Advanced Editor from the view toolbar, delete everything and paste in my code instead.

Re: Gateway Unreachable After Host Machine Reboots

CmdrKeene — Wed, 22 Sep 2021 20:02:25 GMT

I've got a case open, they had me send in my log files (not sure if it'll help).

I've seen other threads of users saying they have the same problem, so at this point I don't if what I'm facing is expected or not. I'd like the service to run when I'm not logged-in, but it does use my credentials to connect to data sources like SQL servers, SharePoint, and even file paths.

Should I consider setting it up in a virtual machine configured to automatically login my windows user account at boot up?

Re: Gateway Unreachable After Host Machine Reboots

CmdrKeene — Tue, 21 Sep 2021 13:13:41 GMT

Unfortunately it didn't help, our infrastructure team setup the gateway in a virtual machine but anytime the virtual machine reboots, same issue occurs.

Re: Gateway Unreachable After Host Machine Reboots

CmdrKeene — Mon, 20 Sep 2021 14:26:31 GMT

Unfortunately that did not help, the computer that runs the gateway restarted last night, and the datasets did not refresh (I received failure emails at midnight, 7am, and 9am). When I used remote desktop to logon to the computer that runs the gateway everything resumed syncing.

Re: Gateway Unreachable After Host Machine Reboots

CmdrKeene — Thu, 16 Sep 2021 16:29:30 GMT

Alright, I've changed it to be my own user account there, and it was successfully changed and I verified my datasets are still able to refresh. Let me monitor for what happens next time I reboot the machine where the gateway is installed.

Re: Gateway Unreachable After Host Machine Reboots

CmdrKeene — Wed, 15 Sep 2021 14:40:36 GMT

Thanks for the suggestion. I see the On-Prem Data Gateway Service is currently set to login as NT Service\PBIEgwService. Should I change this to be my own user account? I am a local administrator on this machine.

Sidenote: While the issue was occuring (after the machine running the gateway rebooted), I made some notes from another machine.

The computer hosting the gateway is online, booted, and the on-prem gateway service is running (see screenshot for the commands used to check on the remote system). But until I used Remote Desktop to actually loggin to "GW" machine, the datasets in PBI service would not refresh.