Tuesday, February 27, 2024
HomeBusiness IntelligenceDatatype Conversion in Energy Question Impacts Information Modeling in Energy BI

Datatype Conversion in Energy Question Impacts Information Modeling in Energy BI

Datatype Conversion in Power Query Affects Data Modeling in Power BI

In my consulting expertise working with clients utilizing Energy BI, many challenges that Energy BI builders face are because of negligence to information varieties. Listed below are some widespread challenges which can be the direct or oblique outcomes of inappropriate information varieties 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 stop future challenges that may be time-consuming to establish and repair.


Earlier than we dive into the subject of this weblog publish, I want to begin with a little bit of background. Everyone knows that Energy BI will not be solely a reporting instrument. It’s certainly a knowledge platform supporting numerous elements of enterprise intelligence, information engineering, and information science. There are two languages we should study 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 varieties.

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

Energy BI makes use of the Tabular mannequin information varieties when it masses the information into the information mannequin. The information varieties within the information mannequin might or is probably not appropriate with the information varieties outlined in Energy Question. For example, Energy Question has a Binary information sort, however the Tabular mannequin doesn’t.

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

Power Query and DAX (data model) data type mapping
Energy Question and DAX (information mannequin) information sort mapping

Because the above desk reveals, in Energy Question’s UI, Complete Quantity, Decimal, Mounted Decimal and Proportion 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 have now just one numeric datatype: quantity whereas within the Energy Question Editor’s UI, within the Remodel tab, there’s a Information Sort drop-down button exhibiting 4 numeric datatypes, as the next picture reveals:

Data type representations in the Power Query Editor's UI
Information sort representations within the Energy Question Editor’s UI

In Energy Question method 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:

	, {
		, {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}
		, {#period(11,45,54,22)}
		, {"It is a textual content"}

The outcomes are proven within the following picture:

Generating values in Power Query
Producing values in Energy Question

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

Getting a column's value types in Power Query
Getting a column’s worth varieties in Energy Question

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

Click on a cell to see its type in Power Query Editor
Click on on a cell to see its sort in Energy Question Editor

With this methodology, we have now to click on every cell in to see the information varieties of the values that’s not supreme. However there may be presently no perform 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 perform in Energy Question returning the desk’s metadata: Desk.Schema(desk as desk). The perform leads to a desk revealing helpful details about the desk used within the perform, together with column IdentifyTypeNameForm, 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) perform. We then get the values of the Form column from the output of the Desk.Schema() perform.

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


The next picture reveals the outcomes:

Getting type values as text in Power Query
Getting sort values as textual content in Energy Question

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 varieties. The information sort representations within the Energy Question UI are one way or the other aligned with the sort aspects in Energy Question. A side is used so as to add particulars to a sort sort. For example, we will use aspects 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 varieties utilizing sort aspects utilizing Aspect.Sort syntax, resembling utilizing In64.Sort for a 64-bit integer quantity or utilizing Proportion.Sort to indicate a quantity in share. Nonetheless, to outline the worth’s sort, we use the sort typename syntax resembling defining quantity utilizing sort quantity or a textual content utilizing sort textual content. The next desk reveals the Energy Question varieties and the syntax to make use of to outline them:

Defining types and facets in Power Query M
Defining varieties and aspects in Energy Question M

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

Whereas Energy Question engine treats the values based mostly on their varieties not their aspects, utilizing aspects is beneficial as they have an effect on the information when it’s being loaded into the information mannequin which raises a query: what occurs after we load the information into the information mannequin? which brings us to the subsequent part of this weblog publish.

Information varieties in Energy BI information mannequin

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

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

// Decimal Numbers with 6 Decimal Digits
    Supply = Listing.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 Mounted Decimal" = Desk.DuplicateColumn(#"Duplicated Supply Column as Decimal", "Supply", "Mounted Decimal", Forex.Sort),
    #"Duplicated Supply Column as Proportion" = Desk.DuplicateColumn(#"Duplicated Supply Column as Mounted Decimal", "Supply", "Proportion", Proportion.Sort)
    #"Duplicated Supply Column as Proportion"

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 aspects. 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 aspects, as follows:

  • Decimal
  • Mounted decimal
  • Proportion

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

Generating 10 million numeric values and use different type facets in Power Query M
Producing 10 million numeric values and use totally different sort aspects in Energy Question M

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

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

External tools in Power BI Desktop
Exterior instruments in Energy BI Desktop

Click on the DAX Studio from the Exterior Instruments tab which routinely 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. Take a look at the CardinalityCol Measurement, and % Desk columns

The next picture reveals the previous steps:

VertiPaq Analyzer Metrics in DAX Studio
VertiPaq Analyzer Metrics in DAX Studio

The outcomes present that the Decimal column and Proportion consumed essentially the most important a part of the desk’s quantity. Their cardinality can also be a lot increased than the Mounted Decimal column. So right here it’s now extra apparent that utilizing the Mounted Decimal datatype (side) for numeric values will help with information compression, decreasing the information mannequin measurement and rising the efficiency. Due to this fact, it’s sensible to at all times use Mounted Decimal for decimal values. Because the Mounted Decimal values translate to the Forex datatype in DAX, we should change the columns’ format if Forex is unsuitable. Because the identify suggests, Mounted Decimal has fastened 4 decimal factors. Due to this fact, if the unique worth has extra decimal digits after conversion to the Mounted 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 reveals a lot decrease cardinality for the Mounted 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 sensible to the enterprise and is environment friendly within the information mannequin. Utilizing the VertiPaq Analyzer in DAX Studio is sweet for understanding the assorted elements of the information mannequin, together with the column datatypes. As a knowledge modeler, it’s important to grasp how the Energy Question varieties and aspects translate to DAX datatypes. As we noticed on this weblog publish, information sort conversion can have an effect on the information mannequin’s compression fee and efficiency.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments