Press "Enter" to skip to content

Day: April 16, 2018

Push-Based Alerting With Kafka Streams

Published 2018-04-16 by Kevin Feasel

Robin Moffatt shows how to take syslog data and create a notification app using Python and Kafka Streams:

Now we can query from it and show the aggregate window timestamp alongside the result:

ksql> SELECT ROWTIME, TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss'), \
HOST, INVALID_LOGIN_COUNT \
FROM INVALID_USERS_LOGINS_PER_HOST;
1521644100000 | 2018-03-21 14:55:00 | rpi-03 | 1
1521646620000 | 2018-03-21 15:37:00 | rpi-03 | 2
1521649080000 | 2018-03-21 16:18:00 | rpi-03 | 1
1521649260000 | 2018-03-21 16:21:00 | rpi-03 | 4
1521649320000 | 2018-03-21 16:22:00 | rpi-03 | 2
1521649080000 | 2018-03-21 16:38:00 | rpi-03 | 2

In the above query I’m displaying the aggregate window start time, ROWTIME (which is epoch), and converting it also to a display string, using TIMESTAMPTOSTRING. We can use this to easily query the stream for a given window of interest. For example, for the window beginning at 2018-03-21 16:21:00 we can see there were four invalid user login attempts. We can easily check the source data for this, using the ROWTIME in the above output for the window (16:21 – 16:22) as the bounds for the predicate:

It’s a very interesting use case.

Comments closed

Using The Bot Framework

Published 2018-04-16 by Kevin Feasel

Jakub Kaczmarek demonstrates using the Microsoft Bot Framework:

Before starting a new bot project, you need to consider if it really is a solution for your business case. It’s not recommended to start bot development just because it’s a hot topic. However, in some cases, this kind of software can save a lot of time, money and resources. The following list of bot example use cases might help in making the decision:

  • Answer for typical questions

    • A bot can make use of Q&A knowledge to receive user question and provide an appropriate answer.
    • Questions can be matched to correct answers using a LUIS (language understanding intelligent service) cognitive service.
    • Reduced time can be spent by help desk staff answering typical questions.
    • Example use cases are help chat, contact pages and web stores.

  • Alternative system interface

    • By integrating a bot with external systems (e.g. Outlook, Jira, CRM, SharePoint) a bot can become an alternative interface to work with these systems.
    • A bot can simply ask some questions and gather the answers given by the user to submit data that normally would be filled in on a form.
    • Example use cases are creating support tickets, uploading SharePoint documents, making calendar appointments, and providing translations.

  • Entertainment & education

    • A bot can be also used to entertain and educate its recipients by sending various kinds of content to the user.
    • It’s a good idea to use media types like videos, audio, images and links to knowledge base articles.
    • Example use cases are workout coach, recipes book and product adviser.

  • Notification bot

    • A bot can be scheduled to initialize conversations at appropriate time, notifying the user about some actions or reminding about things he should do.

    • It’s important to remember that sending proactive messages is not always possible – it depends on the channel used for communication.

    • Example use cases are meeting reminders and timesheet reminders.

I try to avoid the term “intelligent bots” because we’re at least 2 or three generations away from that.  But it’s definitely worth getting your hands dirty with them today, at least to learn their limitations.

Comments closed

Migrating Excel Power Pivot Models To SSAS

Published 2018-04-16 by Kevin Feasel

Imke Feldmann has a walkthrough to show how to migrate a Power Pivot model in Excel into SQL Server Analysis Services by way of Power BI:

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher.

Click through for a list of operations and a video showing how it’s done.

Comments closed

Checking For Temp Table Existence

Published 2018-04-16 by Kevin Feasel

Wayne Sheffield offers a clinic on temp tables:

I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code:

Seeing this takes me back to one of my favorite presentations, where I compare Temporary Tables and Table Variables. In this, I go over several of the methods that I have seen for how code found on the internet actually does this task… and I show why they are all doing it wrong.

Read on to understand why this isn’t the correct answer.

Comments closed

Verifying SSIS Database Connections With ssisUnit

Published 2018-04-16 by Kevin Feasel

Bartosz Ratajczyk shows how to test project-level connections in SQL Server Integration Services with ssisUnit:

Previously we successfully prepared tests for variables and parameters using VariableCommandand and ParameterCommand. Now it’s time to communicate with the database, and for that, I will use connection manager defined on the project level. I know from the ssisUnit tutorials it works perfect with package connection managers, so it’s time to verify it against the projects. I will test the package 10_ProjectCM.dtsx – it is just getting a single value from the table in a database and storing it in a variable. All the packages and unit tests are on my GitHub.

The package contains three SQL Tasks: the first just checks if we can communicate with the database using SELECT 1 statement, the second gets the information from the table, and the third repeats the second on the container level.

Click through for the tests.

Comments closed

Optimizing SSIS Throughput With Buffer Properties

Published 2018-04-16 by Kevin Feasel

Andy Leonard explains how he uses data flow properties to tune SQL Server Integration Services package performance:

I started answering a question on SQL Community Slack’s #ssis channel and I realized this would be better served as a blog post. The question was about three SSIS Data Flow properties: DefaultBufferSize, Engine Thread and DefaultBufferMaxRows.

I rarely change the EngineThreads property.

DefaultBufferSize and DefaultBufferMaxRows are two ways of managing the size limits of a Data Flow buffer. The two Data Flow Task properties can – and should – be treated as a single property. DefaultBufferSize is the number of bytes per buffer. DefaultBufferMaxRows is the number of rows per buffer. The defaults are 10,485,760 (10M) and 10,000, respectively.

Click through to learn more about these properties.

Comments closed

Optimizing Powershell Module Load Times

Published 2018-04-16 by Kevin Feasel

Chrissy LeMaire explains ways that the dbatools team reduced how long it takes to load their module:

We noticed that the longest part of importing the module was importing all the extra SMO DLL’s that we require for many of the commands. We import about 150 DLLs and it looks like that number will only grow as we begin to support more functionality (such as Integration services, etc.)

To address this concern, Fred added multi-threading via runspaces to our import process. Too cool! This resulted in a significant decrease in time.

Read on for more details; some of these tips might work on other slow modules, too.

Comments closed