Doing Power BI the Right Way: 1. Futureproofing Power BI solutions

Part of the series: Doing Power BI the Right Way

When starting a Power BI project, you have many choices to make. Decisions like how to source your data, where and how to create queries to cleanse, transform and reshape the data; where and how to create calculations and the nuances of modeling are just the tip of the iceberg. Most Power BI solutions progress through a few stages in their lifespan, which include:

  1. Creating a simple prototype to explore ideas and to get feedback from users and business leaders
  2. Building a working proof-of-concept to frame the solution
  3. Designing a foundational data model to build upon
  4. Completing a production-scale solution
  5. Iterative enhancements & releases

The purpose of this post is to provide some guidance to help you design Power BI solutions that can survive the journey through these stages with as little “throw-away” design as possible. There will always be prototyping and redesign in any project but if you follow proven design patterns and lessons learned from prior experience, you’ll have a much better chance of building a reporting solution that will endure.

Oh, I should mention this… the difference between item #1 (the simple prototype) and #2 (working proof-of-concept) is that you need to throw away the first one – and anything else that doesn’t lay a foundation that you can build on top of. This is a hard lesson but one that will pay off by helping our teams, sponsors and business stakeholders understand that we can proceed after laying down solid bricks (based on well-defined functional and technical requirements) to build the rest of the solution.

Reality Check

If you are using Power BI to create reporting solutions for business users, your circumstances are likely similar to one of these scenarios:

  1. You are a self-service report designer creating a small report solution that will grow and evolve over time.
  2. You work in a software development group in an IT organization with a formal application lifecycle management discipline.
  3. You work in a business unit or small company with an informal software process – and they need your guidance to be successful.

If you are a self-service report designer creating a small report solution that will grow and evolve over time, you likely have a data source that wasn’t specifically curated for reporting and analysis. The data will need to be massaged and shaped before you can design insightful, interactive visuals. That’s cool because we have great tools built into Power BI Desktop to do that sort of thing. You might have some ideas about how to present the data but you’ll probably try different things and see where the journey takes you. These projects tend to have a lot of business impact right out of the gate. Business report users will be delighted with what you can do in a short time with Power BI and they will want more. You’ll get excited and expand the project to include more data or more dimension to the data you have. It is almost inevitable that you will experience two common challenges:

  • Scope creep
  • Data quality issues

These are not necessarily “problems” per se but realities to acknowledge. Building a solid, foundational data model is challenging in under these circumstances. The good news is that Power BI let’s you explore options quickly and allows you to try different approaches while you sort-out the formal business and technical requirements. The typical outcome is to start over with a new project where you will be well-prepared to use a cleansed data source and design to a well-defined set of requirements within the new project scope. An experienced BI practitioner will set this expectation with the business and either carefully evolve certain elements into the final project and toss the rest, or use the first attempt as a throw-away prototype and start over. Just know that this is a natural part of the process.

If you work in an software development group in an IT organization with a formal application lifecycle management discipline, you are probably accustomed to using team development a code repository, version control and differencing tools to manage a shared code base. The main components of Power BI came from a long heritage of IT-centric development so at its core, objects (like tables, fields and measures) have properties and metadata descriptors that can be versioned, differenced, merged and scripted. But what has made Power BI so successful is that is is primarily designed for self-service reporting. Power BI Desktop is a beautifully streamlined product that packages data transformation queries, model tables, data and reports into a tidy package but it is not an IT development tool and doesn’t have these capabilities built in – nor will it. That’s not what Power BI Desktop was designed for. The good news is that the community has rallied to develop tools to meet the needs of IT developers who work in teams to develop, test and deploy formal, enterprise-scale BI reporting solutions. Power BI and the rest of the Microsoft BI platform – can be scripted and automated to create custom deployments.

Right-sizing solutions

Before creating Power BI, earlier versions of Microsoft’s Business Intelligence components existed as separate products. BI reporting projects took many months to design, develop and deploy. By contrast, today you can open-up Power BI Desktop and create a quick “report” (PBIX file). In this form, the file contains data transformation queries, an in-memory data model with measures and other calculations, and report pages that include the interactive visuals. This design typically meets the needs of small and intermediate sized projects.

