Slides and Demo for Introduction to DAX

Power Point for Introduction to DAX

https://github.com/IkeEllis/democode/blob/master/IntroToDAX/Power%20BI%20Introduction%20to%20DAX.pbix

Wish List for Power BI

Here are the things I wish were changed about the Power BI and Power Apps suite of services.  I've broken it down by product.  I've included the date I added it to the list and the date it was corrected by the team.

Power BI Desktop
Taskbar Icon:  Added ~ 6/9/2018
When I update Power BI Desktop, it loses the taskbar icon and it turns white.  I'd like the taskbar icon to be preserved even after an update.  This is particularly annoying because of the aggressive update cycle of Power BI Desktop.

Feature parity with Power BI Service and the on-premise Power BI Report Server: Added ~ 6/9/2018
I have to use two different versions of Power BI Desktop, one for the portal and one for PBIRS.  I'd like greater conformity so I only have to use one tool for either deployment target.

Switching from Template Driven, to Reactive or Model Driven Forms

When we started writing Angular 2 apps, we had come from an AngularJS background. So of course our first forms were template driven. All we had to change from AngularJS was ng-model to ngModel and put it in a banana box. As long as the form's validation remains simple, Template driven forms are probably the way to go primarily due to their simplicity. However as the complexity of the forms grows, especially the validation of the form, its readability and even feasibility go bad pretty quickly. The only other downside to template driven forms is that they can't be unit tested. In our company most of these sorts of things were tested at an end-to-end level using Gherkin anyway, so less of an issue for us. But again as the complexity grows you might need to start unit testing those edge cases.

So let's take a relatively straightforward concrete example where the validation requirements might force us into implementing them in Reactive or Model-Driven forms. One side note about the nomenclature, we try to use Model Driven in our company, because we have React projects as well, and things can get pretty confusing distinguishing between a React form and a Reactive form. The example we are going to look at is a change password form. The validation requirements are that the passwords are strong, and that they match.

First let's take a look at the Template Driven HTML (change-password.component.html)

  

    
Change Password
  
  

    
{{error}}
  
  

    

      

        Old Password
        
      
    
    

      

        New Password
        
      
    
    

      

        Confirm New Password
        
      
    
    

      
    
  

And here is the TypeScript (change-password.component.ts)

export class ChangePasswordComponent {
  formErrors: string[];

  oldPassword: string;
  newPassword: string;
  confirmNewPassword: string;

  constructor() { }

  changePassword() {
    // submit to server
    if (this.newPassword!==this.confirmNewPassword){
      this.formErrors=["Passwords don't match"];
    } else {
      this.formErrors=[];
    }
  }
}

So far no validation. It *is* possible to write template validation using directives, but it is much simpler using Model-driven forms. To convert over the first thing we need to remember is to add the ReactiveFormsModule to your module (app.module.ts)

import { FormsModule, ReactiveFormsModule } from '@angular/forms';

and

