In my consulting expertise working with clients utilizing Energy BI, many challenges that Energy BI builders face are as a consequence of negligence to information sorts. Listed below are some widespread challenges which are the direct or oblique outcomes of inappropriate information sorts and information sort conversion:

• Getting incorrect outcomes whereas all calculations in your information mannequin are appropriate.
• Poor performing information mannequin.
• Bloated mannequin measurement.
• Difficulties in configuring user-defined aggregations (agg consciousness).
• Difficulties in organising incremental information refresh.
• Getting clean visuals after the primary information refresh in Energy BI service.

On this blogpost, I clarify the widespread pitfalls to forestall future challenges that may be time-consuming to establish and repair.

Background

Earlier than we dive into the subject of this weblog put up, I want to begin with a little bit of background. Everyone knows that Energy BI isn’t solely a reporting software. It’s certainly a knowledge platform supporting numerous features of enterprise intelligence, information engineering, and information science. There are two languages we should be taught to have the ability to work with Energy BI: Energy Question (M) and DAX. The aim of the 2 languages is sort of totally different. We use Energy Question for information transformation and information preparation, whereas DAX is used for information evaluation within the Tabular information mannequin. Right here is the purpose, the 2 languages in Energy BI have totally different information sorts.

The most typical Energy BI growth eventualities begin with connecting to the info supply(s). Energy BI helps lots of of information sources. Most information supply connections occur in Energy Question (the info preparation layer in a Energy BI answer) until we join dwell to a semantic layer comparable to an SSAS occasion or a Energy BI dataset. Many supported information sources have their very own information sorts, and a few don’t. As an illustration, SQL Server has its personal information sorts, however CSV doesn’t. When the info supply has information sorts, the mashup engine tries to establish information sorts to the closest information sort accessible in Energy Question. Although the supply system has information sorts, the info sorts won’t be suitable with Energy Question information sorts. For the info sources that don’t assist information sorts, the matchup engine tries to detect the info sorts based mostly on the pattern information loaded into the info preview pane within the Energy Question Editor window. However, there isn’t any assure that the detected information sorts are appropriate. So, it’s best observe to validate the detected information sorts anyway.

Energy BI makes use of the Tabular mannequin information sorts when it hundreds the info into the info mannequin. The info sorts within the information mannequin could or will not be suitable with the info sorts outlined in Energy Question. As an illustration, Energy Question has a Binary information sort, however the Tabular mannequin doesn’t.

The next desk exhibits Energy Question’s datatypes, their representations within the Energy Question Editor’s UI, their mapping information sorts within the information mannequin (DAX), and the inner information sorts within the xVelocity (Tabular mannequin) engine:

Because the above desk exhibits, in Energy Question’s UI, Entire Quantity, Decimal, Fastened Decimal and Share are all in sort quantity within the Energy Question engine. The sort names within the Energy BI UI additionally differ from their equivalents within the xVelocity engine. Allow us to dig deeper.

Information Varieties in Energy Question

As talked about earlier, in Energy Question, we’ve got just one numeric datatype: quantity whereas within the Energy Question Editor’s UI, within the Rework tab, there’s a Information Sort drop-down button displaying 4 numeric datatypes, as the next picture exhibits:

In Energy Question system language, we specify a numeric information sort as sort quantity or Quantity.Sort. Allow us to have a look at an instance to see what this implies.

The next expression creates a desk with totally different values:

