Press "Enter" to skip to content

Month: November 2018

Deploying SQL Server To Minikube

Published 2018-11-20 by Kevin Feasel

Chris Adkin continues a series on Kubernetes with a look at Minikube:

Once the cluster is up, lets deploy a stand alone SQL Server instance to it, availability groups will be covered at a later date. To do this several different types of object will need to be created:

  • Storage objects; persistent volume claims, these will lead to the automatic creation of a persistent volumes.

  • A secret to hold the password for the sa account.

  • A deployment, this embodies the SQL Server instance.

  • A service, this provides the means of accessing the instance via an ip address and port from outside the cluster.

Click through for a demo.

Comments closed

Running R Scripts In Power BI’s Query Editor

Published 2018-11-20 by Kevin Feasel

Brad Lewellyn walks us through the process of executing an R script against a table in Power Query:

If you aren’t able to open the R Script Editor, check out our previous post, Getting Started with R Scripts.  While it’s possible to develop and test code using the built-in R Script Editor, it’s not great.  Unfortunately, there doesn’t seem to be a way to develop this script using an external IDE like RStudio.  So, we typically export files to csv for development in RStudio.  This is obviously not optimal and should be done with caution when data is extremely large or sensitive in some way.  Fortunately, the write.csv() function is pretty easy to use.  You can read more about it here.

It’s not a perfect experience, but Brad does show us how to get it done.

Comments closed

Inactive Relationships In Power BI

Published 2018-11-20 by Kevin Feasel

Reza Rad explains the value of inactive relationships and shows how you can implement this in Power BI:

As you can see this new type of relationship is different. It is dashed line, compared to the active, which was a solid line. This is an inactive relationship. You can only have one active relationship between two tables. Any other relationships will become inactive.

An inactive relationship doesn’t pass filtering. It doesn’t do anything by itself. I still see many people creating inactive relationships in their model thinking that just the inactive relationship by itself will do some filtering. It doesn’t. If I use the FullDateAlternateKey from the DimDate table to slice and dice the SalesAmount from the FactInternetSales table, which field I’m filtering based on? The field that is related through an Active relationship of course. Here is a result for that (which is apparently same as what you have seen in the previous example because the inactive relationship doesn’t do anything. It is just the active relationship that passes the filter);

Read the whole thing.

Comments closed

Minor Differences Between Plan Cache And Query Store Plans

Published 2018-11-20 by Kevin Feasel

Grant Fritchey shows us some minor differences between what the Query Store shows for a particular execution plan versus what exists in the plan cache:

As you can see, while the structure of the plans are identical, not everything is. The Compile values are different (although sometimes, they’ll be the same, that one is kind of luck of the draw to a degree) because they were compiled at different times with varying load on the system, so certainly that will be reflected. However, the other differences are also interesting. Which of the plans was retrieved from cache for example and, more importantly, the statement for the plans. The one on the left is the plan from the Query Store. It was not retrieved from cache and, the statement is for the query, not the stored procedure. Meanwhile, the plan on the right is from cache and, it’s based on the plan handle from the stored procedure, so it reflects that in the Statement value.

Click through for the full set of differences as well as Grant’s explanation.

Comments closed

Working With Temporal Line Charts In Power BI

Published 2018-11-20 by Kevin Feasel

Marco Russo shows off a few things you can do with Power BI to make displaying temporal data in line charts better:

The first line is related to the week ending on February 2nd, so Sales Amount includes only 2 days (February 1st and 2nd) excluding the amount of other 5 days in the same week (January 27th to 31st). The same happens in the last week, which includes June 29th and 30th but does not include sales for the remaining 5 days in the same week (July 1st to 5th). This also explains why the report includes a week ending in July 2008 even though the Month slicer only includes dates up to June 2018.

We can create a measure that removes incomplete weeks from the calculation, as shown in the following code. A similar technique could be used for incomplete months and quarters.

