The Buttonfactory

PowerBI -Fix: The import List.ConformToPageReader matches no exports. Did you miss a module reference?

Melanie koorevaar — Mon, 23 Nov 2020 16:11:38 +0000

Did you wake up this morning finding PowerBI failure messages in your inbox?

Then you are probably not the only one. Your Gateway is out of date and left you with errors on your Datasets on PowerbI.

Some datasets are not able to refresh due to a module not being found, but testing your connections all seems to be working fine ...or not? It does not cleary indicate something is wrong with your gateway, it just gives you a friendly suggestion to update, but all connections are A OK!

What you need to do is install the recently released (let me do a wild guess, last Sunday I...?) gateway version number: 3000.66.4 november 2020

Once you update your (on-premise) gateway server and give it a restart. You will be able to refresh all your reports.

Thank you Microsoft for keeping us busy today!

IBM Watson assistant – Live web chat integration

Melanie koorevaar — Wed, 30 Oct 2019 15:23:38 +0000

IBM has launced a new feature as add on to the virtual assistant: Web chat - Live agent. IBM is really putting some work in becoming the best virtual assistant.
This functionallity can switch a user from a chatbot conversation directly to a real human agent.

This is the second feature, next to the search skill (Discovery), that can really boost your chatbot game. Since your chatbot can now be a single point of contact for all your customers!

The web agent is an of the shelf integration, just add a script provided by IBM, no developer or programming skills are needed.

How do you integrate it on your own website?
You need to look up the Live Engage integration in the catalog on IBM cloud. You will need a plus or premium plan. You will be able to escalate conversations to this platform out of the box using Web Chat.

Just put the script element in the header of the website page you want it to display your assistant:




    My Test Page
    The body of my page.

This is how the UI looks like for an agent. It can see the history of the current chat and respond real time to the customer.

Golang and that strange asterisk and ampersant

Jacqueline — Sun, 18 Aug 2019 09:29:39 +0000

When learning Golang and coming from other (scripting) languages, that asterisk and ampersant may strike as a bit odd.

This is a beginner Golang post!

Let's start with a basic Golang program outline and in the main func, let's create a variable named a and assign it the value Three-toed sloth.

package main
import (
    "fmt"
)
func main() {
  a := "Three-toed sloth"
  fmt.Println(a)
}

Notice the : before the = and that no type was specified. The Go compiler is able to derive the type based on the literal value of the variable. So it knows it's a string.

I could also have declared the variable like this:

var a string = 'Three-toed sloth

Ok, so the program will return:

Three-toed sloth

So you see that a is assigned a value of Three-toed sloth. It has stored that value in memory. And we can get the memory address by adding the & (ampersant).

Like this:

package main
import (
    "fmt"
)
func main() {
  a := "Three-toed sloth"
  fmt.Println(a)
  fmt.Println(&a)
}

This will return:

Three-toed sloth
0xc0000861c0

We can get the types like so:

fmt.Printf("%T\n",a)
fmt.Printf("%T\n",&a)

Adding this to our func will return:

 string
*string

We can also get the value of what is stored on that memory address like so (with the asterisk!):

  b := &a
  fmt.Println(*b)

This will return:

Three-toed sloth

And finally we can do:

    fmt.Println(*&b)

Now what would that return?

Exactly:

0xc0000861c0

Anyways, here is the complete code of this very useful exercise:

package main
import (
    "fmt"
)

func main() {
    a := "Three-toed sloth"
    fmt.Println(a)
    fmt.Println(&a)
    fmt.Printf("%T\n",a)
    fmt.Printf("%T\n",&a)
    b := &a
    fmt.Println(*b)
    fmt.Println(*&b)
}

So the gist of this post comes down to:

The & will give you the memory address
The asterisk (*) will give you the value stored at an address, aka the pointer to the memory location where the value of the variable is stored.

Now consider this piece of code without any pointers:

package main
import (
    "fmt"
)

func main() {
    x := 100
    blah(x)
    fmt.Println(x)
}

func blah(y int) {
    fmt.Println(y)
    y = 12
    fmt.Println(y)
}

This will return

100 # from blah
12 # from blah
100

But when we change the signature of 'blah' so that it will take in a memory address instead of an actual int

package main
import (
    "fmt"
)

func main() {
    x := 100
    blah(&x)
    fmt.Println("From main: ", x)
}

func blah(y *int) {
    fmt.Println("1.From foo:", y)
    *y = 12
    fmt.Println("2.From foo:", y)
}

This will return

1.From foo: 0xc00001c0c8
2.From foo: 0xc00001c0c8
From main:  12

That value will be never be the value of 100 because the blah function assigns the memory address the value of 12.

Finally a tip for a great Golang mentor: https://twitter.com/Todd_McLeod

Protected: Simple JSON codes mockup voor Watson Assistant

Melanie koorevaar — Fri, 05 Jul 2019 20:16:41 +0000

IBM Cloud platform Watson API – CLI Tools Error: NO CF API endpoint set

admin — Fri, 05 Jul 2019 17:16:36 +0000

IBM Watson is a really good AI platform.
But since development of the Watson Platform goes so quickly, they keep pushing new updates and workspaces.
If you are a developer, this can be quite time consuming, since you need to keep rebuilding the former workspace now called Skills and APi configurments.
This last Update to V2 and the deprecation of the bluemix environment gave me quite a few headaches.

To save you from the hassle, here is an example how you can rebuild a Watson assistant and Watson discovery API with the cloud CLI Tool.

c:\Program Files\IBM\Cloud\bin>ibmcloud cf push
FAILED
No CF API endpoint set.
Use 'ibmcloud target --cf-api ENDPOINT [-o ORG] [-s SPACE]' to target Cloud Foundry, or 'ibmcloud target --cf' to target it interactively.

it's because you are still pointing to the old bluemix link:
c:\Program Files\IBM\Cloud\bin>ibmcloud api https://api.eu-gb.bluemix.net
Setting api endpoint...
API endpoint https://api.eu-gb.bluemix.net is going to be deprecated. Use https://cloud.ibm.com.

here is what you do:
c:\Program Files\IBM\Cloud\bin>ibmcloud api https://cloud.ibm.com
c:\Program Files\IBM\Cloud\bin>\ibmcloud login
now your ENDPOINT is set to the cloud.ibm.com
Now set the right environment for discovery:
c:\Program Files\IBM\Cloud\bin>ibmcloud target --cf-api api.eu-gb.cf.cloud.ibm.com

Now you can (re-)do al your CF functions

Deploy linked Azure Resource Manager templates with a SAS token

Jacqueline — Mon, 02 Jul 2018 05:23:27 +0000

ARM templates tend to get huge when your deployments get more complex.
With linking you can call an ARM template from another template and create a hierarchy of your templates, making it easier to adjust and reuse the templates. You can pass parameters from the master template to the linked template.

Linked templates are not very intuitive to use however. In this blog post I will walk you through an example where I deploy a storage account with a linked template. I will also show you how to use the template in a CD/CI pipeline in Visual Studio Team Services.

A complete example is on my Github repository.

The linked storage template

Let's start with a regular template for storage. However, without the variables! A linked template only has parameters.
These parameters will be populated by the master template. These parameters can be hardcoded, populated by variables or declared in a separate parameters template.

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "storageAccountType": {
            "type": "string",
            "defaultValue": "Standard_LRS",
            "allowedValues": [
                "Standard_LRS",
                "Standard_GRS",
                "Standard_ZRS",
                "Premium_LRS"
            ]
        },
        "storageAccountTier": {
            "type": "string",
            "defaultValue": "Standard",
            "allowedValues": [
                "Standard",
                "Premium"
            ]
        }
    },
    "resources": [
        {
            "apiVersion": "2017-10-01",
            "name": "[concat('disk', uniqueString(resourceGroup().id))]",
            "type": "Microsoft.Storage/storageAccounts",
            "sku": {
                "name": "[parameters('storageAccountType')]",
                "tier": "[parameters('storageAccountTier')]"
            },
            "kind": "Storage",
            "location": "[resourceGroup().location]",
            "tags": {}
        }
    ]
}

Let's call this template storage.json.
Now we are going to call this template from a master template that I will name template.json.

The master template

Let's create a folder structure like this:

In template.json I need to make a reference to storage.json. I could put my ARM Templates on Github or GitLab and reference the public URI of storage.json. But what if you are in an enterprise and you need to keep your templates private? What if you want to run the templates from a private storage account?
Then you will want to protect them with a SAS Token. How that works will be described in the last part of this article.

This is how the master.json file will look like:

 {
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "artifactsLocationSasToken": {
            "type": "string"
        },
        "artifactsLocationStorageAccount": {
            "type": "string"
        }
    },
    "variables": {
        "storageAccountType": "Standard_LRS",
        "storageAccountTier": "Standard",
        "nestedTemplates": {
            "storageTemplateUrl": "storageTemplateUrl": "[uri(deployment().properties.templateLink.uri, 'nestedtemplates/storage.json' )]"
        }
    },
    "resources": [
        {
            "name": "storageDeployment",
            "type": "Microsoft.Resources/deployments",
            "apiVersion": "2017-05-10",
            "dependsOn": [],
            "properties": {
                "mode": "Incremental",
                "templateLink": {
                    "uri": "[concat(variables('nestedTemplates').storageTemplateUrl, parameters('artifactsLocationSasToken'))]",
                    "contentVersion": "1.0.0.0"
                },
                "parameters": {
                    "storageAccountType": {
                        "value": "[variables('storageAccountType')]"
                    },
                    "storageAccountTier": {
                        "value": "[variables('storageAccountTier')]"
                    }
                }
            }
        },
    ],
    "outputs": {
    }
}

Some explanation: according to the Microsoft docs you can use deployment() to get the base URL for the current template, and use that to get the URL for other templates in the same location. The templateLink property is only returned when linking to a remote template with a URL. If you're using a local template, that property isn't available.

