Microsoft Power BI Reviews

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

Buyer's Guide

Microsoft Power BI

February 2025

Free Report: Microsoft Power BI Reviews and More

Learn what your peers think about Microsoft Power BI. Get advice and tips from experienced pros sharing their opinions. Updated: February 2025.

DOWNLOAD NOW

838,533 professionals have used our research since 2012.

For how long have I used the solution?

What do I think about the stability of the solution?

How are customer service and support?

What was our ROI?

Which other solutions did I evaluate?

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What is most valuable?

How has it helped my organization?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and technical support?

Which solution did I use previously and why did I switch?

How was the initial setup?

Which other solutions did I evaluate?

What other advice do I have?

#21: Avoid using discretization buckets for your dimension attributes

Discretization buckets lets you group numerical attributes into ranges. Say you have a customer dimension including the age of the customer you can use this feature to group them into age clusters such as 0-5, 6-10 and so on. While you can tweak how the algorithm creates groups and even provide naming templates for the groups you still have relatively limited control over them. Worst case scenario: A grouping is removed / changed by the algorithm which is referenced in a report. A better way of grouping these attributes is by doing it yourself either in the data source view or a view in the database (there will be a separate tip on this). This way you have complete control over the distribution of values into groups and the naming of the groups.

#22: Do not build a SSAS solution directly on top of your source system

SSAS has a couple of features that enable it to source data directly from a normalized data model typically found in business applications such as ERP systems. For instance you can “fake” a star schema through queries in the data source view. You can also utilize proactive caching to eliminate any ETL to populate your cube with data. This all sounds very tempting but unfortunatly I have never seen this work in reality. Unless you are working with a very small source system with impeccable data quality and few simultanous users you should avoid the temptation for all the usual reasons: Proactive caching will stress your source system, data quality will most likely be an issue, integrating new data sources will be nearly impossible,etc. There is a reason BI projects spend 70-80% of their time working with modelling and integrating data.

#23: Deploy SSAS cubes with the deployment tool

If you are working with multiple environments (dev/test/prod) do not use the deployment functionality of visual studio to deploy to another environment. This will overwrite partitions and roles that may be different between the environments. Use the deployment wizard.

#24: Remember that your SSAS cubes are a single point of failure

Keep in mind that most client tools do not cope well with changes to SSAS data models. Any renames or removals you do in the model will most likely cause clients that reference those entities to fail. Make sure you test all your reports against the changed model before deploying it to production. Also, if you allow ad-hoc access to your SSAS solution be aware that users may have created reports that you do not know about. Query logging may help you a little here (it gives you an indication of which attribute hierarchies are in use). The best way to avoid all of this is to thoughtfully design your cube and the naming of your SSAS objects so that there is no need to change or remove anything in the first place.

#25: Avoid “real time”

“Real time” means different things to different people. Some interpret it as “simultaneous to an event occurring” while others have more leeway and have various levels of tolerance for delays. I prefer the term “latency”: How old can the data in the BI solution get before it needs to be refreshed?. The lowest latency I have ever implemented is two hours. That is hours not minutes. I know this does not sound very impressive but that is honestly the best I have been able to do at a reasonable cost. When doing “real time” you need to consider a lot of factors: Partitioning, changes to dimensions, ROLAP vs MOLAP / direct query vs xVelocity, source system access, how to administer it, etc., etc. These things add up quickly to a point where the value simply does not justify the cost.

#16: Implement reporting dimensions in your SSAS solution

Reporting dimensions are constructs you use to make the data model more flexible for reporting purposes. They usually also simplify the management and implementation of common calculation scenarios. Here are two examples:

• A common request from users is the need to select which measure to display for a given report in Excel through a normal filter. This is not possible with normal measures / calculations. The solution is to create a measure dimension with one member for each measure. Expose a single measure in your measure group (I frequently use “Value”) that you assign the correct measure to in your MDX script / DAX calculation based on the member selected in the measure dimension. The most frequently used measure should be the default member for this dimension. By doing this you not only give the users what they want, but you also simplify a lot of calculation logic such as the next example.
• Almost all data models require various date related calculations such as year to date, same period last year, etc. It is not uncommon to have more than thirty such calculations. To manage this effectively create a separate date calculation dimension with one member for each calculation. Do your time based calculations based on what is selected in the time calculation dimension. If you implemented the construct in the previous example this can be done generically for all measures that you have in your measure dimension. Here is an example for how to do it tabular. For dimensional use the time intelligence wizard to get you started.

