Press "Enter" to skip to content

Author: Kevin Feasel

Syntax for Scripting Calculation Groups

Published 2022-02-01 by Kevin Feasel

Marco Russo and Alberto Ferrari are linguists:

When calculation groups were introduced in 2019, we did not have a way to describe them in a textual form. A calculation group was represented as a table with one visible column and one or more rows, one for each calculated item. Each calculation item could have one or two DAX expressions associated with it – one for the calculation item itself and an optional one for the format string. Describing a calculation group in an article often required the writer to include screenshots of the Tabular Editor user interface, plus comments in the sample code to explain where each DAX expression should be placed in the user interface.

From the start we proposed a syntax to describe an entire calculation group in a textual form. However, there was no tool able to convert that syntax into the actual object in the Tabular model. For this reason, in the initial version of the articles about calculation groups we used a “pseudo-syntax” and we included comments that made the code more verbose and not necessarily easier to read. However, Tabular Editor 3 introduced the full DAX script syntax for calculation groups that we had hoped would be available in 2019. We decided to adopt that syntax in our content. We use this article as a guide to introduce and explain the DAX Script syntax for calculation groups.

Go check it out.

Comments closed

Power BI Workspace Permissions

Published 2022-02-01 by Kevin Feasel

Marc Lelijveld continues a series on Power BI workspace configuration:

After Power BI Workspace setup – part 1, which was mainly about creating your workspace, giving it the right name etcetera, this blog will elaborate on workspace permissions. If you did not read part 1 yet, I encourage you to start at that blog to get the basic setup in place.

You might think, permissions is an easy topic, but often underestimated! In this blog I will describe the different workspace roles, and how you can apply them in your project. Also we will look at sharing specific content from a workspace perspective, such as sharing dataflows.

Read on to learn more.

Comments closed

Disabling Join Types Globally

Published 2022-02-01 by Kevin Feasel

David Alcock shows us the button not to press:

A while ago I presented a session which covered transformation rules that are used by the query optimiser to produce our execution plans. I’m not feeling in the mood for relational algebra this morning so instead I’ll introduce a command that can cause mayhem on an instance SQL Server: DBCC RULEOFF.

DBCC RULEOFF is an undocumented command, that alone makes me want to use it but in order to cover one’s backside please don’t do any of the following in any environment apart from a disposable sandbox that only you use for weird and wonderful experiments in SQL Server, because we are going to break it…

Read on for something you can but should not do.

Comments closed

A Free Power BI Sandbox

Published 2022-01-31 by Kevin Feasel

Reza Rad has the right price in mind:

A question I often get from many students is: “How can I practice Power BI service features if I do not have a Power BI Account?”. Not having a Power BI account can happen because of many scenarios; your company might close this option so that the process be only channeled through a specific process within the company. Or you may not have the permission to do so. Not having an account makes it difficult to practice Power BI Service options such as workspace, datasets, dashboards, dataflows, apps, and many other features. On the other hand, even if you have the Power BI Service account, in most of the organizations, you are not the service administrator, so you cannot practice tenant-settings configurations in the service.

Fortunately, there is a way to create your own Power BI sandbox; which means an environment just for yourself, with 25 accounts. You will be the administrator of your environment. The environment will be up for at least 90 days, and you can practice whatever you want for the Power BI service there. The best of all, it is FREE. You don’t have to pay a cent for it. Credit card detail is not needed. What better you could wish for?

Read on to see how.

Comments closed

Using SQL Server Vulnerability Assessments

Published 2022-01-31 by Kevin Feasel

Manvendra Sing takes a look at SQL Server’s vulnerability assessment tool:

I will explain how to use and perform security testing using SQL Server vulnerability assessment in this article. I have explained basic understanding about multiple layers of security that we configure to protect our SQL Server instances in my last article. I have also explained about security testing at each layer we should perform to understand how secure our systems are. I would request you to read this article, Understanding security testing for SQL Server environments to learn more about them.

Security is a very critical area for any database environment. Database security measures help an organization to protect its data to maintain its privacy and integrity. Security testing should be done regularly to ensure all security policies are properly configured to protect the systems.

SQL Server also offers various features using which we can perform security testing for our databases. One of such features is SQL Server vulnerability assessment. This feature scans the database for which you are running it and displays all weaknesses along with their probable solutions.

Read on to see how you can run a vulnerability assessment, the types of results you can get, and how you might detail with some common issues.

Comments closed

Script Parsing with ScriptDOM

Published 2022-01-31 by Kevin Feasel

Mala Mahadevan continues a series on ScriptDOM:

In the last post I wrote about what ScriptDOM is and why it is useful. From this post, I will explain how it can be put to use. What it does when you pass a script to it is to parse it, check if it is free of syntax errors, and build what is called an ‘Abstract Syntax Tree’, which is a programmatic representation of the script, with nodes and branches for each code element. The rest of the usage/functionality is built around the Abstract Syntax Tree. So in this post let us look into how this is accomplished.

Read on to see what you need to do.

Comments closed

Quickly Finding Row Counts for SQL Server Tables

Published 2022-01-31 by Kevin Feasel

Jeff Iannucci doesn’t have time for a table scan:

Have you ever had to find the number of rows in a user table, and then wrote a little “SELECT COUNT(*) FROM tblWhatever” and hit execute…and waited…and waited…and waited some more? And then started wondering what was going on?

If so, this post is for you.

Read on for a stored procedure which gets a nice estimate of the total number of rows. I tend to have a form of the underlying query saved as a snippet so I can use it easily. One thing to keep in mind is that these stats-based counts can be wrong. It’s rare and typically has to do with page splits duplicating values, but on a very large, very busy table you might occasionally be off by a few rows. I might posit in return that on such a table, if precision is that important, the amount of time between querying the stats and doing something with it will probably also cause you to see a difference in row counts anyhow.

Comments closed

Reviewing Power BI Query Sessions with Log Analytics

Published 2022-01-31 by Kevin Feasel

Chris Webb continues a series on Power BI monitoring:

In my last post I showed how to use Log Analytics data to analyse Power BI query activity. The problem with looking at a long list of queries, though, is that it can be overwhelming and it can be hard to get a sense of when users were and weren’t actively interacting with a report. In this post I’ll show you how you can write a KQL query that gives you a summary view that solves this problem by grouping queries into sessions.

Click through to see what Chris means by the term “session” and for the KQL to do the job.

Comments closed

Building Packages from Base R Files

Published 2022-01-28 by Kevin Feasel

John Nash and Arkajyoti Bhattacharjee package things up:

This article tries to explain an approach to developing alternative versions of functions which are in the distributed base of R. Our interest was in developing improvements to the nls() function and related features in R as part of a Google Summer of Code project for which Arkajyoti Bhattacharjee was the funded student. However, nls() has many tentacles involving a number of files and functions that may or may not be called as nls() is executed.

Part of the difficulty in carrying out such development of alternative versions is that one needs to be able to execute the new variants in parallel with the existing ones. A heavy-effort approach would be to have separate full sets of R code and build each system and run them separately. That is, we want to have two or more versions of R in the same computing system.

Read on for the process, some difficulties you might encounter along the way, and specific issues you might run into on Windows. H/T R-Bloggers.

Comments closed