imports:[
 BrowserModule, FormsModule, ReactiveFormsModule

Then at the top of your component (change-password.component.ts) you will need to add:

import { FormBuilder, FormGroup } from '@angular/forms';

In the fields section of your component replace

  oldPassword: string;
  newPassword: string;
  confirmNewPassword: string;

with

  form: FormGroup;

Lastly, change the constructor to this:

constructor (protected formBuilder: FormBuilder) {
  this.form=formBuilder.group({
    oldPassword:[''],
    newPassword:[''],
    confirmNewPassword:['']
  });
}

So basically there is one field where there use to be three, but the constructor was expanded to initialize the form with those three values. Now let's change the HTML (change-password.component.html). First find the form element, and change

to

Lastly, change all banana in a boxed ngModels, e.g. [(ngModel)] to formControlName. Here is an example change:

to

After doing that for all 3 fields. Voila! it is converted.

In Part 2 I will talk about the validation.

PS: For some reason Blogger and/or SyntaxHighlighter hate Angular code, it might be easier to read this over on my BrainHz blog

Power BI Tip: How to stay logged in to multiple powerbi.com accounts at the same time.

I have a great tip today!

 As a consultant, I find it difficult to switch between accounts on PowerBI.com.

 I have to log out of an existing account and log back in to a new account. The login process takes a long time. I have found a work around. I use google chrome to manage different chrome accounts, different themes, different cookies, and this allows me to stay logged in to multiple power bi accounts at the same time.

 1. In Chrome, click the title bar on the upper-right corner of the screen.

 You'll see your name, probably:

 

 2. Click on your name and pull down the menu. Click Manage People.

 3. Add different names for each of the Power BI accounts that you manage. I start mine with "Client - " and the name of the company, just so they're all grouped together.

 Now, each time you click on a profile, you will open a new chrome window. That profile will have different cookies, settings, bookmarks, and themes. I use themes to tell them all apart from each other. I use bookmarks to keep VPN logins, JIRA boards, TFS, and Azure logins all separate from one another.

 Here, I made a video on this for you:



 Hope this helps you!

 Ike

Power BI - Creating a playable scatterchart like Hans Rosling

I created seven quick youtube videos on how to create a playable scatterchart like the one Hans Rosling created in his famous Ted talk.

Webpack vs SystemJS

Angular 2.0 finally released on September 15th. We started a new project in early October, so we decided to try it out. Pretty quickly the question came up, which module loader should we use for the new application?

The Angular 2.0 tutorials use SystemJS, except for a few pages referencing Webpack. So we started leaning towards SystemJS. Then I came across a webpack article in the Angular documentation: In it is says:

It's an excellent alternative to the SystemJS approach we use throughout the documentation

Well, if it is such an "excellent alternative" why wasn't it used in the documentation instead of SystemJS itself?

I also found this on Stack overflow.

Webpack is a flexible module bundler. This means that it goes further [edit: than SystemJS] and doesn't only handle modules but also provides a way to package your application (concat files, uglify files, ...). It also provides a dev server with load reload for development.

SystemJS and Webpack are different but with SystemJS, you still have work to do (with Gulp or SystemJS builder for example) to package your Angular2 application for production.

So Webpack can do more, point for Webpack.

And then I found this article

Angular 2 CLI moves from SystemJS to Webpack

Google itself is now using webpack? Game over, webpack wins.

Training Videos on the Redgate Developer Bundle

These videos will train you on every product in the Redgate Developer bundle, including SQL Prompt, SQL Source Control, SQL Data Generator, SQL Schema Compare, SQL Doc, SQL Data Compare, and SQL Search.

Slides from my SQL in the City presentation

11 Goals of High Functioning SQL Developers from Ike Ellis

Top 5 Things You'll Learn From my PASS Summit Session

I don't want to give everything away, but if you come to my PASS Summit session, you'll learn and see demos explaining the following five bullets:

 1) When to use a JSON document store and when to use a relational store.

 2) When you need to use Azure Table Storage, and when you should use something else.

 3) You'll see demos for DocumentDB, Azure Table Storage, and Azure SQL Database.

 4) You'll see when to use Azure SQL Database or SQL on an Azure VM or both.

 5) You'll see Azure SQL Warehouse and why it's a unique data storage offering.

 Most importantly, at the end of this session, you'll understand your Azure data storage choices and why they can each play a pivotal role in your data architecture.

SSMS: Query Shortcuts

No reason to type out SELECT TOP 1000 * FROM or SELECT COUNT(*) FROM anymore

Crafting Bytes announces partnership with RedGate Software

Database Lifecycle Management 

Crafting Bytes is proud to announce a special partnership with RedGate software.

http://blog.red-gate.com/redgate-partners-alm-experts/

People have been talking about Database Lifecycle Management (DLM) for a while. It’s all about extending existing ALM practices like source control, continuous integration, and automated deployments to the database.

 Now I’m going to be talking about it in Philadelphia, San Diego, and Baton Rouge. Specifically, I’ll be running training workshops for Redgate on two aspects of DLM: Database Source Control and Automated Database Deployment. I’m really looking forward to this because Redgate have an amazing DLM solution that solves real problems bringing significant advantages to SQL Server professionals.

