Rockford Lhotka's Blog

Home | | CSLA .NET

 Wednesday, March 28, 2018

Power BI is a pretty amazing tool, even for data analysis novices like me. The ability to take data from an Excel spreadsheet and create useful and compelling dashboards as a power user is really wonderful!

However, I don't create such a dashboard all that often, and I find that each time I do it I fall over one particular issue: how to connect my PBI report to a dataset that can auto-refresh.

My dataset is an Excel file in O365 SharePoint (actually in a folder/site managed by Microsoft Teams). In theory it is easy to have PBI connect to such a spreadsheet, and when the report is published to PBI in the web it can be set to auto-refresh the data daily, weekly, etc.

In practice however, this is fraught with peril. The instructions commonly available from Microsoft and other blogs leave out a couple key issues that (I find at least) derail the process and lead to hours of frustration.

I use Power BI Desktop to create my report. So step 1 is to connect to the Excel file hosted in O365 SharePoint. And it is step 1 where things go wrong, because there are so many different ways to connect to a spreadsheet, and so many URL variations for a spreadsheet hosted in SharePoint - most of which won't actually work in the end.

But what's worse, is that all these myriad variations work from Power BI Desktop, so you are fooled into thinking all is well. When in reality, after you publish the report to PBI in the web it will fail! Very frustrating!

My colleague Scott Diehl and I spent some time this afternoon sorting through what works and what only appears to work, and I am documenting it in this blog post so next time I need to create a dashboard I'll have a place to find the answer without re-learning it.

So here's the thing. I have this spreadsheet in Teams.

And if I click on the "Open in SharePoint" link I can see it in O365 SharePoint via the browser.

If I click the "Copy Link" button here I get a link to the file, such as:

And you can use that link to add a dataset to PBI Desktop and it work great.


So instead what you must do is open the spreadsheet in actual desktop Excel. Not in Excel Online, not in O365 SharePoint, but in actual Excel.

Then go to the File tab in the ribbon and you should see something like this.

Notice the text I've highlighted. Left-click on this and choose "Copy Link to Clipboard". The result is a URL like this:

This is just a different URL to the same exact file, but this URL is one that PBI Web will actually accept.

Note: You do need to remove the ?web=1 bit from the end of the URL before using this to create your dataset in PBI Desktop.

From here you can create your report and dashboard, and publish to PBI Web. Then in PBI Web you can edit the dataset to provide PBI Web with your organization credentials, and to set up a refresh schedule for the data:

One final note. If you do what I did, and create your report and dashboard using the first style of URL that works in Desktop but not Web, you are not totally out of luck. In that case, follow the above steps to get the functional URL from Excel, and then in PBI Desktop you can edit the dataset's source.


That'll allow you to replace the broken URL with the working URL, without having to recreate your entire report or lose your work.

Wednesday, March 28, 2018 1:27:54 PM (Central Standard Time, UTC-06:00)  #    Disclaimer
 Saturday, March 24, 2018

tl;dr: We're just starting on the biggest revolution in smart client and web client development technology in many, many, many years. Now is an extremely exciting time to be a smart client developer, or to rediscover smart client development if you (like me) have been hiding in server-side code over the past decade or so. WebAssembly is the technology that may reshape the way we build client-side software, and it is really cool!

I've been a smart client developer since 1991 when I first started exploring how to build apps on my Amiga and on Windows 3.1. Prior to that point I was purely focused on building minicomputer apps that used VT100 terminals to interact with the user. And I must confess that (after a long and painful learning curve) I completely fell in love with the idea of making full use of the powerful computing devices sitting on my users' desks, and later laps, and these days that they hold in their hands.

But it certainly hasn't been a smooth ride from 1991 to 2018! Our industry tends to engage in wide pendulum-like swings in terms of technology. So I went from terminals to PCs to browsers-as-terminals to mobile to browsers-as-smart-clients to a major resurgence in command line windows (bash and PowerShell) - which brings us to now.

I've noticed over all this time though, that end users prefer a smart, interactive, and responsive experience. They never liked terminals, and I think we all agree that the "classic web" was even worse than using a 3270 or VT terminal for the most part. Yuck!