#17: Consider creating separate ad-hoc and reporting cubes

Analysis Services data models can become very complex. Fifteen to twenty dimensions connected to five to ten fact tables is not uncommon. Additionally various analysis and reporting constructs (such as a time calculation dimensions) can make a model difficult for end users to understand. There are a couple of features that help reduce this complexity such as perspectives, role security and default members (at least for dimensional) but often the complexity is so ingrained in the model that it is difficult to simplify by just hiding measures / attributes / dimensions from users. This is especially true if you use a “reporting cube” which I talked about in tip #16. You also need to consider the performance aspect of exposing a large, complex model to end user ad-hoc queries. This can very quickly go very wrong. So my advice is that you consider creating a separate model for end users to query directly. This model may reduce complexity in a variety of ways:

• Coarser grain (Ex: Monthly numbers not daily).
• Less data (Ex: Only last two years, not since the beginning of time).
• Fewer dimensions and facts.
• Be targeted at a specific business process (Use perspectives if this the only thing you need).
• Simpler or omitted reporting dimensions.

Ideally your ad-hoc model should run on its own hardware. Obviously this will add both investment and operational costs to your project but will be well worth it when the alternative is an unresponsive model.

#18: Learn .NET

A surprisingly high number of BI consultants I have met over the years do not know how to write code. I am not talking about HTML or SQL here but “real” code in a programming language. While we mostly use graphical interfaces when we build BI solutions the underlying logic is still based on programming principles. If you don’t get these, you will be far less productive with the graphical toolset. More importantly .Net is widely used in Microsoft based solutions as “glue” or to extend the functionality of the core products. This is especially true for SSIS projects where you quite frequently have to implement logic in scripts written in C# or VB.net but also applies to most components in the MS BI stack. They all have rich API’s that can be used for extending their functionality and integrating them into solutions.

#19: Design your solution to utilize Data Quality Services

I have yet to encounter an organization where data quality has not been an issue. Even if you have a single data source you will probably run into problems with data quality. Data quality is a complex subject. Its expensive to monitor and expensive to fix. So you might as well be proactive from the get-go. Data Quality Services is available in the BI and Enterprise versions of SQL Server. It allows you to define rules for data quality and monitor your data for conformance to these rules. It even comes with SSIS components so you can integrate it with your overall ETL process. You should include this in the design stage of your ETL solution because implementing it in hindsight will be quite costly as it directly affects the data flow of your solution.

#20: Avoid SSAS unknown members

Aside from the slight overhead they cause when processing, having unknown members means that your underlying data model has issues. Fix them there and not in the data model.

# 6: Use a framework for your Integration Services solution(s) because data is evil

I know how it is. You may have started your ETL project using the SQL Server import / export wizard or you may have done a point integration of a couple of tables through data tools. You might even have built an entire solution from the ground up and been pretty sure that you thought of everything. You most likely have not. Data is a tricky thing. So tricky in fact that I over the years have built up an almost paranoid distrust against it. The only sure thing I can say is that it will change (both intentionally and unintentionally) over time and your meticulously crafted solution will fail. Best case scenario is that it simply will stop working. Worst case scenario is that this error / these errors have not caused a failure technically but have done faulty insert / update / delete operations against your data warehouse for months. This is not discovered until you have a very angry business manager on the line who has been doing erroneous reporting up the corporate chain for months. This is the most likely scenario. A good framework should have functionality for recording data lineage (what has changed) and the ability to gracefully handle technical errors. It won’t prevent these kinds of errors from happening but it will help you recover from them a lot faster. For inspiration read The Data Warehouse ETL Toolkit.

#7: Use a framework for your Integration Services solution(s) to maintain control and boost productivity

