Free Porn
20.8 C
New York
Saturday, July 20, 2024

Datatype Conversion in Energy Question Impacts Knowledge 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 resulting from negligence to knowledge varieties. Listed below are some frequent challenges which might be the direct or oblique outcomes of inappropriate knowledge varieties and knowledge sort conversion:

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

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


Earlier than we dive into the subject of this weblog submit, 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 varied facets of enterprise intelligence, knowledge engineering, and knowledge 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 kind of totally different. We use Energy Question for knowledge transformation and knowledge preparation, whereas DAX is used for knowledge evaluation within the Tabular knowledge mannequin. Right here is the purpose, the 2 languages in Energy BI have totally different knowledge varieties.

The most typical Energy BI improvement eventualities begin with connecting to the information supply(s). Energy BI helps a whole lot of knowledge sources. Most knowledge supply connections occur in Energy Question (the information preparation layer in a Energy BI resolution) until we join dwell to a semantic layer akin to an SSAS occasion or a Energy BI dataset. Many supported knowledge sources have their very own knowledge varieties, and a few don’t. As an example, SQL Server has its personal knowledge varieties, however CSV doesn’t. When the information supply has knowledge varieties, the mashup engine tries to determine knowledge varieties to the closest knowledge sort accessible in Energy Question. Though the supply system has knowledge varieties, the information varieties may not be appropriate with Energy Question knowledge varieties. For the information sources that don’t help knowledge varieties, the matchup engine tries to detect the information varieties primarily based on the pattern knowledge loaded into the information preview pane within the Energy Question Editor window. However, there isn’t any assure that the detected knowledge varieties are appropriate. So, it’s best follow to validate the detected knowledge varieties anyway.

Energy BI makes use of the Tabular mannequin knowledge varieties when it masses the information into the information mannequin. The information varieties within the knowledge mannequin might or will not be appropriate with the information varieties outlined in Energy Question. As an example, Energy Question has a Binary knowledge 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 knowledge varieties within the knowledge mannequin (DAX), and the inner knowledge varieties within the xVelocity (Tabular mannequin) engine:

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

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.

Knowledge Sorts in Energy Question

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

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

In Energy Question components language, we specify a numeric knowledge sort as sort quantity or Quantity.Kind. Allow us to take 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}
		, {#length(11,45,54,22)}
		, {"This can be 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 exhibits the information sort of the values. To take action, use the Worth.Kind([Value]) operate 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 should click on on every cell (not the values) of the Worth Kind 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 technique, now we have to click on every cell in to see the information forms of the values that’s not ultimate. However there’s at the moment no operate accessible in Energy Question to transform a Kind 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 TitleTypeNameSort, and so forth. We need to present TypeName of the Worth Kind 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 Sort column from the output of the Desk.Schema() operate.

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


The next picture exhibits 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 best way 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 by some means aligned with the kind sides in Energy Question. A side is used so as to add particulars to a sort type. As an example, 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 are able to outline the worth’s varieties utilizing sort sides utilizing Side.Kind syntax, akin to utilizing In64.Kind for a 64-bit integer quantity or utilizing Share.Kind to indicate a quantity in proportion. Nevertheless, to outline the worth’s sort, we use the sort typename syntax akin to defining quantity utilizing sort quantity or a textual content utilizing sort textual content. The next desk exhibits 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 sides in Energy Question M

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 aside from Ben Gribaudo’s weblog who totally defined sides intimately which I strongly advocate studying.

Whereas Energy Question engine treats the values primarily based on their varieties not their sides, utilizing sides is really helpful 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 following part of this weblog submit.

Knowledge varieties in Energy BI knowledge mannequin

Energy BI makes use of the xVelocity in-memory knowledge processing engine to course of the information. The xVelocity engine makes use of columnstore indexing know-how that compresses the information primarily based 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 kind 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 proper sort side for every column is vital.

The numeric values are one of the frequent 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
    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.Kind),
    #"Duplicated Supply Column as Fastened Decimal" = Desk.DuplicateColumn(#"Duplicated Supply Column as Decimal", "Supply", "Fastened Decimal", Forex.Kind),
    #"Duplicated Supply Column as Share" = Desk.DuplicateColumn(#"Duplicated Supply Column as Fastened Decimal", "Supply", "Share", Share.Kind)
    #"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 knowledge with totally different sides. The primary column, Supply, incorporates 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 knowledge 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 sides 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 could 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 exhibits:

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

Click on the DAX Studio from the Exterior Instruments tab which robotically connects it to the present Energy BI Desktop mannequin, and comply with 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 exhibits the previous steps:

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

The outcomes present that the Decimal column and Share consumed probably the most vital a part of the desk’s quantity. Their cardinality can be a lot larger than the Fastened Decimal column. So right here it’s now extra apparent that utilizing the Fastened Decimal datatype (side) for numeric values may also help with knowledge compression, decreasing the information mannequin dimension and rising the efficiency. Due to this fact, it’s clever to all the time use Fastened Decimal for decimal values. Because the Fastened Decimal values translate to the Forex datatype in DAX, we should change the columns’ format if Forex is unsuitable. Because the title suggests, Fastened Decimal has fastened 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 can 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 maintain as much as 4 decimal factors, no more).

Obtain the pattern file from right here.

So, the message is right here to all the time use the datatype that is sensible to the enterprise and is environment friendly within the knowledge mannequin. Utilizing the VertiPaq Analyzer in DAX Studio is sweet for understanding the varied facets of the information mannequin, together with the column datatypes. As a knowledge modeler, it’s important to grasp how the Energy Question varieties and sides translate to DAX datatypes. As we noticed on this weblog submit, knowledge sort conversion can have an effect on the information mannequin’s compression charge and efficiency.

Related Articles


Please enter your comment!
Please enter your name here

Latest Articles