So while there've been these pendulum swings, the overall gravity of the changes have focused on how to build smart clients that make users happy, while trying to achieve the zero-friction deployment models that make terminals and browsers so popular.

For a brief, shining moment in 2007-2009 we had a vision of what "could be" via Silverlight. Zero friction web deployment to give the user a full-blown smart client experience based on mature and modern developer tooling, including unit testing and all the other goodies we rely on to make quality software.

Sadly Apple and Microsoft managed to derail that technology, leading to us spending a decade on a different approach: single page apps (SPAs). A valiant attempt to use web client technologies (html, css, JavaScript) to build comparable smart client productivity and capabilities to what was created by Visual Basic and PowerBuilder in the 1990's, and then carried through the 00's via .NET with Windows Forms and WPF.

I'm sure there are a lot of lessons to be learned by looking at what's transpired over the past decade in this regard. One conclusion I take from it, is that without some organization devoted strongly to creating all the tooling necessary to build a smart client app development environment, this stuff is nearly impossible.

To be clear, what I'm saying is that Visual Basic went from a toy in 1991 to the dominant way of building apps by 1997. Beyond that, by 1997 it was pure joy to build Windows apps, because the tooling was highly productive, very stable, and we then got to enjoy a stable platform from 1997-2002. Think about that - learn your tools and just be productive for FIVE YEARS!!

Today the typical web development experience using Angular and TypeScript (the dominant players in the space) generally seems to revolve around getting four DAYS of productivity, and then spending a day troubleshooting why the dev pipeline broke because of some npm package that got changed somewhere in the world.

My personal guess as to why web client development remains so fragile is that it is "owned" by hundreds of individuals and companies, all of whom do whatever the f-ck they want, when they want - and we just sit on top of that quivering mass of software and try to build multi-million dollar enterprise apps. Knowing that even if we get them built and deployed, that they'll never be stable without continual work to accommodate the random changes constantly occurring to the underlying muck on which we've built that software.

If you are a consultant who charges by the hour there's no better world in which to live. The money will never stop flowing, and you'll never have to worry about finding work, because at the very least you'll always have a job dealing with these random changes to the dozens or hundreds of JavaScript library dependencies used to build every app.

If you are a business decision maker or leader in IT, you are probably just starting to realize what a nightmare you've stepped into, and will quickly be trying to figure out how to escape the world of web client development. Hopefully with a little dignity, if not your job.

Yeah, I know. At this point some of you are probably thinking that this is how it has always been. You have no memory of building apps with VB6, and then Windows Forms, and then WPF. All platforms that evolve, but don't break on a weekly basis. Believe me though - there really was a time when software could be built, tested, deployed, and then it would actually work for months or even years with little to no maintenance beyond enhancements and other things that provided real business value.

Perhaps I'm just impatient. Maybe the web client world will eventually stabilize and become productive at the level of the technologies we had through the 90's and 00's. Maybe it just takes 20 years instead of 10?

Or maybe we need another vector for innovation.

I think that vector is just now arriving on the scene and I'm very excited!

WebAssembly is now available (or about to be available) in all major browsers. It is now in Firefox, Chrome, and Edge. Safari is lagging, but Apple will get with the modern world very soon.

You know how browsers (used to) run html, css, and JavaScript?

Now they also run web assembly code.

At a very basic level all this means is that JavaScript is no longer the only programming language for the web. And thanks to the Canvas and OpenGL support provided by HTML5, it is true that html/css aren't even the only ways to create the visual elements for end users.

For many years now people have been trying to figure out ways to "escape" JavaScript. Mostly by creating other languages that transpile or even compile into JavaScript. One of the most popular of these is TypeScript, which is a true superset of JavaScript, with a bunch of extremely nice features that transpile into JavaScript before the code is run on the browser. But in all these cases the new language is limited by how it can be converted into JavaScript.

WebAssembly (wasm) offers an alternative. The wasm engine is nestled in the browser right alongside the JavaScript engine, really filling the same niche: running code in the browser. Ultimately this means developers can choose to develop in JavaScript or something that compiles into JavaScript, or they can develop in any other language that can compile to wasm.