#desk({"Worth"}
	, {
		{100}
		, {65565}
		, {-100000}
		, {-999.9999}
		, {0.001}
		, {10000000.0000001}
		, {999999999999999999.999999999999999999}
		, {#datetimezone(2023,1,1,11,45,54,+12,0)}
		, {#datetime(2023,1,1,11,45,54)}
		, {#date(2023,1,1)}
		, {#time(11,45,54)}
		, {true}
		, {#length(11,45,54,22)}
		, {"It is a textual content"}
	})

The outcomes are proven within the following picture:

Now we add a brand new column that exhibits the info sort of the values. To take action, use the Worth.Sort([Value]) operate returns the kind of every worth of the Worth column. The outcomes are proven within the following picture:

To see the precise sort, we should click on on every cell (not the values) of the Worth Sort column, as proven within the following picture:

With this methodology, we’ve got to click on every cell in to see the info kinds of the values that isn’t supreme. However there’s at the moment no operate accessible in Energy Question to transform a Sort worth to Textual content. So, to indicate every sort’s worth as textual content in a desk, we use a easy trick. There’s a operate in Energy Question returning the desk’s metadata: Desk.Schema(desk as desk). The operate leads to a desk revealing helpful details about the desk used within the operate, together with column Identify, TypeName, Variety, and so forth. We need to present TypeName of the Worth Sort column. So, we solely want to show every worth right into a desk utilizing the Desk.FromValue(worth as any) operate. We then get the values of the Variety column from the output of the Desk.Schema() operate.

To take action, we add a brand new column to get textual values from the Variety column. We named the brand new column Datatypes. The next expression caters to that:

Desk.Schema(
      Desk.FromValue([Value])
      )[Kind]{0}

The next picture exhibits the outcomes:

Because the outcomes present, all numeric values are of sort quantity and the way in which they’re represented within the Energy Question Editor’s UI doesn’t have an effect on how the Energy Question engine treats these sorts. The info sort representations within the Energy Question UI are someway aligned with the sort sides in Energy Question. A aspect is used so as to add particulars to a sort type. As an illustration, we are able to use sides to a textual content sort if we need to have a textual content sort that doesn’t settle for null. We will outline the worth’s sorts utilizing sort sides utilizing Aspect.Sort syntax, comparable to utilizing In64.Sort for a 64-bit integer quantity or utilizing Share.Sort to indicate a quantity in proportion. Nonetheless, to outline the worth’s sort, we use the sort typename syntax comparable to defining quantity utilizing sort quantity or a textual content utilizing sort textual content. The next desk exhibits the Energy Question sorts and the syntax to make use of to outline them:

Sadly, the Energy Question Language Specification documentation doesn’t embody sides and there usually are not many on-line assets or books that I can reference right here apart from Ben Gribaudo’s weblog who completely defined sides intimately which I strongly advocate studying.

Whereas Energy Question engine treats the values based mostly on their sorts not their sides, utilizing sides is advisable as they have an effect on the info when it’s being loaded into the info mannequin which raises a query: what occurs after we load the info into the info mannequin? which brings us to the following part of this weblog put up.

Information sorts in Energy BI information mannequin

Energy BI makes use of the xVelocity in-memory information processing engine to course of the info. The xVelocity engine makes use of columnstore indexing expertise that compresses the info based mostly on the cardinality of the column, which brings us to a vital level: though the Energy Question engine treats all of the numeric values as the sort quantity, they get compressed in another way relying on their column cardinality after loading the values within the Energy BI mannequin. Due to this fact, setting the right sort aspect for every column is necessary.

The numeric values are probably the most widespread datatypes utilized in Energy BI. Right here is one other instance displaying the variations between the 4 quantity sides. Run the next expression in a brand new clean question within the Energy Question Editor:

// Decimal Numbers with 6 Decimal Digits
let
    Supply = Record.Generate(()=> 0.000001, every _ <= 10, every _ + 0.000001 ),
    #"Transformed to Desk" = Desk.FromList(Supply, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Desk.RenameColumns(#"Transformed to Desk",{{"Column1", "Supply"}}),
    #"Duplicated Supply Column as Decimal" = Desk.DuplicateColumn(#"Renamed Columns", "Supply", "Decimal", Decimal.Sort),
    #"Duplicated Supply Column as Fastened Decimal" = Desk.DuplicateColumn(#"Duplicated Supply Column as Decimal", "Supply", "Fastened Decimal", Foreign money.Sort),
    #"Duplicated Supply Column as Share" = Desk.DuplicateColumn(#"Duplicated Supply Column as Fastened Decimal", "Supply", "Share", Share.Sort)
in
    #"Duplicated Supply Column as Share"

The above expressions create 10 million rows of decimal values between 0 and 10. The ensuing desk has 4 columns containing the identical information with totally different sides. The primary column, Supply, accommodates the values of sort any, which interprets to sort textual content. The remaining three columns are duplicated from the Supply column with totally different sort sides, as follows:

• Decimal
• Fastened decimal
• Share

The next screenshot exhibits the ensuing pattern information of our expression within the Energy Question Editor:

Now click on Shut & Apply from the Dwelling tab of the Energy Question Editor to import the info into the info mannequin. At this level, we have to use a third-party neighborhood software, DAX Studio, which might be downloaded from right here.

After downloading and putting in, DAX Studio registers itself as an Exterior Instrument within the Energy BI Desktop as the next picture exhibits:

Click on the DAX Studio from the Exterior Instruments tab which mechanically connects it to the present Energy BI Desktop mannequin, and observe these steps:

1. Click on the Superior tab
2. Click on the View Metrics button
3. Click on Columns from the VertiPaq Analyzer part
4. Have a look at the Cardinality, Col Dimension, and % Desk columns

The next picture exhibits the previous steps:

The outcomes present that the Decimal column and Share consumed probably the most important a part of the desk’s quantity. Their cardinality can be a lot increased than the Fastened Decimal column. So right here it’s now extra apparent that utilizing the Fastened Decimal datatype (aspect) for numeric values might help with information compression, lowering the info mannequin measurement and growing the efficiency. Due to this fact, it’s sensible to at all times use Fastened Decimal for decimal values. Because the Fastened Decimal values translate to the Foreign money datatype in DAX, we should change the columns’ format if Foreign money is unsuitable. Because the identify suggests, Fastened Decimal has mounted 4 decimal factors. Due to this fact, if the unique worth has extra decimal digits after conversion to the Fastened Decimal, the digits after the fourth decimal level shall be truncated.

That’s the reason the Cardinality column within the VertiPaq Analyzer in DAX Studio exhibits a lot decrease cardinality for the Fastened Decimal column (the column values solely hold as much as 4 decimal factors, no more).

Obtain the pattern file from right here.

So, the message is right here to at all times use the datatype that is smart to the enterprise and is environment friendly within the information mannequin. Utilizing the VertiPaq Analyzer in DAX Studio is sweet for understanding the varied features of the info mannequin, together with the column datatypes. As a knowledge modeler, it’s important to know how the Energy Question sorts and sides translate to DAX datatypes. As we noticed on this weblog put up, information sort conversion can have an effect on the info mannequin’s compression price and efficiency.