Want to know more? Visit Redgate’s workshop pages As a Redgate certified partner, I’m also happy to talk with you about your DLM needs. Feel free to contact me at contact@craftingbytes.com.

Resource Sharing in Windows Universal Apps

CraftingBytes recently took on a Windows Universal project. As with any multiple device project one of the goals is to share as much as possible to avoid writing the same code twice.

The is no conditional compile in XAML, so that means that separate XAML files are needed in the cases where complete sharing is not possible. Luckily the Windows Universal project structure is set up so that all you need to do to share a XAML file is move the file into the Shared folder/project.

Styles are the appropriate way of providing a consistent styling across multiple pages /sections of the application, so it makes sense to try and place those in a common area. However, there will be some styles which will be specific to the Windows or Windows Phone projects. The tricky part is finding a way to share the bulk of the style, except for those pieces which are specific.

My first thought was to have a SharedStyles.xaml in the Shared folder, and a PlatformSpecificStyles.xaml in each Windows and WindowsPhone directory. Then in the App.xaml include first the shared files followed by the specific files. Something like this:

ResourceSharing.Shared\SharedStyles.xaml

<ResourceDictionary
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

 <Style TargetType="HubSection" x:Key="HubSectionStyle">
  <Setter Property="Background" Value="Pink" />
 </Style>
</ResourceDictionary>

ResourceSharing.Windows\PlatformSpecificStyles.xaml

<ResourceDictionary
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

 <Style TargetType="HubSection" BasedOn="{StaticResource HubSectionStyle}">
  <Setter Property="Foreground" Value="Purple" />
 </Style>
</ResourceDictionary>

ResourceSharing.WindowsPhone\PlatformSpecificStyles.xaml

<ResourceDictionary
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

 <Style TargetType="HubSection" BasedOn="{StaticResource HubSectionStyle}">
  <Setter Property="Foreground" Value="Blue" />
 </Style>
</ResourceDictionary>

ResourceSharing.Shared\App.xaml

<Application
    x:Class="ResourceSharing.App"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
 <Application.Resources>
  <ResourceDictionary>
   <ResourceDictionary.MergedDictionaries>
    <ResourceDictionary Source="SharedStyles.xaml" />
    <ResourceDictionary Source="PlatformSpecificStyles.xaml" />
   </ResourceDictionary.MergedDictionaries>
  </ResourceDictionary>
 </Application.Resources>
</Application>

However, it turns out that doesn't work. In order for ResourceDictionary A to reference a resource from ResourceDictionary B, the ResourceDictionary A needs to include the ResourceDictionary B itself. So the end result ended up looking like this:

ResourceSharing.Shared\SharedStyles.xaml unchanged ResourceSharing.Windows\Styles.xaml (renamed from PlatformSpecificStyles.xaml)

<ResourceDictionary
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

 <ResourceDictionary.MergedDictionaries>
  <ResourceDictionary Source="SharedStyles.xaml" />
 </ResourceDictionary.MergedDictionaries>
 
 <Style TargetType="HubSection" BasedOn="{StaticResource HubSectionStyle}">
  <Setter Property="Foreground" Value="Purple" />
 </Style>
</ResourceDictionary>

ResourceSharing.WindowsPhone\Styles.xaml (renamed from PlatformSpecificStyles.xaml)

<ResourceDictionary
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

 <ResourceDictionary.MergedDictionaries>
  <ResourceDictionary Source="SharedStyles.xaml" />
 </ResourceDictionary.MergedDictionaries>
 
 <Style TargetType="HubSection" BasedOn="{StaticResource HubSectionStyle}">
  <Setter Property="Foreground" Value="Blue" />
 </Style>
</ResourceDictionary>

ResourceSharing.Shared\App.xaml

<Application
    x:Class="ResourceSharingHubApp.App"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
 <Application.Resources>
  <ResourceDictionary>
   <ResourceDictionary.MergedDictionaries>
    <ResourceDictionary Source="Styles.xaml" />
    <!-- Styles contains both Shared and PlatformSpecific -->
   </ResourceDictionary.MergedDictionaries>
  </ResourceDictionary>
 </Application.Resources>