There are some interesting techniques that Marco shows off, including hiding incomplete weeks.

Comments closed

Configuring Snapshot Replication

Published 2018-11-20 by Kevin Feasel

Nisarg Upadhyay shows us how to configure snapshot replication:

On the next screen, configure the SQL Agent security. To configure the Agent security, click the Security Settings button. The Snapshot Agent Security dialog box opens. In the dialog box, provide the account under which the subscriber connects to the publisher. Moreover, provide the account information under which the SQL Server agent job will be executed. For this demo, SQL Server jobs are executed under the SQL server agent service account, hence select the Run under the SQL Server Agent service account option. Subscribers will be connected to the publisher using SQL login, hence select the Using the following SQL Server login option and provide SQL login and password. In this demo, connect using the sa login. Click OK to close the dialog box and Click Next.

Snapshot replication is the easiest to get right, but most of the setup is the same for transactional or merge replication.

Comments closed

Operating Management Studio With Multiple Active Directory Accounts

Published 2018-11-20 by Kevin Feasel

Kenneth Fisher shows how to use different Active Directory credentials when using SQL Server Management Studio:

To help promote the seperation of duties one of the things my company has done is to divide our permissions into two accounts. We have one account that is for our daily tasks. Reading email, searching the internet, basic structure changes in a database etc. The other account is our admin account. It’s for remoting to servers, security tasks, really anything that requires sysadmin. I’m not going to argue the advisability of this because honestly, I’m kind of on the fence. That said, I do have to deal with it and there are a few tips in case you have to deal with it as well.

And if you’re not on the domain as well, runas /netonly /user:[domain\username] ssms.exe will do the job.

Comments closed

Deploying Cloudera Enterprise On Azure

Published 2018-11-19 by Kevin Feasel

Xavier Morera announces a new Cloudera course:

You will start by learning the Microsoft Azure services required to deploy a secure, elastic, Cloudera Enterprise cluster. These core services include security, networking, virtual machine management, and storage, just to name a few.

Then, you’ll learn best practices and patterns for cloud-based clusters, including tips and caveats for security and workload management.

Next, you’ll learn how to bootstrap a cluster using Cloudera Manager, which allows you to deploy a cluster on premises or in the cloud. The module covers how to deploy both development (Path A) and production-grade (Path B) clusters.

This is a free course, so if you’re looking for a way to fill your Thanksgiving weekend, this is definitely an option.

Comments closed

Python In SQL Server Reporting Services

Published 2018-11-19 by Kevin Feasel

Tomaz Kastrun shows how we can visualize results from Python models in SQL Server Reporting Services:

As we have created four different models, we would also like to have the accuary of the model visually represented using SSRS.

Showing plots created with Python might not be as straight forward, as with R Language.

Following procedure will extract the data from database and generate plot, that can be used and visualized in SSRS.

Tomaz shows us examples of displaying data as well as visuals generated in Python.

Comments closed

Running Python-Based ML Tasks In Excel

Published 2018-11-19 by Kevin Feasel

Tony Roberts shows off some of the functionality of PyXLL:

Once we’ve done the hard work of building and testing a model we need to put it to some use! Excel is a great front-end tool for playing with data interactively. It’s used virtually everywhere and so being able to deliver your model in Excel to non-developer users massively opens up opportunities for how it can be used in your business. Even if the model is being used as part of a real-time or batch system, being able to call the model interactively can be really helpful when trying to understand the behaviour of a system.

Fortunately now the model is written in Python getting it into Excel is extremely simple. PyXLL, the Python Excel Add-In has everything we need to write Python for Excel. All we need to do is add a few @xl_func decorators from the pyxll module and configure the PyXLL add-in to load the module containing our model.

If you’re not already familiar with PyXLL, check out the introduction to PyXLL from the user guide.

I mean, if the data’s going to live in Excel spreadsheets anyhow…

Comments closed