A Fine Slice Of SQL Server
Marco Russo diagnoses an error:
If you try to use SELECTEDVALUE on the visible column of the table generated by the Fields Parameters feature in Power BI, you get the following error:
Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.
Read on to understand what causes this error and what you can do to resolve it.
Comments closedTzvia Gitlin Troyna has a flow chart for us:
Azure Data Explorer is a big data interactive analytics platform that empowers people to make data driven decisions in a highly agile environment. The factors listed below can help assess if Azure Data Explorer is a good fit for the workload at hand. These are the key questions to ask yourself.
The following flowchart table summarize the key questions to ask when you’re considering using Azure Data Explorer.
In addition to the flow chart, there is a table of three common patterns of interaction which ADE can do well.
Comments closedBrett Powell thinks about first principles:
Data and analytics languages should be prioritized far beyond graphical interface tools/software/services and should form a solid foundation of a skillset. Unlike software applications and various user interface controls which change frequently, the essential concepts and semantics of data languages such as SQL and DAX don’t change nearly as frequently and thus languages offer a much greater return on the time invested to learn them. For example, the fundamental PowerShell scripting knowledge I built up years ago using the Windows PowerShell ISE can still be applied today in many different tools, apps, and services that weren’t around back then such as Azure Function Apps and Visual Studio Code.
In almost every BI project I can remember, even projects that were explicitly intended to use low-code or no-code tools, it was the combination of different languages such as SQL, DAX, Kusto (KQL), Power Fx, and others that delivered the most value or which made the difference between project success and failure. Similarly, even in projects in which my role was intended to exclusively focus on the data model layer with DAX, I’ve almost always found myself also writing SQL, Power Query (M) and using other languages and code either in the data warehouse or on the reporting layer.
Brett has put a lot of thought into this and I think many of the principles apply outside of business intelligence work as well.
Comments closedErik Darling points out an important concern with GENERATE_SERIES:
These table valued functions of the built-in variety have this problem.
This one is no exception. Well, it does throw an exception. But you know.
That’s not exceptional.
Click through for a demonstration of the problem. Hopefully this gets sorted out prior to GA.
Comments closedChris Webb describes an important setting:
If you’re working with DirectQuery in Power BI then one of the most important properties you can set on your dataset is the “Maximum connections per data source” property. You can find it on the Published Dataset Settings tab in the Options dialog in Power BI Desktop:
Read on to see what makes this so important.
Comments closedAnthony Nocentino takes us through an example of integrating with MinIO via its S3 integration:
In this post, I will walk you through how to set up MinIO, so you can use it to work with SQL Server 2022’s s3 object integrations. Working with s3 and SQL Server requires a valid and trusted TLS certificate. This can be a pain for some users and environments. So I’m writing this post so you can get off the ground running with this new feature set in SQL Server 2022. The certificate we’re working with here is self-signed. You could get a real certificate for your environment, and that’s encouraged. But this walk-through intends to get you up and running fast so that you can test out SQL Server’s s3 object integrations. We’re using MinIO’s free GNU AGPL v3 edition and running it in a docker container for our s3 compatible object storage and SQL Server 2022 CTP 2.0, which is also running in a container.
Click through for the demo, in which Anthony sets everything up and then backs up a database in SQL Server 2022 to MinIO.
Comments closedPaul Roome, et al, announce Unity Catalog:
We are excited to announce that data lineage for Unity Catalog, the unified governance solution for all data and AI assets on lakehouse, is now available in preview.
This blog will discuss the importance of data lineage, some of the common use cases, our vision for better data transparency and data understanding with data lineage, and a sneak peek into some of the data provenance and governance features we’re building.
Click through to see what it currently supports. My curious question is around whether this and Microsoft Purview will play nice in an Azure Databricks setup.
Comments closedTom Collins sets up column-level encryption:
How do I set up the SQL Server Column Level Encryption ?
This is quite different from Always Encrypted—it’s the encryption process which we saw with SQL Server going back (at least) to 2000. It’s also (currently?) the only available way manually to encrypt columns in an Azure Synapse Analytics dedicated SQL pool. I’d say it’s not trivial to set up and use but neither is it a huge challenge.
Comments closedErik Darling looks the gift horse in the mouth:
Quite a while back, I blogged about how much I’d love to have this as a function. We… sort of got it. It doesn’t do dates natively, but you can work around some of that with date functions.
In this post, I want to go over some of the disappointing performance issues I found when testing this function out.
It’s not good news but it’s important to understand if you’re planning to use this when SQL Server 2022 comes out. Also, the things Erik mentions are easier to fix (potentially) than modifications in the signature of a function, as they’re internal. I wouldn’t guarantee that things will certainly be better but there are some good cases when we saw performance improvements between early CTPs and RTM.
Comments closedArun Sirpal explains the benefit behind scale sets in Azure:
I really like scale sets. It lets you create and manage up to 1000 load balanced VMs per availability zone using windows or Linux images. (We can have flexible or uniforms modes for orchestration which dictates if you go down the homogenous VM route or a mix, where a mix is the flexible option.
There are many other benefits too apart from scaling, such as built-in load balancing options, increased resiliency via 3 Availability Zones and from a cost perspective you can couple scale sets with Azure Hybrid benefit or even use reserved instances – cost is important in the cloud!
Read the whole thing.
Comments closed