Integration Services is a powerful ETL tool that can handle almost any data integration challenge you throw at it. To achieve this it has to be very flexible. Like many of Microsoft’s products its very developer oriented. The issue with this is that there are as many ways of solving a problem as there are Business Intelligence consultants on a project. By implementing a SSIS framework (and sticking with it!) you ensure that the solution handles similar problems in similar ways. So when the lead developer gets hit by that bus you can put another consultant on the project who only needs to be trained on the framework to be productive. A framework will also boost productivity. The up-front effort of coding it, setting it up and forcing your team to use it is dwarfed by the benefits of templates, code reuse and shared functionality. Again, read The Data Warehouse ETL Toolkit for inspiration.

#8: Test and retest your calculations.

Come into the habit of testing your MDX and DAX calculations as soon as possible. Ideally this should happen as soon as you finish a calculation, scope statement, etc. Both MDX and DAX get complicated really fast and unless you are a Chris Webb you will loose track pretty quickly of dependencies and why numbers turn out as they do. Test your statements in isolation and the solution as a whole and verify that everything works correctly. Also these things can have a severe performance impact so remember to clear the analysis services cache and do before and after testing (even if you have cache warmer). Note that clearing the cache means different things to tabular and dimensional as outlined here.

#9: Partition your data and align it from the ground up.

Note that you need the enterprise version of SQL Server for most of this. If you have large data sets you should design your solution from the ground up to utilize partitioning. You will see dramatic performance benefits from aligning your partitions all the way from your SSIS process to your Analysis Services cubes / tabular models. Alignment means that if you partition your relational fact table by month and year, you should do the same for your analysis services measure group / tabular table. Your SSIS solution should also be partition-aware to maximize its throughput by exploiting your partitioning scheme.

#10: Avoid using the built-in Excel provider in Integration Services.

I feel a bit sorry for the Excel provider. It knows that people seeing it will think “Obviously I can integrate Excel data with my SSIS solution, its a MS product and MS knows that much of our data is in Excel”. The problem is that Excel files are inherently unstructured. So for all but the simplest Excel workbooks the provider will struggle to figure out what data to read. Work around this by either exporting your Excel data to flat files or look at some third party providers.

A quick look at the whole idea on another weblogs gives you a sense that all of them just talked about very brief things like report refresh feature in BO or cube feature in MS Analysis Service. I choose MS BI and I want to share my reasons and opinions on why I choose it and give you another quick but a little deeper compare on these two Business Intelligence platforms.

As we all know both Business Objects and Microsoft are big companies who are working on BI solutions and both have their own advantages. It’s not true to compare them in term of which one is better, we have to check what is our requirements and then depend on requirements take the decision whether MS or BO. A vision like this could help us relief from religious decisions against a software or technology.

In a BI architect first of all we have the data store level, I mean the storage of the raw data not the stage or olap cubes or universe data source, I mean the first place of our data. This is important to know that where your raw data is and what is the type of storage used to store them. Whether file system or Access or Fox database or a complex database solution like oracle, sqlserver or a web service can made our place of raw data. We have to check our tools against them; check to see which one gives us a smooth way to transfer them among ETL process to destination. So take a look at what Business Objects gives us.

There is a Data Integration platform in Business objects but the problem is that you have to buy that separately because it is not shipped with the BI system. In Microsoft sqlserver enterprise you have all the services and features needed for this part of the game. SSIS is the service that sqlserver deliver for data extract, integration and load. Both product gives you the ability to enhance the data quality and data cleansing portion of your integration phase, but when we down to details things change a little to the Microsoft side, because of the ability of using your Dot.Net knowledge to write complex parts of ETL process you have more room to think and do whatever you want in your process, and in BO side it is always look simple and it’s really not easy to take complex situation into it. There are advantages and disadvantages on this. First you can do many things with the ability of dot.net code but it could give you complexity in your development so you have to decide on your situation, if things looking normal both could fit your need, but if the situation is not stable and you have to make yourself ready for the changes in future it’s better to get the power of SSIS and spend a little more time development today to create a powerful and easily changeable mechanism that could help you in future. You can also do that with Business Objects Data Integration but you have to spend more bucks for the development and changes of ETL processes because development cost in Business Objects solutions is always a nightmare for a project.

