User Profile
WhiteBusLady
Frequent Visitor
Joined 7 years ago
User Widgets
Contributions
Re: No AlwaysOn server
This has evolved.... The current situation is that I DO have a gateway. I have placed a standard gateway on a hosted cloud server. However, that is not where initial development happens. I do all my development on a laptop that is only online occasionally. I installed a personal gateway on the laptop for test purposes, but obviously it cannot be used for production because is it not usually online. So here is the problem: I develop offline. When I am satisfied with my results I post the completed .pbix file on OneDrive, where the most current shared data files live. At that time I have to manually change all the Data Source connections from connection strings that are appropriate from the perspective of my laptop, to connection strings that are appropriate for the perspective of the online server with the standard gateway. Changing the connections manually is both risky and time-consuming. How can I utilize some sort of parameter, variable, or other method to consistently change the connections every time I post a new or updated .pbix file from my offline test system to the online production system? (Notice there is no network. My laptop is not connected to the online server, except occasionally through Remote Desktop Program. The report consumers are likewise using independent laptops. We all work for different companies. Office 365 is supposed to function as a faux "network" for us.) Comment: I'm still wondering what I'm missing. It can't be this hard, lots of people develop in places other than their production server. I'm used to SQL Server SSIS, which is often overly complex. I do now have a gateway. But it is not really on-premise, rather it is in-the-cloud. Is that a major problem? Isn't that supposed to be a good thing, not a bad thing <grin>?5.5KViews0likes0CommentsRe: No Gateway
WhiteBusLady again: I just realized PBI may not let you have multiple schedules per report. SQLServer does. But if you only need to update once per day my suggestion may still help. I got lucky, my users like the reports so well they agreed to pay for a cloud server. We have other uses for it also, and this pushed them over the edge. But Microsoft licensing is expensive. No one answered one of my questions: Is there a Mac version of the gateway? More important, is there a LINUX version?2.6KViews0likes0CommentsRe: No Gateway
Thanks for the empathy! And a possible small solution for you regarding your statement " There is no option for "weekday" refreshes, just daily". This isn't true. I'm still in Trial and Error learning stage, so it took me a while to realize this. The Schedule Refresh settings in the Service work like SQL Server schedule settings. If you select Weekly, you can then check individual days of the week. For a given dataset, click Schedule Refresh. Turn it on. Immediately below the On button is the Refresh Frequency dropdown. Set it to Weekly. Checkboxes for each day of the week appear. Uncheck Saturday, Sunday. If you need 4 times per day, set up 4 schedules, one for 7am, one for noon, .... Set each one to run the Weekly schedule, Mon-Fri. But one will run at 7am, another at noon, etc.2.6KViews0likes1CommentRe: No AlwaysOn server
Your 2nd paragraph applies. And I am now spending money for an always-on online server. My subject should now say "no LOCAL Always-On server". Now that I have "rented" a hosted cloud server, how do I use it? Below is an outline of what I have done. All is mostly working except the final step. I need the details for Step #6. Gather data, build model(s), set up online (shared) data collection process based on Excel and SQLServer. (Maintain Excel files in shared OneDrive folders, SQL Server on web server.) Using local copies of sample data and PowerBI Desktop, work offline to develop datasets and reports. (Store .pbix file in shared/sync'd OneDrive folder that remains accessible offline.) Publish reports to PowerBI Service Add Standard PowerBI Gateway to web server. Schedule twice-daily refreshes of SQLServer data. Schedule Excel/OneDrive data refreshes (and figure out why hourly refreshes are not working - will address that separately) Change the published report Data Sources from local offline files/databases to the online, dynamically updating Data Sources I need a simple method for promoting reports from Desktop testing to PBIService testing to PBIService production. In SQLServer SSIS I would set parameters for the Data Source Connection Strings and use SSIS Environments and Events to trigger the proper settings for each phase. I do NOT want to have to open up an already-tested report and manually change connectors. Too error-prone. There needs to be a consistent hands-off method. I'm sure I'm not the only one insisting on this, I just can't find the right guidance on how to approach it. Variables? Parameters? Triggered by what? Or a different kind of universal Connector rather than Excel and SQL Server? Thanks for any guidance you can give me!5.5KViews0likes2CommentsRe: Missing column when refreshing
First thing I always ask is "WHO touched my report?" If no one answers, check the data. Finally, check the queries. For Excel data, the most common error I might expect for this errror is an unexpected comma. Maybe a number entered with commas (1,000) when that isn't supposed to be allowed. Or a comma in the middle of a first or last name without quote marks surrounding it. OR a missing comma. Especially at the top of a data set. The commas control the column count. As a long time data developer, if I'm reasonably certain no one has touched the program/report, then I assume a data problem and am usually right. Especially if these spreadsheets are created by hand or can be edited by hand after being exported by some program. If there is any chance some has opened the report in Power BI Desktop and looked at the queries, then you can use Edit Queries to look through the Applied Steps for column lists. You don't need to know the syntax to recognize a list of column names and determine that there is an extra one or that one is missing.12KViews0likes1CommentNo AlwaysOn server
This is an extension of 02/09/2019 post "No Gateway" I'm trying to provide dynamic PBI reports for a small group of people without an intranet. There is one developer (myself) who manages SQLServer data and creates PBI datasets and reports from both SQLS and Excel spreadsheets maintained in a shared OneDrive account. There are three people who contribute new and/or updated Excel worksheets, and use the PBI reports. The four of us work for different companies, are geographically separated, and use a mix of Windows and Mac laptops, Android and iOS phones, etc. None of us have local networks. None of us have a local always-on machine. We use Office365 and OneDriveForBusiness to work together. Our shared data creation/maintenance is working. PBI reports have been a hit. I develop the reports in PBI Desktop while working mostly offline. I don't usually need live data for design. I can work off the local copies of the OneDrive files, and a local backup of the online SQLS database. I keep my pbix files in a OneDrive folder, and eventually publish them to PBI Service. My scheduled refreshes work through a personal PBI gateway when my laptop is on and awake and connected to the internet. Which isn't very often. That is a real problem. We read that it is super easy to base dynamic reports on OneDrive files because OneDrive and PBI Service are both online (and both controlled by Microsoft, as is Office365). But even though all data and the .pbix files are online, the data still seems to need to go through a PBI gateway on my laptop. I have now set up OneDrive and a standard PBI gateway on the [virtual hosted cloud always-on] online server. But my .pbix files are created on my laptop using laptop copies of the OneDrive data. Do I now have to put PBI Desktop on the server, manually open every OneDrive pbix file and change the report data sources to addresses matching the server perspective/file structure? Do I have to do this every single time I want to publish an enhancement or correction to each report file? This is getting way too complex. I have 1) four people with laptops; 2) a shared OneDrive account for all data and pbix files; and 3) a cloud web server with SQLServer data and a standard PBI gateway. What is the simple way to connect published reports with updated data under this scenario? The key: without going through a laptop for ongoing data refreshes If I was developing in PBI desktop on the cloud server all would be fine. But I'm not. I'm developing off-line. No choice, several months of the year I'm in the mountains where I must drive out to get internet. And I wouldn't develop on a Production server anyway.5.6KViews0likes4CommentsRe: No Gateway
Thank you for the reply. I had to do some reading.... Superficially, this makes sense if you have a Windows machine that is always online. I am developing the reports on a Windows laptop, but my presence is temporary. I will be moving on to other projects. My client has 3 PBI users geographically separated, no internal network, all using Macs. They interface with one of their large clients using Office365. The plan was to use OneDrive and PBI to share daily/weekly reports. I read that the Gateway runs on Windows. What about a Mac-based Gateway? If I was logging in with an Apple machine would I be offered a Mac version? I downloaded the Gateway softeware and need to do some experimenting before I mark this as solved. Will be back....2.9KViews0likes4CommentsNo Gateway
After all I've read about how simple OneDrive hourly refreshes are, and all I've learned about self-service PowerQuery data prep with calculated columns and measures, I'm horrified to finally realize I cannot do simple data updates to my fancy reports without a Gateway! My client has 3 principals, working from separate coasts. All company data flows to shared OneDrive folders. There is no local network. No server. No always-on computer. Isn't that the point of Office 365 and OneDrive? Is there no way to do a simple daily data update without a server-based Gateway? What am I missing? Is there some way to script something that will accomplish the same thing?Solved2.9KViews0likes6Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.