close

A Step-by-Step Guide to Improve your Data Model’s Performance using the Best Practices in Power BI

Posted by Kevin Booth on Mar, 22, 2021 10:03

Today here in this article we will be going over some rules to add to your Tabular Editor. It will scan your entire data model as per every rule defined and provide suggestions as per the objectives whenever a condition is met. This method will increase your efficiency in a task that usually takes long hours if done manually.

Power BI and DAX expressions:

  • Make use of the DIVIDE function for splitting up different information and evade using the IFERROR function in the Power BI. 
  • Qualify the Column references entirely and Measure references should be entire without qualifications.
  • Two measures should never have identical descriptions.

How to prevent an error?

  • To avoid any types of errors, Make sure that all the data columns have a source column without fail. Also, all the calculated columns should have an expression.
  • Make certain that the model is as contracted and lean as it can be.
  •  You should have longer and leaner tables for optimum working of Power BI.

How to ace formatting?

  • Add or remove data category for columns in your data model.
  • Do not recapitulate numeric columns and also make sure that the initial letter of items is in capital. 
  • Hide fact table columns.
  • Put out of sight the foreign keys and mark the main keys. 
  • Months (as a string) should be organized and sorted out.
  • Any object should neither begin nor finish with a space. 
  • Percentages has to be formatted with a number of separators and just one decimal.
  • Provide a design string for “Date” and “month” columns in your data model.
  • Association columns should be of numeral data type.
  • Whole numbers should be formatted with thousands separators and no decimals

Maintenance of data model:

  • Make sure that the tables have associations.
  • Objects should be without any description.
  • Eliminate data sources that have no references.
  • Get rid of unnecessary columns and measures to increase the speed of your data model: If you have the option to choose amongst a premeditated column or a measure, go for the measure because they are more flexible, don’t acquire too much processing time and don’t capture a lot of space in the model too.
  • If you need time portion in your model, then divide the timestamp into two columns namely a date column and a time column. This decreases the uniqueness of the high cardinal column and the engine is going to be able to squeeze the data accurately to a large extent.
  • Make sure that the model is as thin and incline as it can be.
  • Longer and leaner tables are much easier to work with.

Columns Naming Conventions to come into play:

  • Bring into play Camel Case for columns that are out of sight or hidden.
  • Divider name must match the table name for distinct partition tables.
  • The names of the objects are not supposed to hold any special characters.

SSAS Best Practices:

  • Stay away from bi-directional associations in opposition to the elevated cardinality columns: What exactly is high cardinality columns? Columns with an elevated amount of distinctive values. The more distinctive values a column has, the more impenetrable it is for the engine to squeeze it.
  •  Try to keep away from text columns that are of no use, except for when you enormously require them in your reports.
  • Keep away from the snowflake schema structural diagram.
  • Carry out the usage of hovering point data types.
  • Huge tables should be separated.
  • Reduce the power query transformations.
  • The data model should include a date table and the auto-date table should be removed.
  • For users who are giving out printed reports, colorless or light color backgrounds are the most suitable for printers.
  • Don’t place a lot of visuals in a particular report as it slows down the report’s presentation.
  • Decrease the usage of calculated tables.
  • Templates make sure that commercial branding is functional to all the pages.
  • Numerous scrolls on a particular page can direct to an unconstructive user experience.
  • Examine traditional visual presentation in reports to make sure that the report loads quickly.

What is a Tabular Editor and how does the best practice rule works in it? 

Tabular Editor is a tool that is a substitute for SSDT to generate Tabular models. It’s a lot quicker and has a number of superior features like scripting. The best part about is that you can connect it up to a Power BI model as well!

Here are the steps from which you can understand how the Best Practice Rules works in Tabular Editor:

1. Download and Install Tabular editor in your device.

2. Install the Best Practice Rules from Git Hub 

3. Inside the set up menu, type %localappdata% and then click on Enter to proceed further.

4. Find the way to the ‘Tabular Editor’ file.

5. Next, Copy the file of rules i.e. (.json) and then insert it into the Tabular Editor folder.

6. Subsequently, open the Tabular Editor and attach to your data model.

7. Choose ‘Tools’ from the File menu and select ‘Best Practice Analyzer’ right after that.

After being done with this procedure, there you have loaded all the rules to the local Tabular Editor and you can now run these rules in opposition to your model. 

