Press "Enter" to skip to content

Month: October 2020

View Native Analysis Services Queries in Power Query

Chris Webb gives us an update on Power Query:

If you’re familiar with the topic of query folding in Power Query, you’ll know that the View Native Query right-click option in the Applied Steps pane of the Power Query Editor can be used to show the native query that is run against the data source. You may also know that there are some data sources where query folding does take place but where View Native Query remains greyed out.

Read on to see which sources are now available and to see an example of this in action.

Comments closed

ADF and Self-Hosted Integration Runtime Config Errors

Teo Lachev points out a common issue with using the Azure Data Factory self-hosted integration runtime:

You’ve set up the Azure Data Factory self-hosted integration runtime to access on-prem data sources. You create a linked server, click Test Connection, and then get greeted with an error saying the security context can’t be passed. On the on-prem VM, you use the Integration Runtime Configuration Manager and get a similar error or something to the extent that JSON can’t be parsed. You spent a few hours in trying everything that comes to mind, such as checking firewalls, connectivity from SSMS, but nothing helps.

Read on for the solution.

Comments closed

Time Series Forecasting in R

Selcuk Disci contrasts a couple of methods for time series forecasting:

It is always hard to find a proper model to forecast time series data. One of the reasons is that models that use time-series data often expose to serial correlation. In this article, we will compare k nearest neighbor (KNN) regression which is a supervised machine learning method, with a more classical and stochastic process, autoregressive integrated moving average (ARIMA).

We will use the monthly prices of refined gold futures(XAUTRY) for one gram in Turkish Lira traded on BIST(Istanbul Stock Exchange) for forecasting. We created the data frame starting from 2013. You can download the relevant excel file from here.

Click through for the demonstration. H/T R-Bloggers.

Comments closed

Delayed Durability in SQL Server

Esat Erkec walks us through Delayed Durability in SQL Server:

In this article, we will learn the Delayed Durability feature that helps to improve transaction log file write throughput in SQL Server.

OLTP (Online Transaction Processing) databases should process a huge number of transactions within the shortest time and concurrently. Therefore, the transaction completion time becomes more important for the performance of the OLTP databases. Particularly for SQL Server, the transaction log (T-log) file configuration will play a key role in the performance of the transaction completion times because the write throughput to the log file directly affects the application response times.

This is a feature which might be useful in specific scenarios, but I’m always concerned about that risk of data loss.

Comments closed

Creating a Power BI Workspace from Powershell

Martin Schoombee continues a series on automating Power BI deployments:

There are two things to keep in mind when creating workspaces in an automated fashion:

– The workspace may already exist. When you delete a workspace, it isn’t really deleted…only its status changes to “deleted”. That’s great if you want to restore a workspace you’ve accidentally deleted, but your code will have to account for it or it will fail.
– Whether you are creating or restoring a workspace, you have to provide an account to be the new administrator in the workspace. This ensures that the workspace is accessible, and by design a workspace cannot exist without an administrator.

Read on for the code.

Comments closed

Efficient Polling of Remote Data Sources

Ed Pollack looks at ways of optimizing linked server connections to remote SQL Server and Postgres instances:

Data rarely resides in one place. Oftentimes, there is a need to collect data from many different data sources and combine them locally into meaningful reports, analytics, or tables. The process for accessing, collecting, validating, and using data from remote data sources requires the same level of design and architectural considerations as building data structures for a software application.

In this article, methods of accessing remote data will be introduced with the goal of presenting best practices and ways to optimize load processes. This will lead into a discussion of performance and how to avoid the latency often associated with loading data from remote locations.

Click through for the article.

Comments closed

Refresh a Dataset when Dataflow Refresh Completes

Matthew Roche points out an API update:

Back in August I highlighted the new dataflows PowerShell script repo on GitHub. These scripts provide an accelerated starting point to working with the dataflows REST APIs and to automate common dataflows tasks.

This week the dataflows team has released two new REST APIs for managing dataflows transactions (think “refresh”) and a new parameterized PowerShell script to make using the APIs easier.

Read on to see how you can use these new APIs to trigger a dataset refresh once a dataflow refresh has completed.

Comments closed

Color Combination Testing in Powershell

Jeffrey Hicks has a script to test out color combinations in Powershell:

A lot of my PowerShell work lately has involved color. I find myself using ANSI escape sequences quite often. I’m also playing with different color schemes in Windows Terminal. And I still on occasion find myself using Write-Host to display colorized messages. What has gotten trickier is that Windows Terminal schemes can redefine colors. What I am used to as Green may not actually be Green. I polished up a simple script, to display all possible combinations for the console colors.

Read on for the script, as well as some oddities with Windows Terminal.

Comments closed

What Makes for a Good Estimator?

Jasmine Nettiksimmons and Molly Davies explain what estimators are:

What makes a good estimator? What is an estimator? Why should I care? There is an entire branch of statistics called Estimation Theory that concerns itself with these questions and we have no intention of doing it justice in a single blog post. However, modern effect estimation has come a long way in recent years and we’re excited to share some of the methods we’ve been using in an upcoming post. This will serve as a gentle introduction to the topic and a foundation for understanding what makes some of these modern estimators so exciting.

Read on for a very nice introduction to the topic.

Comments closed