</Application>

Hope that helps

Slides from the SQL PASS BAC Conference - 60 Reporting Tips in 60 Minutes

SQL Tip: Use Templates and Parameters

Slides from talk at SQL Saturday Silicon Valley

SQL Tip: Quickly Alias Columns

Refactor vs rewrite (again)

I don't know why I have to blog about this.  It depresses me and reflects poorly on our entire industry.  It is 2014!  Don't we know better?!?

Apparently not.  I have found myself *several* times in the past month, having to argue against a big rewrite of many thousands of lines of code.  I am amazed and appalled that anyone still thinks this way, after *so* many articles have been written for *so* many years.  Two of my favorites are Joel in the year 2000, and this more recent article that references one of my favorite cartoons (thanks to Lance for introducing me to the cartoon).

One thing that is different from when Joel wrote that blog post 14 years ago is that refactoring software is so much easier now.  It is now *incredibly* easy, in addition to being much safer.

I don’t want to go through the same arguments again, because so many others have done it for me.  Unlike some of the people I have to convince, I am not stupid enough to think that I am the first person faced with this decision, or arrogant enough to dismiss what those hundreds of other people have said.  However, I will offer one small piece of advice.  Often times the refactoring can happen *while* people are designing what the rewritten version is going to look like.  At that point the code will be easy to change and it will be a much simpler process to add the new features.

Azure DocumentDB First Look

Step 1: Run the PowerShell Scripts

Step 2: Use the new Azure portal at portal.azure.com

Step 3: Run NuGet: Install-Package Microsoft.Azure.Documents.Client -Pre

Step 4: This is the Flashcard class

 
public class FlashCard
{
    [JsonProperty(PropertyName = "id")]
    public string ID { get; set; }
    [JsonProperty(PropertyName = "question")]
    public string Question { get; set; }
    [JsonProperty(PropertyName = "answer")]
    public string Answer { get; set; }
}

Step 5: This is the console application:

 
internal class Program
{
    private static void Main(string[] args)
    {
      CreateSaveAndGet().Wait();
      //CleanUp().Wait();
    }

public static async Task CreateSaveAndGet()
{
 //connect database
 var client = GetClient();
 //find or create a database
 var database = await GetDatabase(client);

 //create a collection
 var collection = await GetCollection(client, database);

 Console.WriteLine("Save new flashcards...");
 var flashcard1 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
 {
  ID = "1",
  Question = "When did Azure DocumentDB release in preview?",
  Answer = "August 21st, 2014",
 });