How can you view the rule details?

To view extra information about a meticulous rule, take the helm to ‘Tools -> Manage BPA Rules’ in Tabular Editor. Tap on ‘Rules for the local user’ and there you have it, you can view all the details you want about as many rules as you want.

How can you fix the issues?

A few of the problems that are highlighted by the Best Practice Rules are to a certain extent easy and can get fixed in just a few seconds. Others are extra convoluted and may perhaps take some time. A number of rules that are comparatively simpler have an integral mechanism in them to fix the matter. All that you have to do is right snap on an object that is within the Best Practice Analyzer and subsequently choose ‘Generate fix script’. This is going to copy a C# script which can be pasted and implemented in the superior Scripting window in Tabular Editor. 

How to disable the rules?

If a specific rule is not relevant, there are quite a lot of ways to conceal these rules or put them out of sight. To begin with, you can unpick a rule inside the ‘Manage Best Practice Rules’ windowpane. This is going to put the rule out of action as a whole. If you wish to immobilize a rule for a specific item, then you can double click on an object inside the Best Practice Analyzer and then click on ‘Ignore items’. 

What is rule severity?

To set the ‘severity’ level for each rule, there is an option. This has zero force and is of no use when the most excellent Practice Analyzer rules are running within the Tabular Editor. Nevertheless, if you install a model via the Command Line alternative and state the Best Practice Analyzer rules to scamper, it is then that the severity level comes into participation. 

Conclusion:

This set of rules were drafted from the after-effects of analyzing a number of tabular models formed by experts from across the planet for big data modeling. The best practices in the Power BI can work like magic for your data model in enhancing the performance of your data model. However, You need to be pretty careful while working with these practices to ensure the smooth and safe working of your data model.