Today C, C++, Go, Rust, and probably other languages have wasm compilers. Other languages with compilers and runtimes built using, let's say C, have subsequently been built for WebAssembly too - Python being a good example. The Xamarin team at Microsoft took the open source mono implementation of .NET and built that for WebAssembly, so now .NET (C# and F#) is available in the browser.

My personal background is very Microsoft-focused. But WebAssembly isn't a Microsoft thing. It is a standards-based initiative led by the Mozilla Foundation:

No longer are smart client developers stuck only with JavaScript. We can now escape the JavaScript hegemony and use nearly any other language!

You might still choose to use html/css, but with C or Go or C# instead of JavaScript as your programming language. One example of this is the Blazor project. In many cases you'll probably see performance benefits, because those languages have better compiler optimizations, and because wasm runs faster than JavaScript in most cases. Beyond which, many of these other languages have more robust tooling for development, testing, DevOps, and more.

What is interesting is that other UI technologies now compete with html/css in the browser. Again, the HTML5 Canvas and OpenGL support means that pretty much anything can be rendered in the browser window. As an example, the Ooui project has Xamarin Forms (XAML) running in the browser on top of the mono .NET implementation.

It is this last bit that has some folks on twitter comparing WebAssembly to Microsoft Silverlight from 2007. But that's a very incomplete and misleading comparison.

WebAssembly represents something much bigger and more powerful than Silverlight: the use of many different programming languages and UI frameworks in the browser as alternatives to the previous html/css/JavaScript monoculture. We can now think of the browser as a true client-side target for smart client development on a par with Windows, macOS, Android, and iOS.

The fact that Microsoft and the open source community have been able to get .NET, ASP.NET Razor, and Xamarin Forms running in WebAssembly are merely illustrations of just how powerful wasm is today, and I think are just a teaser for the innovation and exciting stuff we'll see in the future!

And yes, I do think this will directly allow us to recapture the smart client development productivity and stability we enjoyed for a couple decades when building software using VB and .NET.

Saturday, March 24, 2018 4:26:54 PM (Central Standard Time, UTC-06:00)  #    Disclaimer
 Thursday, March 22, 2018

I am pleased to announce the release of CSLA .NET version 4.7.100 with full support for netstandard 2.0 and .NET Core 2.

The packages are now in NuGet. Once some final updates to the samples are complete and merged into master I'll create a formal release tag/page on GitHub.

This release also includes some other very exciting capabilities, including:

  • #760 Add support for ASP.NET Core 2.0
  • #759 Add support for EF Core (EntityFrameworkCore)
  • #813 Major performance improvement when data binding to large object graphs thanks to keithdv
  • #795 Add Transactional attribute back into netstandard 2.0 code
  • #800 Changes to configuration so it is possible to configure CSLA without any web.config or app.config files (such as in .NET Core, etc.)
  • #496 Support ClaimsPrincipal via new CslaClaimsPrincipal type
  • #729 ApplicationContext now defaults to using AsyncLocal to maintain values on the current thread/context with help from j055 BREAKING CHANGE
  • #712 Support in-place deserialization of an object graph
  • #748 Major improvements to serialization via MobileFormatter thanks to jasonbock
  • #763 Update to samples thanks to tfreitasleal
  • #688 Get ApplicationContext.User authentication working with ASP.NET Core thanks to dazinator
  • #766 Update to use latest UWP libraries for Windows 10 Fall Creators Update BREAKING CHANGE
  • #790 BUG: Fix AmbiguousMatchException in data portal thanks to iherwald
  • #710 BUG: Fix ambiguous Save method issue thanks to rabidkitten

There are a couple known issues with this release:

  • #818 Xamarin projects using System.Data.SqlClient show a warning about this assembly's location
  • #794 UWP projects show warning PRI263: 0xdef01051 messages relative to CSLA resource strings
  • #822 There are two "sets" of CSLA packages/assemblies: one for full .NET 4.6.1+ and one for netstandard (including Xamarin, UWP, .NET Core, etc.) due to a type error between netstandard and full .NET
  • #703 Though netstandard supports BinaryFormatter, that is currently not an option from the CSLA configuration, and this needs to be addressed

Regarding the NuGet/assembly split noted in #822:

Right now there are two "families" of CSLA .NET packages in NuGet. One that supports full .NET and one that supports all other runtimes.

The full .NET family must be used for the following types of app:

  • Windows Forms
  • WPF
  • ASP.NET (other than Core)
  • Console apps (other than Core)
  • Azure Functions
  • Any other runtime hosted by full .NET 4.6.1+

The netstandard family must be used for the following types of app:

  • Xamrin
  • UWP
  • .NET Core
  • ASP.NET Core
  • Any other runtime not hosted by full .NET 4.6.1+

What this means for you is that if your n-tier app is 100% full .NET or full netstandard then you can live within one of those families. BUT if your server is full .NET and your client is Xamarin (for example) then your business library assemblies need to be compiled twice: once for full .NET and once for netstandard.

The Samples\ProjectTracker app shows how this is done by using a Shared Project to contain the code, and two concrete class library projects that compile the code for full .NET and netstandard respectively.

Thursday, March 22, 2018 1:38:26 PM (Central Standard Time, UTC-06:00)  #    Disclaimer
 Thursday, March 15, 2018

There's some really wonderful work happening now that mono runs in webassembly. There's the Blazor project, and perhaps more exciting is an initiative that has Xamarin Forms running in webassembly.

Blazor sits on mono in webassembly and provides a Razor style UI framework. You write your .NET (C#) code to run behind an html/css UI created using Razor syntax.

Frank A. Krueger just published a different approach, where Xamarin Forms runs in the browser via webassembly, so your Xamarin Forms app (XAML + C#) runs on the client inside the browser. If you follow the above link, you'll find a nice walkthrough on how to use the dotnet command line to create a simple project and run it in a browser.

I started there, and thought it would be fun to try a more robust Xamarin Forms project using Visual Studio, XAML and code-behind. Pretty much my standard approach when building apps for iOS or Android.

Here are my steps (and the code is in GitHub as XFOouiDemo):

  1. Run through Frank's walkthrough - in my case on Ubuntu in Windows 10 WSL (Windows Subsystem for Linux), where I have dotnet installed, and of course Linux already has python - the result is that you should have XF running in your browser with a simple button that counts clicks
  2. Put the walkthrough directory on /mnt/c somewhere so the files are available to Windows as well as Linux - my path is /mnt/c/src, which is c:\src on the Windows side
  3. Open the csproj file in Visual Studio
  4. Add a NuGet reference to Xamarin.Forms (at this point you won't be able to do a dotnet build on Linux because XF apparently doesn't yet work correctly with .NET Core)
  5. Build the project - or maybe just restore NuGet packages
  6. Close and re-open VS (I found this stabilized things a lot)
  7. Add a new item to the project - specifically a Xamarin Forms ContentPage
  8. Edit the xaml to show your content, or not, as you choose
  9. Edit the Program.cs file as shown below
  10. Build the project in Visual Studio
  11. Switch back to your WSL window and run python to rehost your new code
  12. Again navigate to http://localhost:8000 in your browser to load your updated app, and you should see your new XAML-based content

The updated program.cs file is this:

using System;
using Ooui;
using Xamarin.Forms;

namespace MyFormsApp
  class Program
    static void Main(string[] args)
      // Initialize Xamarin.Forms

      // Create the UI
      var page = new Page1();

      // Publish a root element to be displayed
      UI.Publish("/", page.GetOouiElement());

Basically just replace all the manual creation of controls with an instance of your XAML page (in my case Page1.xaml).

What might bend your brain a little here, is that it might appear that I'm compiling the code on Windows and running it on Linux. But that is not what's happening. Remember that the code is running in your browser, not on Windows OR on Linux. So it doesn't matter whether you compile on Linux or Windows, the output of the compilation is wasm code that's downloaded to the browser where it is executed.

Thursday, March 15, 2018 6:58:04 PM (Central Standard Time, UTC-06:00)  #    Disclaimer
On this page....
Feed your aggregator (RSS 2.0)
April, 2019 (2)
January, 2019 (1)
December, 2018 (1)
November, 2018 (1)
October, 2018 (1)
September, 2018 (3)
August, 2018 (3)
June, 2018 (4)
May, 2018 (1)
April, 2018 (3)
March, 2018 (4)
December, 2017 (1)
November, 2017 (2)
October, 2017 (1)
September, 2017 (3)
August, 2017 (1)
July, 2017 (1)
June, 2017 (1)
May, 2017 (1)
April, 2017 (2)
March, 2017 (1)
February, 2017 (2)
January, 2017 (2)
December, 2016 (5)
November, 2016 (2)
August, 2016 (4)
July, 2016 (2)
June, 2016 (4)
May, 2016 (3)
April, 2016 (4)
March, 2016 (1)
February, 2016 (7)
January, 2016 (4)
December, 2015 (4)
November, 2015 (2)
October, 2015 (2)
September, 2015 (3)
August, 2015 (3)
July, 2015 (2)
June, 2015 (2)
May, 2015 (1)
February, 2015 (1)
January, 2015 (1)
October, 2014 (1)
August, 2014 (2)
July, 2014 (3)
June, 2014 (4)
May, 2014 (2)
April, 2014 (6)
March, 2014 (4)
February, 2014 (4)
January, 2014 (2)
December, 2013 (3)
October, 2013 (3)
August, 2013 (5)
July, 2013 (2)
May, 2013 (3)
April, 2013 (2)
March, 2013 (3)
February, 2013 (7)
January, 2013 (4)
December, 2012 (3)
November, 2012 (3)
October, 2012 (7)
September, 2012 (1)
August, 2012 (4)
July, 2012 (3)
June, 2012 (5)
May, 2012 (4)
April, 2012 (6)
March, 2012 (10)
February, 2012 (2)
January, 2012 (2)
December, 2011 (4)
November, 2011 (6)
October, 2011 (14)
September, 2011 (5)
August, 2011 (3)
June, 2011 (2)
May, 2011 (1)
April, 2011 (3)
March, 2011 (6)
February, 2011 (3)
January, 2011 (6)
December, 2010 (3)
November, 2010 (8)
October, 2010 (6)
September, 2010 (6)
August, 2010 (7)
July, 2010 (8)
June, 2010 (6)
May, 2010 (8)
April, 2010 (13)
March, 2010 (7)
February, 2010 (5)
January, 2010 (9)
December, 2009 (6)
November, 2009 (8)
October, 2009 (11)
September, 2009 (5)
August, 2009 (5)
July, 2009 (10)
June, 2009 (5)
May, 2009 (7)
April, 2009 (7)
March, 2009 (11)
February, 2009 (6)
January, 2009 (9)
December, 2008 (5)
November, 2008 (4)
October, 2008 (7)
September, 2008 (8)
August, 2008 (11)
July, 2008 (11)
June, 2008 (10)
May, 2008 (6)
April, 2008 (8)
March, 2008 (9)
February, 2008 (6)
January, 2008 (6)
December, 2007 (6)
November, 2007 (9)
October, 2007 (7)
September, 2007 (5)
August, 2007 (8)
July, 2007 (6)
June, 2007 (8)
May, 2007 (7)
April, 2007 (9)
March, 2007 (8)
February, 2007 (5)
January, 2007 (9)
December, 2006 (4)
November, 2006 (3)
October, 2006 (4)
September, 2006 (9)
August, 2006 (4)
July, 2006 (9)
June, 2006 (4)
May, 2006 (10)
April, 2006 (4)
March, 2006 (11)
February, 2006 (3)
January, 2006 (13)
December, 2005 (6)
November, 2005 (7)
October, 2005 (4)
September, 2005 (9)
August, 2005 (6)
July, 2005 (7)
June, 2005 (5)
May, 2005 (4)
April, 2005 (7)
March, 2005 (16)
February, 2005 (17)
January, 2005 (17)
December, 2004 (13)
November, 2004 (7)
October, 2004 (14)
September, 2004 (11)
August, 2004 (7)
July, 2004 (3)
June, 2004 (6)
May, 2004 (3)
April, 2004 (2)
March, 2004 (1)
February, 2004 (5)

Powered by: newtelligence dasBlog 2.0.7226.0

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2019, Marimer LLC

Send mail to the author(s) E-mail

Sign In