One of the first questions to consider is whether the solution you intend to create with Power BI is a quick informal project or a formal project – or may become a formal project in the future. Informal projects don’t necessarily have to conform to all the design best practices. They are limited in scope and typically meet a very specific and short-term need. Formal projects are built on best practice design standards to endure a certain degree of expanding scope and are designed for longevity. They have a business sponsor or champion who drives the business requirements and at least one developer who is responsible for designing a solution that meets those requirements. Formal projects are typically testing, signed-off an then deployed in accordance with an established development discipline or application lifecycle process so they can be supported by the business. If your organization has a process for delivering and supporting company-sanctioned applications and reports, then you will need a roadmap to transition from an informal project to a solution that the business is prepared to support. A few common questions that come up in these discussions are:

  • How is the source data procured and maintained?
  • Who is responsible for maintaining that data?
  • Who from the business defines the reporting requirements and signs-off on Power BI reports meeting those requirements?
  • Who owns the development of the Power BI solution (data model and reports)?
  • Who is the developer’s backup if they become unavailable?
  • Are the requirements and project assets sufficiently documented to support such a transition?
  • What are the security requirements?
  • Who and how will users be given access to reports or dataset(s) for ad hoc analysis?
  • Are users assigned to report and dataset permissions directly or through group membership?
  • Should users have conditional or restricted access to data within the Power BI dataset?
  • What is the process for requesting new reporting features, adding features to the existing solution, testing and deploying updates?

Personal BI projects, where a data analyst imports data, creates reports and then publishes a report for their own use serves an important need in business. These reports can easily be promoted to a departmental or business area solution by sharing the report with others. As the scope of the project continues to expand, there are several important considerations that define the threshold between an informal project, designed for quick development and convenience, and a formal project; intended for longevity and long-term business support. For each project component, I start with a typical informal project scenario and then provide recommended practices to to plan for futureproofing.

Planning for separation

Designing transformation queries, data model and reports in one PBIX file is convenient and offers quick troubleshooting, debugging and redesign. However, it also limits development to one person at a time and promotes a monolithic solution. Separating the data model from the report files helps to promote self-service and focused report design. The certified or promoted dataset is deployed, secured and managed as a separate object. Reports are connected to a Power BI dataset live connection and any number of reports can connect to a published dataset.

This pattern supports both fully IT-managed projects with multiple developers and it supports self-service report design using certified and promoted datasets. After the data model design is stabilized and locked-down, report designers simply focus on report creation and data analysis by connecting to the secured dataset. Any tools and products capable of connecting to Power BI or SQL Server Analysis Services (SSAS/AAS) can be used for reporting and ad hoc analysis.

Source data

Spreadsheets and files extracted from reports, manually manipulated using Excel or other line-of-business applications have limited utility as reliable data sources. Manually preparing spreadsheets and source data files is only feasible for small sets of data and one-time or manual data loads. An analyst user can easily remove, rename or add a column or formula to a sheet that will break or negate the import process.

Futureproofing: Data from source systems can be loaded into a managed database or data lake before using Power Query to load the data model. Data from files (if carefully protected and maintained) may be transformed, cleansed and validated before it is ready to be loaded. Using “brute force” transformation steps to replace values and cleanse records one step at a time that can be slow and difficult to manage as the complexity and volume increases. Reduce the number and complexity of query steps and apply the following recommendations for Power Query design optimization.

Use parameters in Power Query to manage any variable information such as database servers, database names (e.g. DEV database, QA database & PROD database) and file paths.

Planning for data volume

In smaller, informal projects we typically designed for a limited set of data and fixed time-frame. If manageable, all production data can be loaded into the working PBIX file on a developer’s desktop.

Futureproofing: When working with large data volumes, a subset of data should loaded into the desktop dataset for development. Query parameters are used to expand the number of records (usually using a date range) loaded after the dataset is deployed for production use. Groups and ranges of data records, often partitioned by months or years, are defined and stored separately so a large volume of records don’t need to be reloaded along with new records.

Power Query design practices

Using the robust Power Query design user interface (ribbon and menu options), you can build sophisticated and complex transformations that work well with moderate data volumes. With a small number of query steps, default names like “Rename Columns” and Change Data Type” are fine but it can be difficult to trace the steps and remember where and why you performed each step.

Futureproofing: Tables and fields should be meticulously renamed to use standard friendly names, categorized and optimized with appropriate data types. Unused columns are removed to save space and improve performance. Spend extra time to get the field names right before creating dependent steps. (see; Chris Webb: Names, Tables, Columns & Measures In Power BI)