 var flashcard2 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
 {
  ID = "2",
  Question = "What is Azure DocumentDBs twitter handle?",
  Answer = "@DocumentDB",
 });

 var flashcards = await Task.Run(() => client.CreateDocumentQuery(collection.DocumentsLink)
  .AsEnumerable()
  .ToList());

 Console.WriteLine("Iterating through flashcards...");
 foreach (var flashcard in flashcards)
 {
  Console.WriteLine("QUESTION " + flashcard.ID.ToString() + ": " + flashcard.Question);
  Console.WriteLine("Answer: " + flashcard.Answer);
  Console.WriteLine("Press a key");
  Console.ReadKey();
 }
 Console.ReadKey();

 var flashcardGet = await Task.Run(() =>
  client.CreateDocumentQuery(collection.DocumentsLink)
   .Where(d => d.ID == "1")
   .AsEnumerable()
   .FirstOrDefault());

 Console.WriteLine(flashcardGet.Question);
 Console.ReadKey();

 var doc = client.CreateDocumentQuery(collection.DocumentsLink)
  .Where(d => d.Id == flashcardGet.ID)
  .AsEnumerable().FirstOrDefault();

 await client.DeleteDocumentAsync(doc.SelfLink);

 flashcardGet = await Task.Run(() =>
  client.CreateDocumentQuery(collection.DocumentsLink)
   .Where(d => d.ID == "1")
   .AsEnumerable()
   .FirstOrDefault());

 Console.WriteLine(flashcardGet.Question);
 Console.ReadKey();
    }

    private static async Task CleanUp()
    {
 //connect database
 var client = GetClient();

 //find or create a database
 var database = await GetDatabase(client);

 //create a collection
 var collection = await GetCollection(client, database);
 await client.DeleteDocumentCollectionAsync(collection.SelfLink);
    }

    private static DocumentClient GetClient()
    {
 string endpoint = ConfigurationManager.AppSettings["EndPoint"];
 string authKey = ConfigurationManager.AppSettings["AuthKey"];

 Uri endpointUri = new Uri(endpoint);
        var client = new DocumentClient(endpointUri, authKey);
 return client;
    }

    private static async Task GetDatabase(DocumentClient client)
    {
 Database database;
 var databaseName = "flashcards";
 var databases = client.CreateDatabaseQuery()
  .Where(db => db.Id == databaseName).ToArray();

 if (databases.Any())
 {
  database = databases.First();
 }
 else
 {
  database = new Database { Id = databaseName };
  database = await client.CreateDatabaseAsync(database);
 }
 return database;
    }
    private static async Task GetCollection(DocumentClient client, Database database)
    {
 var collectionName = "flashcards";
 DocumentCollection collection;

 var collections = client.CreateDocumentCollectionQuery(database.SelfLink)
  .Where(col => col.Id == collectionName).ToArray();

 if (collections.Any())
 {
  collection = collections.First();
 }
 else
 {
  collection = await client.CreateDocumentCollectionAsync(database.SelfLink,
   new DocumentCollection { Id = collectionName });
 }
 return collection;
    }
}

Video of Learn JavaScript Properly Part 1 - SQL Pass Book Readers

Simplifying Andoid Concepts

When it comes to Android there are fundamental concepts that a programmer *must* understand in order to author code for the platform or to understand code found online. You need to understand these concepts even if you are writing the application in Xamarin or some other abstraction layer.

