tabulareditor / bestpracticerules Goto Github PK

The Rule "Avoid CamelCase on visible measures and tables" (BPA-Standard) is checking for the first letter being upper case. That would be PascalCasing and not camelCasing. in camelCasing the first letter is lower case.

This rule would check for null descriptions and for those found copy the Expression into the Description.
For deployed models, this would be a starting point to give visibility to users of what calculations are being made in the measure, calculated column, or calculated table.

Some DAX functions require the IsAvailableInMDX property to be true, though documentation as to which functions require it is poor.

In order for the function PATH to work, both the parent and child column it references must have this property set to true. I've added one additional filter to the expression to exempt such columns from being flagged.

Relevant rule addition:
and not ReferencedBy.AllColumns.Any(Expression.Contains(\"PATH(\"))

Full rule definition:

  {
    "ID": "DISABLE_ATTRIBUTE_HIERACHIES",
    "Name": "Disable attribute hierachies to decrease processing",
    "Category": "Metadata",
    "Description": "Disable Attribute hierarchies for hidden collumns. This will ensure faster processing. Exclude columns used in PATH function.",
    "Severity": 2,
    "Scope": "DataColumn",
    "Expression": "not IsVisible\r\nand IsAvailableInMDX\r\nand not UsedInHierarchies.Any()\r\nand not UsedInVariations.Any()\r\nand not UsedInSortBy.Any()\r\nand not ReferencedBy.AllColumns.Any(Expression.Contains(\"PATH(\"))",
    "FixExpression": "IsAvailableInMDX = false",
    "CompatibilityLevel": 1400,
    "Source":  "standard\\Metadata"
  }

From @marcosqlbi on November 8, 2017 2:30

If a user belongs to two roles applying filters on the same table with row level security (RLS) in one role, and object level security in another role (OLS), the connection is not possible by design.

you can’t have RLS and OLS on the same table in different roles – that would prevent the connection from being opened

A Best Practices Analyzer rule could identify the presence or roles applying different filter types (RLS/OLS) over the same tables. If the developer knows that this will never happen (because a user cannot belong to two incompatible roles), there should be a way to disable the BPA (what could we use for that? Special annotations?).

Copied from original issue: TabularEditor/TabularEditor#113

Hi,

Is it possible to exclude comments in calculated items (measures, columns, tables etc.)? We use them allot to give some extra context when needed.
For example

SUMMARIZECOLUMNS (
'Sales'[Posting Date],
'Sales'[Customer No],
'Sales'[Item No],
'Sales'[VAT Rate],
'Sales'[Currency Code],
/* Makes sure that issues with currency conversions are filtered out. */
FILTER ( 'Sales', 'Sales'[Selling Price (EUR)] < 1000 )
)

The "Remove unused columns" rule returns the following error:
No property or field 'Dependants' exists in type 'DataColumn'

I tried this on both 1200 and 1400 compatibility levels, and I still get the same error.

Other rules do not have such issues.

Am I doing something incorrectly?

hI
When running the Tabular Editor by command line and using the Best Practice anlayser
I get this
Deployment failed! DAX comparison operations do not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

I understand the error its just I was unable to tie this back to the rule that triggered it - couldnt see it referenced in Dev ops
We are downloading the latest version of Tabular Editor each time using a ps script - I do know the build version - Tabular Editor 2.12.4 (build 2.12.7563.29301)

I made sure all the rules from the git hub site were up to date but i coudnt see this rule
Could you assist?
Ronan

Your DBA's will love it. It will help them to find heavy queries.

I'm not sure if this is a more of a BPA feature suggestion or a BPA Rule suggestion, but I think this could be done in the rule expression. We are utilizing Git for our CI/CD process and we invoke TE3 via CLI in Git to run the BPA to check before we allow a PR to be created or merged.

Because each model is different and will therefore have it's own exceptions to the rules, can we put every existing rule's LINQ script inside of a "wrapper" that checks to see if the rule should apply to this particular model and then exit if it doesn't?

//Wrapper Pseodo-code
IF Model.Name IN (array of model's to be ignored for this rule)
EXIT
ELSE
run regular LINQ script
END IF

Hi Daniel,
I'm impressed by your presentation of Tabular Editor. It's definitely a key tool in Power BI environment. Congratulation again !
As you know, I work on automatic translations and I got already some good results. So I was very interrested in the rule about "Translate display folders" but I think there is a bug there. As you can see on the screenshot, the rule reports some translations are missing but in the column or measure the properties reports "0 empty".
Additionaly, after I added the standard rules as you demonstrated in the video, opening the bim file takes now a lot of time. The bim file is attached

Thanks in advance

PBI translations.zip

Every visible measure should have format string. Automatic number of decimals is a warning, because it is likely to be wrong.

is there a way to create a rule to detect which fact table columns generate a referential integrity on any particular dimension?
Looks like the Vertipaq Analyzer provides the count of RI's for each dimension, but no info on the offending fact tables.

Also, trying to use this query below, also does not surface the fact tables:
select
[Database_name] ,
[Dimension_Name] ,
[RIVIOLATION_COUNT]
from $SYSTEM.DISCOVER_STORAGE_TABLES
WHERE
[RIVIOLATION_COUNT] > 0

thank you kindly,
Cosmin

Good morning,

Firstly, I really like the effort the communities to elevate the Power BI development level. Keep up the good job.
I'm trying for the first time the BPA and struggling with the rule "Hide foreign key columns". I'm also new to LINQ.
I've paste the below formula found here (https://docs.tabulareditor.com/Best-Practice-Analyzer.html#fixing-objects)
"Model.Relationships.Any(FromColumn = outerIt) and not IsHidden and not Table.IsHidden"
and I got the error message "Operator '=' incompatible with operand types "Column" and Model""

Thanks in advance for letting me know.

Kind regards,
Lohic Beneyzet

Use names such as "Sales Amount" instead of "SalesAmount"

{
"ID": "DISABLE_AUTO_TIME_INTELLIGENCE",
"Name": "Disable auto time intelligence",
"Category": "Performance",
"Description": "Remove hidden auto date/time tables and disable the Current File's Auto date/time."
"Severity": 3,
"Scope": "Model",
"Expression": "Tables.Any(Name.StartsWith("LocalDateTable_"))"
"FixExpression": "__PBI_TimeIntelligenceEnabled = 0"
}

Hi

The rule Disable Attribute Hierarchies should have a minimum compatibility level of 1470 as this is only available in SSAS 2017 after SP2

{
"ID": "DISABLE_ATTRIBUTE_HIERACHIES",
"Name": "Disable attribute hierachies to decrease processing",
"Category": "Metadata",
"Description": "Disable Attribute hierarchies for hidden collumns. This will ensure faster processing.",
"Severity": 2,
"Scope": "DataColumn",
"Expression": " IsHidden \n\nand (IsAvailableInMDX == TRUE)\n\nand (not UsedInHierarchies.Any())",
"FixExpression": "IsAvailableInMDX = false",
"CompatibilityLevel": 1470
}

Columns and calculated columns must have a datatype other than 'Any'.

I think this rule should be restricted to only work on numeric columns, as the rule name implies.

Also, hidden tables should be excluded. There is already a condition to exclude hidden columns, but this is not sufficient, because columns in hidden tables are marked as visible unless explicitly hidden (which is redundant).

I think this issue can be solved by using the following rule expression:

IsHidden = false

and Table.IsHidden = false

and SummarizeBy <> "None"

and (DataType = "Double" or DataType = "Decimal" or DataType = "Int64")

I'm running the BPA on a variety of models, and looking to create a living catalog of violations, fixes, exceptions/ignores. A simple 'export to Excel' feature may help, so that models owners can make annotations when fixes are applied, exceptions are made or rules are added/dropped. An inline tracking/cataloging feature within Tabular Editor might fit the bill too, but likely a taller order.

Define Detail Rows if a measure depends on one or more other measures that have Detail Rows property defined.

Hi

I need to create a rule to check whether a filter condition has been used in calculate to filter a column.

Lets say there is a fact table called Sales and it has an amount column and another column for category which has 4-5 different categories in it. Let’s say I would like to calculate sales amount for a certain category from the category column. There are 2 ways of writing a measure that filters a category column for a certain condition:-

Measure 1 = Calculate(Sum('Sales'[Amount]),'Sales'[Category]="Red")
Measure 2 = Calculate(Sum('Sales'[Amount]),Filter(' Sales ', Sales '[Category]="Red"))

Both measure are correct but measure 2 takes longer time and will eventually slowdown visual rendering. As per marco russo filter function shall never be used for filtering a column. I need to create a rule that checks If any measure in the model is doing it if yes than I would like to highlight it.

Regards

Currently set to diable auto date/time. > Should be Disable Auto Date/Time

I think this rule should not be applied to measures, because there is no way of changing the data type of measures by changing the metadata, as far as I'm aware.

In fact, if you try to apply a fix, you will get the following error:

Also, for clarity purposes, I would change the description from:

Floating point datatypes can cause unexpected results when evaluating values close to 0. Use Decimal instead.

To something like this:

Floating point datatypes can cause unexpected results when evaluating values close to 0. Use Currency / Fixed Decimal Number (decimal) instead.

Because in Visual Studio, the floating point data type is called Decimal Number, which could be confusing when people are instructed to "use Decimal instead".

"ID": "LAYOUT:_FORMAT_MEASURES_IN_ONE_OF_THE_SET_OF_PREDEFINED_FORMATS_FOR_THE_CUBE

",
"Name": "LAYOUT: Format Measures in one of the set of predefined formats for the cube",
"Category": null,
"Description": "Measure formatting should be one of these: currency no decimals, percentage with comma & 1 or 2 decimals, whole number with comma & no decimals, decimal with comma & 1 decimal.",
"Severity": 10,
"Scope": "Measure",
"ScopeString": "Measures",
"Expression": "(!FormatString.Equals("\$#,0;(\$#,0);\$#,0") \nand !FormatString.Equals("#,0.0 %;-#,0.0 %;#,0.0 %")\nand !FormatString.Equals("#,0.00 %;-#,0.00 %;#,0.00 %")\nand !FormatString.Equals("0.0 %;-0.0 %;0.0 %")\nand !FormatString.Equals("0.00 %;-0.00 %;0.00 %")\nand !FormatString.Equals("\$#,0.0;(\$#,0.0);\$#,0.0")\nand !FormatString.Equals("#,0.0")\nand !FormatString.Equals("0.0")\nand !FormatString.Equals("0")\nand !FormatString.Equals("#,0")) and not IsHidden",
"FixExpression": null,
"Compatibility": null,
"IsValid": false

The names of the columns involved in a relationships should be the same, unless there are more relationships to the same table (e.g. Order Date, Start Date).

Connection strings should not contain SQLOLEDB or SQLNCLI. It should be changed to MSSQLOLEDB. Source : https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-2017

Hi!

I have loaded and used BPA rules from Github previously but am unable to neither load rules through your C# snippet or add physical file in the tabular editor folder since lately. I have tried both adding the file through "Create new rule file" and "Include local rule file" to no avail.
No matter which route I take the BPA Rules JSON file in the program folder empties out with the exception of two brackets [ ] making the file 1kb small instead of the ~35kb size I add.

How can I fix this?

Hi Daniel,

I want to check if a column is sorted by another column. What expression would do it?
I've tried "IsHidden = false and sortByColumn <> null" but it did not work. Can you help me out here, please?

Thanks in advance.
Dominik

Measures comprised of a text value don't need a format string.

A rule that detects if a calculation item expression holds references the calculation group itself in the DAX expression (this indicates a sideways recursion which should be avoided).

Sounds stupid until you find out how many tables you have without a rowidentifier/key

See TabularEditor/TabularEditor#597

Hi

Hidden columns should have Avaliable In MDX disabled to optimize processing time
{
"ID": "DISABLE_ATTRIBUTE_HIERACHIES",
"Name": "Disable attribute hierachies to decrease processing",
"Category": "Metadata",
"Description": "Disable Attribute hierarchies for hidden collumns. This will ensure faster processing.",
"Severity": 2,
"Scope": "DataColumn",
"Expression": " IsHidden \n\nand (IsAvailableInMDX == TRUE)\n\nand (not UsedInHierarchies.Any())",
"FixExpression": "IsAvailableInMDX = false"
}

In TE 3 there is a feature which immediately warns me about any expression, which uses non fully qualified columns. Would be nice to have that in BPA as well.

"ID": "LAYOUT:_KEY_COLUMNS_SHOULD_BE_HIDDEN

",
"Name": "LAYOUT: Key columns should be hidden",
"Category": null,
"Description": "Key columns that are used as Primary and foreign keys should be hidden from clients.",
"Severity": 3,
"Scope": "DataColumn, CalculatedColumn, CalculatedTableColumn",
"ScopeString": "Columns",
"Expression": "(Name.ToLower().EndsWith("id") or Name.ToLower().EndsWith("key"))\nand not IsHidden",
"FixExpression": null,
"Compatibility": null,
"IsValid": false

I added the following Rule to my BPA. I don't want my users to connect to the model directly and instead use one of the perspective. Therefore I need to make sure that all measures and columns are added to at least 1 perspective.

I use the following:
IsVisible and Model.Perspectives.Any() and not InPerspective.Any(it)

The current rule does not take into account the following situations where a slash is, in my opinion, legitimate:

1. Comments (especially multi-line ones):
   My Measure := [$] /* This is just for illustration purposes */
2. Slashes inside text strings:
   Net Profit := CALCULATE ( [$], 'Measure Hierarchy'[Name] = "Net gain/(loss)" )
3. Slashes in field names (although issues #8 and #17 suggest that this should be avoided):
   Only Yes := CALCULATE ( [$], Dim[Yes/No] = "Yes" )
4. Division by constants
   Monthly Revenue := Sales[Yearly Revenue] / 12

I managed to address the first situation by adding one more condition:
and (not Expression.Contains("/*"))

Unfortunately, my Dynamic LINQ skills are close to nil, and I don't know how to address the other situations. Also, I understand that my solution to the first situation is not ideal because it excludes cases where there is both a multi-line comment and a genuine division that should be avoided.

Avoid using prefixes and camelCasing. Use "Sales" instead of "dimSales" or "mSales".

Here is a rule that I often find in models due to over normalization of tables. These generally crop up due to snowflaking in the underlying database schema. Note, I exclude hidden and key columns from the count.

{
    "ID": "NO_SINGLE_COLUMN_SINGLE_RELATION_TABLES",
    "Name": "No single column, single relation tables",
    "Category": "Performance",
    "Description": "Single column tables that only participate in a single relationship are unnecessary. Fold the attributes into the related table. Note, hidden and key columns are excluded.",
    "Severity": 1,
    "Scope": "Table",
    "Expression": "not IsHidden and Columns.Count(not Name.EndsWith(\"Key\")) = 1\nand Model.Relationships.Count(ToTable = outerIt) = 1",
    "FixExpression": null,
    "CompatibilityLevel": 1200
}

I'm not sure if this is META or PERF.