Consolidate redundant query steps – like those used to rename columns, remove columns and change data types – into a single step in the query.
Attribute fields needed for grouping and filtering should be moved to separate dimension tables. Dimension unique keys are defined with corresponding fact table keys.
All numeric columns used for aggregation (which share the same level of grain) should be extracted to fact tables, with corresponding dimension keys.
Row-level calculated columns should be defined in either in database views or Power Query tables rather than DAX calculated columns.

Promote query folding by using database tables, views or table-valued user-defined functions in the database rather than writing SQL statements in Power Query. Test and optimize queries to support query folding by checking to see in “View Native Query” is an available right-click menu option on each query step. Power Query also doesn’t work well with stored procedures.

Data loading & refresh management

For large fact tables, design them to be “tall” rather than “wide” by removing unneeded columns or moving them into dimension tables. Tall tables should only include columns that compress efficiently, such as numeric values and short, non-distinct text values. Do not store a single unique key column in a fact table.

If large tables take too long to load or cause the PBIX file to exceed a reasonable size for development (no more than 500 MB), use parameters to limit the range of records. To manage the table size once deployed tot he service, do one of the following:

  1. Use start and end date range parameters to filter a Date or Date/Time type column in the table. Use this to limit the working set on the development desktop and then to expand the range of records in the service.
  2. Similar to step one, use a pair of Date/Time parameters with a range filter on a Date/Time type column and follow the product documentation to implement incremental refresh. This will automatically partition the table records on specified calendar date parts (e.g. weeks, months, years) and then only load changes.
  3. Devise a table partitioning strategy and implement partitions using Tabular Editor. Use PowerShell or the XMLA read/write endpoint to schedule and execute TMSL or XMLA script to manage partition processing.

Data modelling

Many modelling techniques that perform well when tested on small data sets will slow down with large tables. These include relationships on bridging tables, many-to-many relationships, bi-directional relationship filters and multi-measure layered calculations.

Avoid using SWITCH statements over already complex DAX expressions or referenced measures. Avoid using iterator functions (like SUMX) with complex row-by-row predicate logic. Separating calculation logic into separate measures is a useful design pattern to segregate business logic but it can create performance bottlenecks. Identify slow measures and consider building streamlined measures using alternate patterns when bottlenecks are discovered.

Import mode tables are generally faster and more flexible than DirectQuery mode tables. However, DirectQuery is a good option for very tall transactional tables where simple aggregations are used, rather than complex DAX measures. If Import and DirectQuery mode tables are both used in a composite data model, consider creating a summary table using Import model and a detail table using DQ, and then define aggregates to cache certain query results in memory (to compensate for the normal performance degradation resulting from the query translation in DirectQuery.)

There is so much more to discuss at this point but I’ll need to address additional topics in later posts in this series titled “Doing Power BI the Right Way“. Topics include:

  • Service vs on-premises
  • Interactive vs operational reports
  • Spreadsheet reporting
  • Iteration vs redesign: When to start over
  • Designing for scale

Power Query or Power BI Dataflows

Multi-tier and advanced self-service solutions can be architected by further separating the transformation queries from the data model using Power BI dataflows. These are server-hosted Power Query/M queries that are both designed and managed in the Power BI service.

Dataflows expose several integrations with the Microsoft Azure cloud services, such as the Common Data Service and Azure Data Lake Gen2 Storage. Dataflows also have integrations with AutoML machine learning models.

The advantages and disadvantages of dataflows should be carefully considered before jumping in head-first. Power Query in Power BI Desktop is a fast, right query development environment that provides nearly immediate feedback. By contrast, the browser-based dataflows designer is similar in function but slower and has a less-robust query design feature set. Some developers choose to develop Power Query in Desktop and then port their M query code to dataflows as needed.

Stick around. There is a lot more coming in this series in the weeks ahead:

Doing Power BI the Right Way

  1. Futureproofing Power BI solutions
  2. Preparing source data for Power BI
  3. Choosing the best option to shape and transform Power BI data
  4. Power Query design best practices
  5. Power Query in dataflows or Power BI Desktop
  6. Data modeling essentials and best practices in Power BI and AS tabular
  7. Validating data model results
  8. Planning for separation – data models and reports
  9. Power BI delivery options
  10. Choosing the right report type: analytic or paginated
  11. Designing and managing large datasets in Power BI
  12. Multi-developer and lifecycle management for Power BI
  13. Certified reports, certified datasets & the self-service mindset