So we need to concatenate uri(deployment().properties.templateLink.uri plus nestedtemplates/storage.json. That looks like this:

"nestedTemplates": {
"storageTemplateUrl": "storageTemplateUrl": "[uri(deployment().properties.templateLink.uri, 'nestedtemplates/storage.json' )]"
}

And append the SAS Token" parameters('artifactsLocationSasToken') in our resource section:

"nestedTemplates": {
"templateLink": {
"uri": "[concat(variables('nestedTemplates').storageTemplateUrl, parameters('artifactsLocationSasToken'))]",
"contentVersion": "1.0.0.0"
},

Pass the parameters

As already mentioned, you can pass parameters:

Hardcoded the nested template (not recommended)
Hardcoded in the master template in parameters or variables (semi recommended)
In a separate parameters file (recommended)

I would recommend to use the parameters file to set values that are unique to your deployment. Then you can use the concat function to create other resources names in variables.

Nested templates and dependencies

You can reference to the deployment like this:

"nestedTemplates":
"dependsOn": [
"Microsoft.Resources/deployments/storageDeployment"
]

Deployment

Finally, the deployment. If you are in an enterprise and you need to keep your templates private you will want to run the templates from a private storage account. You can achieve this with a SAS Token.

The steps are as follows:

Create separate resource group with a storage account
Create a container in blob storage
Upload all templates and scripts to this container
Create a SAS Token for this container with a valid time of 2 hrs
Inject the SAS Token to your parameters.json file
Append the SAS Token to the nested template URI

Basically, this is what the PowerShell script does when you create an ARM Template in Visual Studio! However, I think it's good to know what it actually does under the hood.

I would suggest you to create a service principal. Here is how.
We need the clientId, Secret, TenantId and SubscriptionId from the principal.

You can find the complete script here.

Then run the script:

$vars = @{
ClientId = ""
Secret = ""
TenantId = ""
SubscriptionId = ""
ResourceGroupName = "azure-vm-poc"
ArtifactsResourceGroup = 'my-artificats'
ArtifactsLocationStorageAccount = 'mybeautifulartifacts'
}

# modify path if needed
.\New-AzureDeploy.ps1 @vars -Verbose

Add the script to a build or release pipeline with VSTS

Simply add an Azure Powershell script task and call the script. Define the variables in VSTS.

Troubleshoot

Sometimes the error message in the PowerShell console are a bit cryptic. With this command you will get more verbose error messages:

(Get-AzureRmLog -Status "Failed" | Select-Object -First 1) | Format-List

Artificial Intelligence – Chat Bot Back to Basic part 3

Melanie koorevaar — Thu, 24 May 2018 22:32:22 +0000

We experimented a little with Machine learning, but now we get to the part where it really get's interesting. Creating a chatbot.

In this example, I use a simple tool called Q&A maker and the Azure QnA maker resource. When you connect both services, you can integrate the bot on social media like Skype, messenger or as a Cortana service, or create a (mobile) app. Sign up at https://qnamaker.ai. and create a new knowledgebase.

I added some of my car data to the knowledge base and named it 'license check'. Based on a license plate we send to the bot, it will respond with the matching car brand. we need to train the bot and publish it. as you can see in this example: Save and train your Q&A with the data, test the data and when you like the results you Publish it.

When you publish the Q&A it will open a new screen with the URL details, you will need the details to associate your Q&A data with the Azure bot:

Go to Azure Bot service in Azure portal and go to the Application Settings page, in the App Settings section, set the QnAKnowledgebaseId, QnAAuthKey, and QnAEndpointHostName values from the published page and save the keys to Azure.

Now try out the new bot in the Azure webchat.

This is still very basic information, but we can 'learn' our bot new skills, for instance to be more social. Since we are humans, we always greet someone when we meet. So, what if someone says hello? the bot will not recognize this as a license plate and will respond with an error, but we can learn the bot, that whenever someone says Hi, Hello or Hoi, The bot will respond back with a friendly Hello! as you can see in this clip. It even understands bad sentences and grammar:

This is just the very basics of a chatbot. You can also add LUIS (Language understanding intelligence service) to your bot. LUIS service is a language processor. You need to create a LUIS service in Azure portal: add new resource and browse for LUIS. Then sign up at https://www.luis.ai and create an intent to associate LUIS with your Q&A content. This makes your bot interesting to publish it under Cortana, You can just tell your computer what license plate you are looking for.

Read my previous post on creating the car data for Machine learning.

Artificial Intelligence prepping data – Back to basic part 1

Melanie koorevaar — Sat, 19 May 2018 18:26:52 +0000

What street legal cars taught me about Machine learning? It's all about the right data being available and the RDW data can't be trusted!

I impulsively signed up for an Artificial Intelligence certification track 2 months ago, So I've been experimenting with Artificial Intelligence for a while now and in the beginning of the course it was one though cookie! Those formula's to interpreted the data predictability really freaked me out!

But once I got past the formula's and I saw the resemblance of the workspace with Microsoft products like SSIS and BI I see endless possibilities. This takes the data to a whole new level.

Preparing the data:

I did a test on all cars that are currently on the road in the Netherlands and combined it with performance data. I wanted to find the fastest street legal car. I guess I just wanted to find out what kind of cars I should fancy these days according to the performance stats.

I used an open data set from the dutch RWD (Driver and Vehicle Standards Agency). It contained 14m rows and it's 7GB in size. So I had to prepare the data in order to keep the experiment basic and performance high. I imported it into my SQL server and I filtered out the the stationwagons, campers, scooters and trailers, So I was left with a 900000 rows data set.

I use a SQL Server 2017 and the Microsoft Azure machine learning studio to create a new experiment.

In order to make a prediction I needed to combine the brand data with the engine displacement data, because horsepower data was not available, to see which models are high performance based on the engine capacity. So sadly the smaller engines which are supercharged are not correctly represented in the prediction.

The calculation based on above rules, took a local SQL server on an i5 laptop about 15 minutes. I needed more data preparation.

Based on engine displacement, a top 3 came up. But I didn't like the results at all. Sure, the engine displacement was high, but the cars are heavy and their performance isn't the best. Super charged Turbo's and gearing make all the difference, but aren't properly represented in this data result.

I had to filter out a lot of data, next up I added the weight of the car, but it wasn't trust worthy either. I found a data set which contained the Kw of the cars and top speed and joined the data with my current results and added a calculation in SQL on the Kw row * 1,362 to calculate the Hp of the car. The Hp outcome looks pretty accurate. After 4 hours of combining data and filtering the queries I gave up. Based on this data there is no way you can truly point out the fastest cars. I had to change my plans. Too many uncertain variables to make a decent prediction and not even close to the start of an IA project

Lot's of NULL data

This No. 1 car can't be trusted!

After more data crunching, The results are still not really worth to display. So here is a TOP 21 of "fastest" cars...based on...well the obvious HP and Weight sorting:

btw, did you know there is only one Koenigsegg on the dutch roads.

Ok, I got a little bit carried away with data prepping.

Now let's import it into an IA experiment: First you need to create a resource in the Azure Portal for your workspace. I won't get into details, we did this before!

Verify that you created the following new resources: A Machine Learning Workspace, A Machine Learning Plan and A Storage Account.

Browse to the Machine learning workspace you created and launch Machine Learning Studio. This opens a new browser page.

Go to experiments and down in the left corner click NEW.

Rename the experiment and add a dataset. Upload a new dataset. Datasets --> NEW--> Select data to upload. Now that you have the dataset ready, you can drag it into your experiment and start running tests and variables on the data.

In my next post we will dive deeper into Artificial Intelligence

PaaS taking over the world! Are dba’s a dying breed?

Melanie koorevaar — Sun, 01 Apr 2018 15:58:53 +0000

PaaS

PaaS vs IaaS

It's easter weekend, or as the dutch say 'PaaS weekend'. So, it might be a good idea to talk a bit about PaaS. What is it and what does this mean for you as a Database administrator? Are Dba's a dying breed. Will they just shift over to more complex or broader tasks or are they here to stay. It all depends on the company and the software they are running.

What is #Iaas? in short, it's a VM. Your database is running in a data center. As a Dba you have the same job requirements as when running a database on-premise. Update, backup, patches, tuning, security and account control.

But #PaaS is a different story, it runs the database as a service, so there is no need for a dba. At least that's what they tell you. But does PaaS solve all your performance and tuning needs, is that faulty query when moved to PaaS suddely solved? Nope. Machine learning is doing a great job so far, but it isn't the magical quick fix, yet.

In all honesty most companies don't care about tuning a database, not all applications have complex queries and tasks running on their SQL server, most are fine running an express edition. They don't even bother having a Dba. The database is taken care of by a system engineer, if being looked at, at all.

Where does this put you as a Dba? Don't sob, we still need you! It's a big relief that the market, which is still flooded with old school high maintenance MS SQL driven apps. How nice if these could be taken care of with PaaS. The only good thing coming out of these high maintenance, splintered databases, is the data itself. Do you really want to spend your time updating, patching and granting rights to users and saying no to SA account requests? No, you don't!

But second, in the real world, companies don't evolve as fast as the IT world itself. Most applications, lots outdated or not are not being replaced overnight and not all the vendors are quite ready for PaaS environments with their applications. So you just have to decide on which side you want to be, fast IT or slow IT. There is still a big playground available for both for the coming years.

Polybase configuration on SQL Server 2017 Part II

Melanie koorevaar — Fri, 16 Mar 2018 18:06:13 +0000

Image: Microsoft

Nowadays your precious data can be stored everywhere, not just on several servers with different SQL versions, your data probably is wide spread in the cloud. It's also a good idea to store data in the cloud with stretch database to release your local discs from excessive data and still be able to query it, but also use it in your SSIS and BI environment and keep an acceptable ETL. With Microsoft's polybase you can access, import and export any data structured, semi, or non structured on the Hadoop platform and azure blob storage using T- SQL language.

The best business knowledge comes from the data you collect. So it might be a good idea to put the data you collect into some good use. Businesses collect lot's of data, but in most cases this is also where it ends. Those who read my posts before, know I am all about combining various sources with linked servers, since SQL 2014 lot's of new features are available for using all your data on business intelligence platforms.

In my last post, we had a first look and troubleshoot of a polybase installation. This time we are going to configure and use the polybase in SQL server 2017. I'm going to use the Blob storage on Azure to demonstrate how you can implement this solution in your (local) SQL database.

First things first, now you've got your polybase installation ready, check if the services for polybase exist and are running.

Services: 'SQL Server PolyBase Data Movement' and 'SQL Server PolyBase Engine'

You need to configure Polybase in order to start using it. Fire up SSMS and open a new query window. Type

sp_configure ' hadoop connectivity', 4;
reconfigure

Option 4 is Azure blob storage (WASB[S]). For more info on availability of the Polybase connectivity configuration, take a look here. Run the query and make sure you restart both Polybase services on the machine to finish the configuration.

In order to start using the blob storage make sure you have an Azure storage account if you don't have an Azure account yet, create one here.

Give it some time, once the storage is created, you also need to create a container on the Azure storage.

To connect your local db to azure storage, you need to get the azure storage key from your Azure storage account you just created and put it in the configuration file of your SQL installation.
Look for the core-site.xml file in the installation path of SQL Server.
The path looks simular to this: "C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBinnPolybaseHadoopconf"
This will open the config directory with the core-site.xml file.

Open the file in notepad and add code before the block of code mentioning Kerebos.

Fill in the storage name, in my case polybasedemo and the storagekey and save the file.

 fs.azure.account.key..blob.core.windows.net

Now we have to create an external data source in SSMS. Replace containername@storagename with the names you created on Azure.

CREATE EXTERNAL DATA SOURCE PolyBaseDemo WITH
( TYPE = HADOOP, -- wasbs:// containername@storagename.blob.core.windows.net/ 
LOCATION = 'wasbs://containername@storagename.blob.core.windows.net/' );

Next up, we create the external file format to define external data on Azure blob storage, this needs to be done in order to create the external table

CREATE EXTERNAL FILE FORMAT PolybaseFormat 
WITH ( FORMAT_TYPE = DELIMITEDTEXT , FORMAT_OPTIONS ( FIELD_TERMINATOR = ',' ) );

This creates 2 new server objects in SSMS and now all is left to create the external table itself. In this demo I use an excel sheet with some irrelevant data to have some test data available.

USE [DemoPolybase] CREATE EXTERNAL TABLE [dbo].[Customers] ( [Name] VARCHAR(255) NULL, [adres] VARCHAR(255) NULL, [postalcode] VARCHAR(6) NULL ) WITH (LOCATION = N'/Customer_Export.csv', DATA_SOURCE = PolyBaseDemo, FILE_FORMAT = PolybaseFormat, REJECT_TYPE = Value, REJECT_VALUE = 10) GO

And to see if this worked, just query the data

SELECT * FROM [Customers]

Now put this knowledge into action yourself with some real data!