[gravityform id="43" title="true" description="false" ajax="true"]
<div class='gf_browser_chrome gform_wrapper gform_legacy_markup_wrapper' id='gform_wrapper_43' ><div id='gf_43' class='gform_anchor' tabindex='-1'></div> <div class='gform_heading'> <h3 class="gform_title">Subscriber - Powerbi e-book</h3> </div><form method='post' enctype='multipart/form-data' target='gform_ajax_frame_43' id='gform_43' action='/a-step-by-step-guide-to-improve-your-data-models-performance-using-the-best-practices-in-power-bi/#gf_43' > <div class='gform_body gform-body'><ul id='gform_fields_43' class='gform_fields top_label form_sublabel_below description_below'><li id="field_43_7" class="gfield gfield--width-full gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_7' id='input_43_7' type='hidden' class='gform_hidden' aria-invalid="false" value='https://www.epcgroup.net/a-step-by-step-guide-to-improve-your-data-models-performance-using-the-best-practices-in-power-bi/' /></div></li><li id="field_43_4" class="gfield gfield_html gfield_html_formatted gfield_no_follows_desc field_sublabel_below field_description_below gfield_visibility_visible" > <div class="description_data"> <p class="dp_one">Subscribe to our newsletter and get the first three chapters of the eBook for <strong>free<strong>.</p> </div></li><li id="field_43_6" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label gfield_label_before_complex' >Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_complex ginput_container no_prefix has_first_name no_middle_name has_last_name no_suffix gf_name_has_2 ginput_container_name' id='input_43_6'> <span id='input_43_6_3_container' class='name_first' > <input type='text' name='input_6.3' id='input_43_6_3' value='' aria-label='First name' aria-required='true' placeholder='First Name' /> <label for='input_43_6_3' >First</label> </span> <span id='input_43_6_6_container' class='name_last' > <input type='text' name='input_6.6' id='input_43_6_6' value='' aria-label='Last name' aria-required='true' placeholder='Last Name' /> <label for='input_43_6_6' >Last</label> </span> </div></li><li id="field_43_2" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_43_2' >Email Address<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_email'> <input name='input_2' id='input_43_2' type='text' value='' class='medium' aria-required="true" aria-invalid="false" aria-describedby="gfield_description_43_2" /> </div><div class='gfield_description' id='gfield_description_43_2'>Please enter your correct email address. You will receive an email to download the eBook.</div></li><li id="field_43_3" class="gfield g-captcha field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label screen-reader-text' for='input_43_3' ></label><div id='input_43_3' class='ginput_container ginput_recaptcha' data-sitekey='6LdQ388UAAAAAJaahWs7D_jWzeQhUZW6-VNwWfaU' data-theme='light' data-tabindex='0' data-badge=''></div></li><li id="field_43_5" class="gfield gfield_html gfield_html_formatted gfield_no_follows_desc field_sublabel_below field_description_below gfield_visibility_visible" ><div class="note_description"><p><i><strong>NOTE: </strong>We will never send you spam or pass on your email address to any third party. You may choose to opt-out at any time.</i></p></div></li></ul></div> <div class='gform_footer top_label'> <input type='submit' id='gform_submit_button_43' class='gform_button button' value='Download Now' onclick='if(window["gf_submitting_43"]){return false;} window["gf_submitting_43"]=true; ' onkeypress='if( event.keyCode == 13 ){ if(window["gf_submitting_43"]){return false;} window["gf_submitting_43"]=true; jQuery("#gform_43").trigger("submit",[true]); }' /> <input type='hidden' name='gform_ajax' value='form_id=43&amp;title=1&amp;description=&amp;tabindex=0' /> <input type='hidden' class='gform_hidden' name='is_submit_43' value='1' /> <input type='hidden' class='gform_hidden' name='gform_submit' value='43' /> <input type='hidden' class='gform_hidden' name='gform_unique_id' value='' /> <input type='hidden' class='gform_hidden' name='state_43' value='WyJbXSIsIjEwNTJhNGVmMWMyNzI3YTJmMjdiZTA1NjU4ZDMzYzY3Il0=' /> <input type='hidden' class='gform_hidden' name='gform_target_page_number_43' id='gform_target_page_number_43' value='0' /> <input type='hidden' class='gform_hidden' name='gform_source_page_number_43' id='gform_source_page_number_43' value='1' /> <input type='hidden' name='gform_field_values' value='' /> </div> <p style="display: none !important;"><label>&#916;<textarea name="ak_hp_textarea" cols="45" rows="8" maxlength="100"></textarea></label><input type="hidden" id="ak_js" name="ak_js" value="133"/><script>document.getElementById( "ak_js" ).setAttribute( "value", ( new Date() ).getTime() );</script></p></form> </div> <iframe style='display:none;width:0px;height:0px;' src='about:blank' name='gform_ajax_frame_43' id='gform_ajax_frame_43' title='This iframe contains the logic required to handle Ajax powered Gravity Forms.'></iframe> <script type="text/javascript"> gform.initializeOnLoaded( function() {gformInitSpinner( 43, 'https://www.epcgroup.net/wp-content/plugins/gravityforms/images/spinner.svg' );jQuery('#gform_ajax_frame_43').on('load',function(){var contents = jQuery(this).contents().find('*').html();var is_postback = contents.indexOf('GF_AJAX_POSTBACK') >= 0;if(!is_postback){return;}var form_content = jQuery(this).contents().find('#gform_wrapper_43');var is_confirmation = jQuery(this).contents().find('#gform_confirmation_wrapper_43').length > 0;var is_redirect = contents.indexOf('gformRedirect(){') >= 0;var is_form = form_content.length > 0 && ! is_redirect && ! is_confirmation;var mt = parseInt(jQuery('html').css('margin-top'), 10) + parseInt(jQuery('body').css('margin-top'), 10) + 100;if(is_form){jQuery('#gform_wrapper_43').html(form_content.html());if(form_content.hasClass('gform_validation_error')){jQuery('#gform_wrapper_43').addClass('gform_validation_error');} else {jQuery('#gform_wrapper_43').removeClass('gform_validation_error');}setTimeout( function() { /* delay the scroll by 50 milliseconds to fix a bug in chrome */ jQuery(document).scrollTop(jQuery('#gform_wrapper_43').offset().top - mt); }, 50 );if(window['gformInitDatepicker']) {gformInitDatepicker();}if(window['gformInitPriceFields']) {gformInitPriceFields();}var current_page = jQuery('#gform_source_page_number_43').val();gformInitSpinner( 43, 'https://www.epcgroup.net/wp-content/plugins/gravityforms/images/spinner.svg' );jQuery(document).trigger('gform_page_loaded', [43, current_page]);window['gf_submitting_43'] = false;}else if(!is_redirect){var confirmation_content = jQuery(this).contents().find('.GF_AJAX_POSTBACK').html();if(!confirmation_content){confirmation_content = contents;}setTimeout(function(){jQuery('#gform_wrapper_43').replaceWith(confirmation_content);jQuery(document).scrollTop(jQuery('#gf_43').offset().top - mt);jQuery(document).trigger('gform_confirmation_loaded', [43]);window['gf_submitting_43'] = false;wp.a11y.speak(jQuery('#gform_confirmation_message_43').text());}, 50);}else{jQuery('#gform_43').append(contents);if(window['gformRedirect']) {gformRedirect();}}jQuery(document).trigger('gform_post_render', [43, current_page]);} );} ); </script>
[gravityforms id=41 title=”true” description=”false”]
<div class='gf_browser_chrome gform_wrapper exit_intent_popup_wrapper gform_legacy_markup_wrapper' id='gform_wrapper_41' > <div class='gform_heading'> <h3 class="gform_title">Exit Intent</h3> <span class='gform_description'></span> </div><form method='post' enctype='multipart/form-data' id='gform_41' class='exit_intent_popup gform_legacy_markup' action='/a-step-by-step-guide-to-improve-your-data-models-performance-using-the-best-practices-in-power-bi/' > <div class='gform_body gform-body'><ul id='gform_fields_41' class='gform_fields top_label form_sublabel_below description_below'><li id="field_41_1" class="gfield gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_1' id='input_41_1' type='hidden' class='gform_hidden' aria-invalid="false" value='https://www.epcgroup.net/a-step-by-step-guide-to-improve-your-data-models-performance-using-the-best-practices-in-power-bi/' /></div></li><li id="field_41_11" class="gfield gfield--width-full gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_11' id='input_41_11' type='hidden' class='gform_hidden' aria-invalid="false" value='ddd01b75-d4fc-ea11-a816-000d3a591fb8' /></div></li><li id="field_41_12" class="gfield gfield--width-full gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_12' id='input_41_12' type='hidden' class='gform_hidden' aria-invalid="false" value='' /></div></li><li id="field_41_13" class="gfield gfield--width-full gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_13' id='input_41_13' type='hidden' class='gform_hidden' aria-invalid="false" value='' /></div></li><li id="field_41_9" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_9' >Full Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_9' id='input_41_9' type='text' value='' class='medium' placeholder='Full Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_6" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_6' >Email<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_email'> <input name='input_6' id='input_41_6' type='text' value='' class='medium' placeholder='Email Address' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_7" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_7' >Phone<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_phone'><input name='input_7' id='input_41_7' type='text' value='' class='medium' placeholder='Phone Number' aria-required="true" aria-invalid="false" /></div></li><li id="field_41_10" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_10' >Company Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_10' id='input_41_10' type='text' value='' class='medium' placeholder='Company Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_8" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_8' >Message<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_textarea'><textarea name='input_8' id='input_41_8' class='textarea medium' placeholder='Type your message here...' aria-required="true" aria-invalid="false" rows='10' cols='50'></textarea></div></li></ul></div> <div class='gform_footer top_label'> <input type='submit' id='gform_submit_button_41' class='gform_button button' value='Submit' onclick='if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; ' onkeypress='if( event.keyCode == 13 ){ if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; jQuery("#gform_41").trigger("submit",[true]); }' /> <input type='hidden' class='gform_hidden' name='is_submit_41' value='1' /> <input type='hidden' class='gform_hidden' name='gform_submit' value='41' /> <input type='hidden' class='gform_hidden' name='gform_unique_id' value='' /> <input type='hidden' class='gform_hidden' name='state_41' value='WyJbXSIsIjEwNTJhNGVmMWMyNzI3YTJmMjdiZTA1NjU4ZDMzYzY3Il0=' /> <input type='hidden' class='gform_hidden' name='gform_target_page_number_41' id='gform_target_page_number_41' value='0' /> <input type='hidden' class='gform_hidden' name='gform_source_page_number_41' id='gform_source_page_number_41' value='1' /> <input type='hidden' name='gform_field_values' value='' /> </div> <p style="display: none !important;"><label>&#916;<textarea name="ak_hp_textarea" cols="45" rows="8" maxlength="100"></textarea></label><input type="hidden" id="ak_js" name="ak_js" value="179"/><script>document.getElementById( "ak_js" ).setAttribute( "value", ( new Date() ).getTime() );</script></p></form> </div>