To wrap up this edition of the series, I will leave the rest of my outline points as teasers for subsequent posts. Please freely comment with your thoughts about what to include in these later editions.

The rest of these are random thoughts left from the outline for the series. Feel free to comment.

Future topics:

  • Define the technical audience
  • Establish solution ownership
  • Continuation plan (if they were to leave?)
  • Who will maintain & manage the solution after deployment?
  • User access
  • Version updates
  • Support

Know and understand the business audience

  • Report/Dashboard Consumer
  • Skilled Data Modeler
  • Analyst, Data Scientist
  • IT BI Developer
  • Planning for capacity
  • Certifying reports and datasets: Gaining business trust
  • Validating results
  • Team collaboration
  • Project file sharing
  • Promote continuous improvement
  • Version management
  • Managing deployment
  • Power Query and Dataflows
  • Promote and support repeatable design
  • Templates and layouts
  • Planning for security
  • Access to reports & dashboards
  • Publish to web
  • Embedding to a business portal
  • Embedding for external access
  • Access to dataset(s) for ad hoc analysis and future report development
  • Conditional data access / role-based access / row-level security

Power BI External Tools: Reading the Tea Leaves

I’m in the midst of writing another post in the series: “Doing Power BI the Right Way” that I started last week. However, this topic is super important so I wanted to pause and spend just a few minutes to share some thoughts. Why is Microsoft (particularly, the Power BI product team) promoting the use of tools that they don’t develop nor officially support?

This week, Christian Wade from Microsoft announced on the Microsoft Power BI blog that a new ribbon “External Tools” was added to the preview features of Power BI Desktop in the July update. In earlier posts, Christian has promoted community developed tools such as Tabular Editor, DAX Studio and the ALM Toolkit that can be used as replacements or extensions to the Power BI development experience. These three tools are autmatically added to the ribbon by default if they are installed, but you can add your own applications by following the instructions in the this Microsoft document titled Using external tools in Power BI.

What are these three “promoted external tools”?

Tabular Editor is an open source application developed primarily by Daniel Otykier. The source code resides in GitHub where multiple community members have contributed to the project. Likewise, DAX Studio is an open source project headed up by Darren Gosbell. The ALM Toolkit for Power BI is an evolution of an earlier Visual Studio extension project from Christian Wade called the BISM Normalizer, used primarily to compare and merge changes between SSAS/AAS Tabular projects and databases. Working with MAQ Software, he evolved the code into a stand-alone application which is optimized for Power BI data models. Each of these tools are free and supported by the community but not officially supported by Microsoft.

There is an important message here

I use all three of these applications regularly. Before the new ribbon, I would open my PBIX file in Power BI Desktop so that the data model was loaded into memory. Then, I would open the needed utility application from the Windows program menu, and then connect to the Power BI data model using the local port address. The new ribbon conveniently allows me to open the installed application and automatically connect to the data model I have loaded. Honestly, it only saves a few steps but the presence of the new ribbon sends an important message.

What does that mean, exactly? Unlike many other vendors, Microsoft has a strong and thriving community who promote and support their products. Much of this is simply volunteerism backed by Microsoft’s MVP, Partner, user group and Certification programs. I typically don’t contact Microsoft product support unless I have a bona fide show-stopping error but I’m certain that if you were to open a support case and tell them that DAX Studio is acting up, they would send you to the GitHub repo and suggest that you chat with other community members.

I worked with a consulting client who had a large investment in database and BI tools from Oracle, IBM and MicroStrategy. As we got started on a Microsoft BI project, I IT operations that all of the developers would need to install and frequently update several software tools. Any software needed to be added to the list of corporate approved software which is referenced by the outsourced support helpdesk to remotely install software for an employee. For each application, we needed to provide the specific product version, licensing agreement and cost, service level agreement and support agreement with the software vendor. Software could only be installed from a network file share and not over the Internet. There was a form and a formal approval process that didn’t support my suggestion that open source tools by installed and that the free, officially-supported applications needed to be updated every month. Altering the approval process required multiple meetings and escalations to top-level leadership who had never heard of a vendor promoting “community-supported” development tools and entertaining such a strange notion was a big paradigm shift – but it is our reality and one of the reasons that Power BI is a successful platform.

External Tools: What Does This Mean?

