A Fine Slice Of SQL Server
Cross-database ownership chaining is a SQL Server security feature allowing database users access to other database objects hosted on the same SQL server instance, in the case where database users don’t have access granted explicitly
Tom shows us whether it is on as well as how to enable it. I’d recommend not enabling it at all and using module signing instead.
Comments closedMarco Russo troubleshoots an issue:
There are cases where the dictionary size reported by VertiPaq Analyzer (used by DAX Studio, Bravo for Power BI, and Tabular Editor 3) does not correspond to the actual memory required by the dictionary. However, the number reported is technically correct because it represents the memory currently allocated for the dictionary. The issue is that – after a refresh – this memory amount is larger than the actual memory required for hash-encoded columns.
Read on to learn what the consequences are and how you can resolve this in Power BI Desktop as well as in Analysis Services.
Comments closedEstera Kot has an announcement:
We are excited to announce the preview availability of Apache Spark™ 3.3 on Synapse Analytics. The essential changes include features which come from upgrading Apache Spark to version 3.3.1 and upgrading Delta Lake to version 2.1.0.
Check out the official release notes for Apache Spark 3.3.0 and Apache Spark 3.3.1 for the complete list of fixes and features. In addition, review the migration guidelines between Spark 3.2 and 3.3 to assess potential changes to your applications, jobs and notebooks.
There’s a lot in there, though I did snicker a bit at log4j 2 being more secure than log4j v1 given what we saw last year, though that gaping hole was fixed.
Comments closedTibor Karaszi puts columnstore ducks in a row:
Data for a columnstore index is divded in groups of approximate 1 million rows, rowgroups. Each rowgroup has a set of pages for each column. The set of pages for a column in a rowgroup is called a segment. SQL Server has meta-data for the lowest and highest value for a segment. There are no SEEKs in a columnstore index. But, SQL Server can use this meta-data to skip reading segments, with the knowledge that “this segment cannot contain any data that I need based on my predicates in my WHERE clause”.
Also, you might want to do these operations using MAXDOP 1, so we don’t have several threads muddling our neat segment alignment.
I’m not sure I actually set the ORDER BY clause on columnstore indexes all that often—a quick mental survey says maybe once, though that could be my own failing rather than a statement on the utility of ordered columnstore indexes.
Comments closedJeffrey Wang is speaking my language:
The December 2022 release of Power BI Desktop includes three new DAX functions: OFFSET, INDEX, and WINDOW. They are collectively called window functions because they are closely related to SQL window functions, a powerful feature of the SQL language that allows users to perform calculations on a set of rows that are related to the current row. Because these functions are often used for data analysis, they are sometimes called analytical functions. In contrast, DAX, a language invented specifically for data analysis, had been missing similar functionalities. As a result, users found it hard to write cross-row calculations, such as calculating the difference of the values of a column between two rows or the moving average of the values of a column over a set of rows.
Read on to learn more about how these functions work and how they differ from their SQL Server counterparts.
Comments closedMatthew McGiffen shares some thoughts:
We looked earlier at the various places our data lives – on disk, in memory, in-transit across the network. A good strategy will protect all of these locations – sometimes with multiple layers. Exactly how you go about doing that for your applications may vary – but as long as you are on SQL Server 2016 or higher there is a default strategy that you should consider. This combines a number of the available SQL Server features to provide the best protection.
This story might look a bit different for Azure SQL Database (though you can still use Always Encrypted there) and will look very different on Azure Synapse Analytics dedicated SQL pools, which don’t have Always Encrypted at all.
Comments closedAndy Jones has some good news for us:
Microsoft recently announced that Power BI Paginated Reports is now available to users with a Pro licence. Previously, you required a Premium licence to take advantage of Paginated Reports in Power BI.
Read on to learn how paginated reports work in Power BI (hint: almost like SSRS). Even with Power BI Premium Per User being a fairly reasonable $20 per user per month, it’s nice to see Pro get a bit more value.
Comments closedMeagan Longoria adds a bit of pizazz:
People have done creative things with SVG measures in Power BI, ranging from KPI cards to infographics to fun games.
For my latest Workout Wednesday challenge, I used SVG measures to make holiday cards that open on a specified date.
Click through to see how Meagan pulled it off.
Comments closedAdi Polak demonstrates lakeFS:
This tutorial demonstrates how to build a development and testing environment for validating your logic on a full-blown production data volume and variety, working with lakeFS and Spark. You will walk through the journey of creating a repository and building a Spark application while using lakeFS capabilities. You will learn how to data changes, revert them in cases of mistakes or other hiccups, and lately merge separate branches to reflect data changes from the isolated environments.
Not too long ago, I had a couple conversations with developers and data engineers about decentralized development and devs having their own environments and data. This seems like it would be a good approach to that common problem, and it works for Azure Synapse Analytics as well.
Comments closedTomaz Kastrun continues an advent of Azure Machine Learning. Day 13 covers the topic of Automated ML:
Automated ML is a no-code automated machine learning task. It iterates over many combinations of algorithms and hyperparameters in order to find the best model for your dataset and your prediction variable(s). The final solution is a model, that can be downloaded and later reused. So Automated ML is not just giving you the best model out of a family of algorithms, but lets you use the model, generate the scripts and create the artefacts.
Day 14 concerns model registration:
Comments closedImportant asset is the “Models” in navigation bar. This feature allows you to work with different model types -> custom, MLflow, and Triton. What you do here is, you register a model from different locations (e.g.: local file, AzureML Datastore, AzureML Job, MLflow Job, Model asset in AzureML workspace, and Model asset in AzureML Registry).
Once you open the Models asset, you will see, that you can do many things here. I have already model register from the running the notebook on day4.