At this point we have a brief understanding of differences in ETL process between two vendors, so it’s good time to take a look back to the source database. Here is a very quick answer, if you use mostly MS products to store your transactional data then take your decision and move to MS for a robust and compatible BI platform. Business Objects don’t have a database system and it always used other database solutions to store data for its universe.

So guess what happen ! from an administrator perspective performance tuning is somehow problematic ! since we should use other database systems we should use different technics for each database systems. And this is one of the areas that MS wins the competition because when you use Microsoft platforms there lots of joint mechanism for performance considerations.

Before the SQL Server 2012 we have SSAS with its famous aggregated cubes, because of the nature of SSAS in previous versions we couldn’t call it a semantic layer, here is a little why. A semantic layer provides translation between underlying data store and business-level language(Business semantic that business users familiar with). There was no actual translation in previous release of SSAS. Perhaps we had some difficulties over SSAS to understand for a business user. So Microsoft change its approach in SSAS 2012 from delivering a complex understandable solution to end users to a true semantic layer like what we has in Business Objects that called Universe. So from now MS BI users can use a powerful toolset like Microsoft Excel and use their existing knowledge to interact with semantic layer. What Microsoft do in backyard is to create aggregations in memory so the performance of this approach is really high ! I don’t want to deep dive into what Microsoft do in backyard in this post but it would be one of my next topics. (sounds like advertisement

I talked about aggregations so know that in BO there are no facility for aggregation tables, so you have to deal with DBAs to create aggregation tables manually and integrate them into the Universe.

One of the important aspects of a BI system is the learning curve of the solution, it was always the slogan of the Business Object that learning curve is very low ! yes for end users it is not hard to interact with Universe. BUT ! the thing that I say here is the problem of every BI platform from Microsoft to BO or Cognos that deliver Semantic layer, it is very easy for a user to get the wrong answer, because everything is behind the Universe or Semantic Model and know that tracking from report back to the base data is a Non-trivial task. So be aware about letting users create whatever they want with their own knowledge. There should always an IT professional observing the whole process. So never think about a fully out of the box solution, because you will shortly find it on Mars ! or your users may have the chance to take decisions based on wrong calculations and find their way to Mars again

Another important aspect of a BI systems is the cost of it, about the Business Objects we can definitely say that it is expensive and for sure Microsoft could be expensive ! but how can we decide ' the answer is to compare the detail parts, there are 4 main parts Database, ETL, Semantic Layer and Reporting or user interaction layer. If you choose to go over BO you have to find heads for your data warehouse, database solution and Java skills or tomcat or other J2EE platform professionals for ETL and development phase and BO specific heads for Universe Modeling, Design, Implementation, perhaps you need security administration and if you want to integrate your Active Directory with this platform it is problematic and integrating with other LDAP platforms is a nightmare ! so be aware of these costs. The point of Microsoft solution is that we can use our in house knowledge like Dot.Net and SqlServer, SharePoint, Windows Server and these knowledge are transferable to other skills. But with BO we need headcount dedicated to BO (Universe Design, Implementation, Maintenance, Security) since BO skills are not transferable to other skills, those extra heads blow the project’s budget ! Microsoft BI platform is a more manageable, more secure and less expensive solution, I see the BO as a consultant dream, as an endless font of billable hours

Conclusion

I decide to go over Microsoft BI platform but I would not suggest anyone at first place to choose Microsoft. This is really depend on the nature and scale of the project and what you did and what technologies you have used in past but a quick look gives an idea that Microsoft’s platform is looking more robust and coherent in different parts so it can be a very good and convenient choice and perhaps after the release of SQL Server 2012 and its BI Semantic layer the answer is more easier and acceptable than before.

I also would like to hear about your experience on either of these solutions.

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

How are customer service and support?

Which solution did I use previously and why did I switch?

Which deployment model are you using for this solution?