Microsoft has several forums for product users and partners to make suggestions and to ask for new features. If you search Ideas.PowerBI.com, where any user can ask for new features, you will see many many requests to add IT/enterprise features to Power BI Desktop such as version control, multi-developer support, partitioning and other capabilities that already exist in Analysis Services projects. Adding all these things to Power BI Desktop would clutter the product and defeat the goal of keeping self-service model/report development simple. For the IT BI developer, we have Visual Studio extensions for data model and paginated report design but the process and overhead required to maintain those project templates and extensions for different versions of Visual Studio is complicated to say the least (see my previous post “I am done using Visual Studio for BI data model development“).

Bottom line

Community-supported development tools are an essential part of the BI development experience. These are necessary utilities to have in your toolbox if you are a serious BI developer. The Microsoft product team will not only tolerate them but rely on them, and promote these tools in features like the new External Tools ribbon so that we have best-of-breed tools to fill gaps as the platform continues to expand.

Doing Power BI the Right Way

This is an introduction to a series of posts and pages that will provide a comprehensive set of best practices for successful Power BI solutions. In previous posts, I have asked readers to suggest topics for future posts. Based on that and other feedback, I will be addressing questions and suggested topics. The topics list at the end of this post is a brainstorm list and I ask that you help me make sure it is complete. My goal is to provide a set of guidelines and practices that provide the best chance of success as you navigate many decisions about how to stage and transform source data, how and where to perform data shaping and calculations, how to model data and the best way to visualize the results. The biggest question of all may be how to make the right decisions so that the small project you design today will work when you add more data, more users and transition into a formal, managed solution.

There are many fantastic resources to learn about Power BI and rest of the Microsoft BI and reporting platform; but learning about Power BI and the choosing among design options can be like drinking from multiple firehoses at full pressure at the same time. I will be the first to admit that my “best practices” are “my opinions”. In many cases they work consistently for me but points are debatable and open for discussion. I’ll tell you when I have a very strong opinion about something being done a certain way, or when I have found a pattern that works for me and that I offer for your consideration. I’m not always right… just ask my wife :-). Please comment, ask and offer alternative points of view.

Rather than offering another training course or duplicating the contributions that so many others in the industry make through their blogs, courses, books and articles; this will be a condensed set of guidelines about the many choices you must make when designing a solution. In the posts to follow, I will reference other resources and discussions on various topics.

Just tell me what to do

Any attempt to apply universal best practices to Power BI solution design is a slippery slope. The tools are so flexible and powerful, and the requirements of each project are so varied that it is challenging to establish a set of steps or rules that, if followed, will always yield the absolute best design for a given scenario. With that out of the way, I’ll say this: In my job, I see a lot of poorly-designed Power BI projects. I’ve worked with dozens or scores (maybe even hundreds?) of consulting clients who bring us projects – some partially completed, some finished, and many that are just broken – to be fixed or completed. My reactions range from “that’s just downright wrong” to “hmmm… I wouldn’t have done it that way but I guess it will work for the time being”. I try not to cast stones and do, on occasion, realize that others have found a better way to solve a problem. I don’t have all the answers but I do have a lot of experience with Microsoft Business Intelligent solution design, and have learned many good practices and design patterns from other community leaders and many successful projects over the past twenty or so years.

A little less conversation and a little more action

Let’s start with a simplified flowchart and condensed decision tree. This first whiteboard drawing is the first half of the Power BI design process, ending with the data model, before measures, visualization and solution delivery. There is a lot more but I think this is a good starting point. Let’s start the conversation here and then I will enhance this post with a more complete list of topics.

Best practice guidelines topics

The following topic list will serve as a link menu for future posts. Expect this list to be updated and completed:

  1. Futureproofing Power BI solutions
  2. Preparing, shaping & transforming source data
  3. Choosing the best option to shape & transform data
  4. Power Query design best practices
  5. Power Query in dataflows or Power BI Desktop
  6. Data modeling essentials and best practices in Power BI and AS tabular
  7. Validating data model results
  8. Planning for separation – data models and reports
  9. Power BI delivery options
  10. Choosing the right report type: analytic or paginated
  11. Designing and managing large datasets in Power BI
  12. Multi-developer and lifecycle management for Power BI
  13. Certified reports, certified datasets & the self-service mindset

I Am Done Using Visual Studio …for BI data model development