From the Android developer web pages (http://developer.android.com/) you can find out that by default, every app runs in its own Linux process. Components are the essential building blocks of an Android app. Each component is a different point through which the system can enter your app.

There are four types of components:

Activity	Screen with a user interface
Service	Performs long running operations in the background
Content Provider	Manages a shared set of app data
Broadcast Receiver	Responds to system-wide broadcast announcements

Broadcast receiver makes perfect sense, and anyone coming from a Windows world thinks of services as performing background tasks already. If you consider data as content then of course a content provider provides data to the rest of the application. So the only one that has a slightly bizarre name is an Activity. If you think of an activity as a single focused thing that a *user* can do, then it makes sense.

Three of them (Activity, Broadcast Receiver, and Service) are all activated by an asynchronous message called an intent. Intents are confusing, but they are essentially an abstract description of an operation to be performed. Try using the mnemonic, "It is my intent to perform this operation." Another way to think of an intent is a description of an operation written down on a piece of paper (more on this later).

In Android an intent can be either explicit or implicit. In an explicit intent the fully qualified class name of an actual component that needs to run is on the piece of paper. Android makes sure that the component runs.

An implicit intent is a little more vague. Once again it is just a string, but it is global, and you don't want to collide with someone else's string. The implicit intents are generally reverse domain qualified (e.g. "com.mydomain.myapp.myintent") so that there are no naming conflicts.

When apps are *installed* Android takes all of the implicit intents specified in the manifest and adds them to its global table. If we go back to the piece of paper analogy, an implicit intent is a single-sheet newsletter. Applications that include that implicit intent in the filters section of the manifest are subscribing for the newsletter, and then when an app publishes the newsletter, Android makes sure that it gets to all of the subscribers.

There is an addtional type of intent called a *pending* intent. Things get a little more complicated with a *pending* intent. However it is easy to understand if we think of an intent (or piece of paper) that has been placed inside an envelope with some handling instructions, similar to inter-office mail or do not open until your birthday. The action written on the piece of paper is performed on behalf of the sender.

One set of handling instructions is the flags parameter. All of the static methods for creating a PendingIntent, like getActivity, getBroadcast, or getService, accept a flags parameter. Flags can be combined together and some only make sense in the context of the other flags. Here are the flags on PendingIntent and how they fit in with the envelope analogy.

FLAG_CANCEL_CURRENT	This is an update, throw away the old piece of paper you were working on and replace it with this new one
FLAG_NO_CREATE	(NOTE: only relevant in combination of the CURRENT flags). If you didn't already have an envelope, ignore this one also
FLAG_ONE_SHOT	Burn after reading
FLAG_UPDATE_CURRENT	Don't throw the first piece of paper away, instead make these revisions to the piece of paper

Here is a few snippets of code (C# code written using Xamarin Studio) that show how you would use an intent in practice. In this example we are creating a “Geofencing Intent”. A GeoFence is simply a location based circle that a user can enter or leave. When either of those events happen your application can be notified. Think of the example where the user wants to be reminded to “take the trash out when I get home”. The “when I get home” piece indicates a location based area. We can create a GeoFence for this area and be notified when the user enters this area.

Here is an example of of creating an IntentService using Xamarin C# code

using Android.App;
using Android.Content;
using Android.Support.V4.App;
using Android.Gms.Location;
using Android.Locations;
 
namespace TIG.Todo.AndroidApp
{
 [Service]
 [IntentFilter(new[] { "com.sdtig.Todo.WITHIN_PROXIMITY" })]
 public class GeofenceIntentService : IntentService
 {
  protected override void OnHandleIntent (Intent intent)
  {
   //Handle Intent
  }
 }
}

Below is a snippet that shows an example of sending a PendingIntent out (see line 43)

 [Service]
 [IntentFilter(new[] { "com.sdtig.Todo.START_LOCATION", "com.sdtig.Todo.SET_GEOFENCE" })]
 public class GeofencingHelper : Service, Android.Locations.ILocationListener
 {
  public override Android.OS.IBinder OnBind (Intent intent)
  {
   return null;
  }
 
  public override void OnStart (Intent intent, int startId)
  {
   if (intent.Action == "com.sdtig.Todo.START_LOCATION")
   {
     //Elided
   }
   if (intent.Action == "com.sdtig.Todo.SET_GEOFENCE")
   {
    SetFence();
   }
  }
   
 
  public void SetFence()
  {
   bool isWithinRadius = false;
   foreach (var fence in fences)
   {
    float[] results = new float[1];
    Location.DistanceBetween(fence.Latitude, fence.Longitude, currentLatitude, currentLongitude, results);
    float distanceInMeters = results[0];
    if (distanceInMeters < radiusInMeters)
    {
     isWithinRadius = true;
     break;
    }
   }
 
   if (!isWithinRadius)
   {
    var intent = new Intent(CustomActions.TODO_WITHIN_PROXIMITY);
    PendingIntent pendingIntent = PendingIntent.GetService(this, 0, intent, 
     PendingIntentFlags.UpdateCurrent);
    _locationManager.AddProximityAlert(currentLatitude, currentLongitude, 
     radiusInMeters, -1, pendingIntent);
   }
  }
 }

And finally here is a snippet showing the MainActivy.cs file where we start an Intent service to watching for system intent's related to Location Monitoring

using System.IO;
using Android.App;
using Android.Content;
using Android.Widget;
using Android.OS;
using TIG.Todo.Common;
using Android.Content.PM;
using TIG.Todo.Common.SQLite;
 
namespace TIG.Todo.AndroidApp
{
 [Activity (Label = "TIG.Todo.Android", MainLauncher = true, ConfigurationChanges = ConfigChanges.Orientation | ConfigChanges.ScreenSize)]
 public class MainActivity : Activity
 {
  private TaskManager taskManager;
 
  protected override void OnCreate (Bundle bundle)
  {
   base.OnCreate (bundle);
 
   // Set our view from the "main" layout resource
   SetContentView (Resource.Layout.Main);
 
   //Intent intent = new Intent (this, typeof(GeofencingHelper));
   StartService(new Intent("com.sdtig.Todo.START_LOCATION"));
  }
 }
}

Hopefully the analogy helped to simplify some of the more abstract Android concepts.