A Fine Slice Of SQL Server
Databricks natively stores it’s notebook files by default as DBC files, a closed, binary format. A .dbc file has a nice benefit of being self-contained. One dbc file can consist of an entire folder of notebooks and supporting files. But other than that, dbc files are frankly obnoxious.
Read on to see how to convert between these two formats.
Comments closedJamie Dixon walks us through scraping a webpage using F#:
I need to go through all 8 pages of the grid and download the .pdfs that are associated with the “View Report” link. The challenge in this particular site is that they didn’t do any url parameters so there is no way to go through the grid via the uri. Looking at the page source, they are using ASP.NET and in typical enterprise-derpy manner, named their table “GridView1”
The way to get to the next page is to press on the “Next” link defined like this:
They over-achieved in the bloated View State for a simple page category though.
#Sigh
The code is straightforward and available as a Gist in the post.
Comments closedLeila Etaati takes us through time series anomaly detection with Cognitive Services and Power Query:
I am excited about this blog post, this is based on the New service in Cognitive Service name “Anomaly Detection” which is now in Preview.
I recorded a video about how it works in cognitive service https://youtu.be/7ZOtZDbn6gM.However, I am going to talk about how to use it in Power BI. In this post first, a brief introduction to the anomaly detection will be presented, then how it can be used inside Power BI will be discussed.
It sounds like there are still some rough edges, but they already have the makings of an interesting service.
Comments closedHasan Savran builds an Azure Data Studio notebook to query SQL Server from Python:
SQL Kernel is the default language, to query database with Python change SQL to Python 3. Probably, you will see the following message if this is the first time you are trying this. You need to install Python packages to be able to run python scripts. I have Visual Studio installed on my machine and I already have Python, I taught I could to use it by clicking “Use existing Python installation”. I was wrong, I couldn’t. This option looks for local installation files and when I point to Visual Studio Python files, it throws error in the middle of the installation. So, I will ignore this option for now.
In ADS, I haven’t gotten “Use existing Python location” to work either, so Hasan’s not alone in that regard.
Comments closedTomaz Kastrun reviews the different DAX functions related to time in Power BI:
New functions are coming to DAX (Data Analysis Expression Language) regularly, and just recently, the function QUARTER was added. And this gave me thinking; why not do a standardized DAX, to be added as a “time dimension” to every Power BI, that I create.
Click through for that standardized time dimension.
Comments closedCathrine Wilhelmsen continues a series on Azure Data Factory. Catching up from the last time around, we first see the Copy Data activity:
You can copy data to and from more than 80 Software-as-a-Service (SaaS) applications (such as Dynamics 365 and Salesforce), on-premises data stores (such as SQL Server and Oracle), and cloud data stores (such as Azure SQL Database and Amazon S3). During copying, you can define and map columns implicitly or explicitly, convert file formats, and even zip and unzip files – all in one task.
Yeah. It’s powerful 🙂 But how does it really work?
But… please, please, please don’t use “source” or “destination” or “sink” or “input” or “output” or anything like that in your dataset names. It makes sense when you have one pipeline with one copy data activity, but as soon as you start building out your solution, it can get messy. Because what if you realize you want to use the original destination dataset as a source dataset in another copy data activity? Yeah… 🙂
So! Let’s rename the datasets.
After that, it’s on to linked services:
Azure Key Vault is a service for storing and managing secrets (like connection strings, passwords, and keys) in one central location. By storing secrets in Azure Key Vault, you don’t have to expose any connection details inside Azure Data Factory. You can connect to “the application database” without directly seeing the server, database name, or credentials used.
Cathrine is rolling with this series and it’s been great so far.
Comments closedErin Stellato explains how Query Store works when you have encrypted stored procedures:
There are two issues here:
– Why are only some plans showing up in Query Store for a query that’s part of an encrypted stored procedure?
– Can I force a plan for a query that’s part of an encrypted stored procedure?When in doubt, test.
Read on for the results.
Comments closedJoost van Rossum has a pair of posts on Azure DevOps updates. First, Azure DevOps supports building SSIS projects:
This new task is much easier to use than the PowerShell code and also easier than most of the third party tasks. With a little practice you can now easily create a build task under two minutes which is probably faster than the build itself.
If your build fails with the following error message then you are probably using a custom task or component (like Blob Storage Download Task). These tasks are not installed on the build agents hosted by Microsoft. The solution is to use a self hosted agent where you can install all custom components
Second, Azure DevOps supports deploying SSIS projects:
Microsoft just released the SSIS Deploy task (public preview) which makes it much easier to deploy an SSIS project. Below you will find the codeless steps to deploy artifacts created by the SSIS Build task.
Click through for the step-by-step instructions for each.
Comments closed