For several years, Visual Studio has been my go-to tool for designing semantic data models used for Business Intelligent reporting. Back in 2005, I used the Business Intelligence Development Studio (BIDS) Visual Studio add-in for SSIS, SSRS and SSAS projects to develop BI solutions with multidimensional cubes. In 2012 when Microsoft began the transition from on-disk cubes to in-memory SSAS Tabular models, I used SQL Server Data Tools (SSDT) to create tabular models. It was a rocky road at first. The Tabular designer was fragile to put it mildly.

Enter Power BI… Initially intended for self-service data model and report design, Power BI Desktop has quickly grown into a robust and full-featured BI design tool. Not only does Power BI Desktop include a lot of great features, it is stable and streamlined. It’s a joy to use compared with my early experiences using SSDT for tabular model design. I prefer to use Desktop to perform model design. It’s faster, more convenient and just easier than SSDT. However, at some point I the life of a project it just makes more sense to transition the data model to an enterprise-scale effort.

Now, before anyone has a chance to comment and say “Paul, what the #$@! are thinking? Visual Studio is an essential tool and there are certain things that you can’t do with out it!”, I agree and will continue to use SSDT for a few key features. So, yes, I am not absolutely done using Visual Studio for managing projects other than SSAS, and perhaps for code check-in …I’ll finish this part of the story in a bit.

I want to be clear – I love Visual Studio.  It’s a great product for developing software and a variety of business and data solutions. However, history has demonstrated that the notion of stitching together several different products and expecting them all to just work together seamlessly is just untenable. Without getting into all the reasons that it has been difficult for Microsoft to develop and maintain a rock-solid tabular model design add-in for Visual Studio, contrast that effort with the evolution of the Power BI product.  The Power BI product team is entirely focused on developing one product by a development team under unified leadership, with a focused set of objectives. Negotiating co-development of any product by several different teams is difficult within any organization, particularly one as large as Microsoft. The reason that new features can be added weekly to the Power BI service and monthly to Power BI Desktop is that one product team manages all those features.

Some of you will remember the time when the Business Intelligence message from Microsoft was that we supposed to create solutions relying on coordinated components of many products like SQL Server (relational, SSIS, SSAS and SSRS), Windows Server, SharePoint and Office – all orchestrated to work together seamlessly. It was a good idea – and still is in moderation – but this approach produced a delicate and complicated beast that was difficult to manage and had many potential points of failure.

One of the reasons Power BI Desktop is such a wonderfully streamlined product is that the feature set is optimized for data analysts and not for IT developers. To maintain a streamlined product, we are not at all likely to see enterprise capabilities (like version control, multi-developer code merging and scriptable objects) added to this product. These capabilities do exist, however, for Analysis Services projects and community supported tools like Tabular Editor and DAX Studio. But now (drum-roll, please) Power BI dataset can be developed and deployed to a workspace using enterprise tools through the magic of the XMLA endpoint.

The Visual Studio/SSDT Quandary

<RANT>

Call it a learning disability, but I have tried time and time again to use the Visual Studio tabular designer to manage SSAS projects with the same outcome.  Smallish demo and POC projects go well but not-so-much when tackling the complexities of product-scale design. I guess it is just my natural optimism to hope things work out better than they did last time, but the laws of the universe dictate that if you do the same thing, history will be repeated.

Here’s how it goes… I start developing a data model in SSDT by importing some tables and queries, and adding relationships and measures.  All good, right?  At this point in the timeline, I often convince myself that development environment is stable and that everything will work-out so I forge ahead, believing that all will be good. I then add some more tables and a whole bunch of new DAX calculations – and soon everything goes to hell.  The model designer stops responding or behaves sporadically, Visual Studio crashes, the model definition file gets corrupted and I then I remember that I’ve been down this dark road before.

Recounting the painful past, it is frustrating to open a support ticket and explain to the engineer that “sometimes when I do that, this happens but not always” and “in all the confusion, I really can’t remember exactly how I got to this state.”

Pondering these memories, then I draft a blog post titled “I Am Done Using Visual Studio”

</RANT>

I sincerely appreciate the efforts of Kasper DeJonge from the SSAS product team back in 2012 as we spent hours in remote meetings trying to reproduce various odd behaviors in the tabular designer with a large data model. The fundamental problem was that the Model.bim file, which defines all the objects in the data model, was an enormous XML document (ours was approaching 100,000 lines.) Every change in the designer required the entire file to be re-written to disk and the loaded back into memory. Things improved significantly in 2016 and 2017 when the model definition was streamlined using JSON rather than XML, and the file structure was simplified to reduce the file size. Similar meetings with several other product leaders have proven that the product team are seriously dedicated to optimizing the enterprise tabular model experience.

I’m all about solutions and not just ranting about problems.  So what’s the answer? How should we manage enterprise BI data model and Power BI solutions from now one? Using Tabular Editor alongside Visual Studio is really a best-of-both-worlds experience. You can open the Model.bim file stored in the Visual Studio SSAS project folder.

Tabular Editor

Tabular Editor is a superb tool for developing and managing tabular data models for SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS) and Power BI. It is a community supported tool created by Daniel Otykier, a Microsoft MVP and Senior Business Intelligence Architect with Kapacity.dk in Denmark. The most comprehensive resource to find this and other community supported BI tools for the Microsoft platform is on the Italians’ site at SqlBi.com/Tools

If the project is under source code control, changes made with Tabular Editor will be detected and can be synchronized with the remote source repository from Team Explorer in Visual Studio.

Here are some quick facts and recommendations:

Power BI and Version Control
Power BI Desktop files (.PBIX) do not support version control and code merging.
Recommendation:
Don’t try to do this – It will turn out badly. Starting model design in Power BI Desktop will save you time but once you transition to the Model.bim file format, use Tabular Editor.

Separating Reports and Dataset
A monolythic PBIX file created with Power BI Desktop containing reports, data model and queries is simple and easy to manage until you need to move beyond several limitations that this imposes.
Recommendation:
Power BI reports and datasets (data models) should be managed separately in all serious projects. Period. …whether you need to transition the data model to Model.bim or not.
Separating Power BI reports from the data model/dataset has many advantages which include allowing report and data model development to be performed in parallel and by different team members. This is an absolute must to create a certified dataset for users to connect and do their own reporting and analysis.

Visual Studio and Version Control
Visual Studio SSAS projects allow you to manage version control.
Recommendation:
This is a good thing. Keep doing this but use Tabular Editor as your primary model design tool.

A data model stored as a Model.bim file can have changes compared, split and merged between data model version files, deployed AS databases or Power BI datasets. Manage integrated source control with Azure DevOps or GitHub. Check-in changes, branch, merge, push and pull changes made by other developers but don’t use the Visual Studio Compare tool. Dependencies within the model definition can easily get you into trouble

Tabular Editor
Tabular Editor is a far superior design experience than Visual Studio. It is streamlined, easy to use and it won’t blow up when writing measure calculations. You can switch back and forth between tools since each tool has features that the other lacks. Just be sure to save and close the model file with one tool before opening it in the other …AND MAKE BACKUPS! The more that I do this, the more I prefer using Tabular Editor.

Tabular Editor doesn’t have a graphical model designer so I prefer to use Visual Studio to model tables and relationships. Set table and column properties, create calculated columns and measures, manage partitions and other tabular model features in Tabular Editor.

Start in Power BI Desktop and Migrate to an Enterprise Model
From Power BI Desktop, save the file as a .PBIT (template) which and then be opened in Tabular Editor. Once you save the file to the .BIM format, this is a one-way trip since a an Enterprise model cannot be saved back to a PBIT or PBIX file. Of course, if you start designing the data model in Visual Studio, there is no need to resave the model. You can just and use Tabular Editor to make new model enhancements.

Power BI Premium, AAS & SSAS
Power BI Premium capacity is required to deploy a Model.bim file as a dataset in a Power BI workspace.

Power BI Premium capacity is the E-ticket and is the best way to access all Power BI enterprise capabilities. With the XMLA endpoint, it will make more sense for most customers to deploy enterprise data models as datasets to a workspace rather than Azure Analysis Services (AAS) models.

Future-Proofing Data Models

If your organization chooses not to use Premium, which might be a more economical choice until you have enough licensed users to justify the cost, you can use AAS data models or on-prem SSAS data models to exceed the capacity limits of Power BI datasets under only Pro licensing.

If industry certification or compliance restrictions prevent your organization from using Power BI in the cloud, using enterprise models is the norm. Use a local instance of SQL Server Analysis Services Tabular. If you move to the cloud in the future, the transition should be relatively simple.

Keep in mind that Premium Capacity, or perhaps a more attractive licensing alternative, may be in your future so architect solutions in such as way that you can easily make that transition.