A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset? Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.
(Note: The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)
Nope! Report Models, however, are deprecated. As of SQL Server 2012, you can no longer create or update Report Models (SMDL files). You can continue to use Report Models currently, but it is recommended to move away from them as time allows. Report Builder itself is at version 3.0 and is a mature product.
The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder. Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).
So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox. I hope it’s alive and well for a long time.
Got any thoughts on this subject? Leave a comment … I’d love to hear your thoughts.
Disclosure: The company I work for is a Microsoft Partner
After working on a couple of serious tabular model projects, I’ve been talking to other BI practitioners lately about their thoughts regarding tabular model technology and I’m finding that there is little consensus among all these industry experts about which option to recommend for new BI projects. At SolidQ, I work with some of the most experienced Business Intelligence experts in the industry. At conferences and in discussion groups, there has been a lot of discussion about the choice between Analysis Services multidimensional models and the newer tabular semantic model technology introduced in SQL Server 2012. There are pros and cons for either side but I think there are some important factors to consider. Among some of the most experienced Analysis Services consultants, I have heard that some are primarily using and recommending tabular for most new projects and others are arguing that tabular offers little significant value over the tried-and-true multidimensional cubes and MDX script solutions.
As is typical for a newer product, the usability experience for the model designer isn’t perfect. In some ways, it’s different and just may take some getting used to, but in other ways there is clearly room for improvement. The question now is; do the advantages of tabular vs multidimensional outweigh the somewhat rocky design experience? I’m not taking sides in this comparison but merely offering an objective analysis of the state of tabular model option in SQL Server 2012 Analysis Services, Service Pack 1. I expect this product to go through changes so for future reference, this information is for the released product at the time of this posting in June of 2013.
The core VertiPaq (or xVelocity) query and aggregation engine is stable & reliable. Originally developed about five years ago and released with PowerPivot for Excel and SharePoint in SQL Server 2008R2 over three years ago, this technology has proven to be ready for serious use.
Under most conditions, for analytic reporting, data scanned and aggregated from an in-memory data structure performs faster than other conventional options; including relational and multidimensional storage.
Tabular can be less complex than multidimensional, OLAP SSAS. The core design and usage concepts are easier for both those who design models and for those use use them for analysis and reporting.
Tabular models can be easier & faster to implement because the model structure is simpler and there may be fewer steps in the design process.
DAX, the core calculation expression language for tabular models, is fairly easy to learn. Fundamental DAX expression concepts can be easier to understand than equivalent MDX commands used in multidimensional modeling and calculations.
Comparing features, tabular can be hard to justify when compared to multidimensional. Traditional SSAS cubes still have more capabilities than tabular models and for someone who already has OLAP skills and background, tabular design is a new learning curve.
PowerPivot is a version 2 product. As such, it’s simple and fairly reliable in the majority of design scenarios. In some, mostly rare, cases, error handling and debugging capabilities aren’t as mature and robust as more tenured technologies like SSAS multidimensional or relational.
Tabular SSAS is a version 1 product. As such, it has a list of well-known design annoyances and features that could (and should) improve in the next product go-round.
The recommended design patterns & best practices, both from Microsoft development and support teams and from members of the expert practitioner community, are still emerging.
One model.bim file = one developer working at a time. A departure from the long-time SSAS project development environment where every object (such as data sources, dimensions and cubes) were defined in isolated files managed by the project; SSAS tabular manages all of the database objects in a single model definition file. The model designer treats the model is more of a black box than a solution comprised of independent objects. However, the fact is that most of the same objects we work with in multidimensional/cube projects are all defined as XML elements in this file. It can be opened and properties manipulated,and there are a growing number of third-party tools to provide enhancements. Regardless, it is one big project file tat would need to be checked-out in source code management as a single project asset.
The tabular SSAS support community is thriving but still small. A core group of trusted professionals from all over the world are the loudest voices right now. They promote tabular solutions and provide active and collective support for one another.
The DAX expression editor in PowerPivot & the Visual Studio-based SSDT designer is quirky. You have to know is strengths and weaknesses and be willing to use it in that context. It attempts to assists with code completion but in the end, it doubles-up brackets and duplicates, rather then replaces old code, that it suggests. No doubt that the experience will get better as enhancements are developed and released but we must live with a product today that is useful and reliable a lot of the time but, it leaves plenty of opportunity for improvements.
The entire tabular model must fit in memory. There’s no caching, swapping or distributed processing option for large models. This means that very large models need very large hardware and there is currently no scale-out option to distribute work loads or fail-over. Got a lot of data? Get a lot of memory.
After you get into serious data with 30, 40 or 50 tables and some complexity to your model, the version 1.0 SSDT tabular model designer can be cumbersome and error-prone. I’ve seen it stop responding and throw errors when there were no errors. I will say that it’s helpful and reliable most of the time but on occasion, when it falls down, I often save and close my work; shut down Visual Studio all together and the fire it back up.
My biggest peeve about the SSDT model designer is that all design work is performed while connected to the workspace database. This means that for every table and column rename,and for every single property setting change, this information is written to the model workspace database on the local SSAS tabular server, and after the changes have been committed, control is given back to the model designer.
Some key SSAS features are not currently supported. These include things like parent-child hierarchies, many-to-many relationships, cube actions and display folders. Actually, this is not entirely true; actions and display folders can be added using after-market tools (like BIDS Helper, available in the CodePlex library) and by hand-writing the XMLA script, but they are not currently supported through the SSDT model designer. There is simply a category of features that didn’t find their way into this first version of the product. There are work-arounds and methods to enable these capabilities but they’re not supported, or at least not to the the same degree as they are in multidimensional SSAS.
There is no doubt that in-memory, tabular model technology is the promise of the future. It just makes sense. Several vendors have come to the same conclusion and are developing products following this paradigm. Oracle just made a big announcement about adding in-memory column store to their future 12C product.
Data residing and processed in memory is faster than data residing in disk. This is what VertiPaq does; whether implemented as PowerPivot, an SSAS tabular model or as a SQL Server column store, it works efficiently and elegantly without the complexities and overhead of indexes, partitions, file groups and other techniques typically used to optimize on-disk data stores.
Even though tabular is fairly new, many useful & valuable features are supported today and work well.
PowerPivot models upgrade seamlessly to tabular models. This provides a path for business users and IT professionals to author models in familiar tools (Excel or Visual Studio) and then promote them to a server hosted environment.
Tabular models are managed and stored by SQL Server Analysis Services! Although some components of the tabular engine and the designer are new and still have wrinkles to be ironed-out, the core product is based on the solid and time-tested foundation of SSAS. This means that many of the features not implemented now will be available in future builds.
Client applications that support SSAS multidimensional will also support tabular. In fact, any application built to work with SSAS cubes will natively work with PowerPivot and tabular as if it were a cube. This is because SSAS tabular uses the same data provider that understands both MDX & DAX queries.
Disclosure: The company I work for is a Microsoft Gold Partner
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.
The product can be used to get all business insights from connected data, for example, whatever is on our system, which we are using for our business solutions.
All the data can be connected and it can give you actionable insights.
There are also some AI capabilities.
The solution is very stable.
The scalability overall has been quite good.
We found the initial installation to be very straightforward.
The admin view could be improved by making it more simplified to manage a users' activity and other things. That would be quite helpful.
It could be more user-friendly in general.
I've been using the solution for three years at this point. It's been a while.
The stability is great. There aren't issues around bugs or glitches. It doesn't freeze. I haven't experienced a crash. It's reliable.
The scalability has been good. If a company needs to expand it, it can do so.
Right now, we have 200 people using the solution.
I've dealt with technical support in the past. It's okay. I don't have any complaints at all.
Before we had this solution, we used some internal software development using normal SQLs and JavaScripts. We also take advantage of our SQL Server.
The initial setup is very straightforward and simple. It's not a complex process. A company shouldn't face any issues handling it.
We only needed two people to handle the deployment and maintenance aspects of the product. It's one manager and one developer.
We deal with both monthly and yearly licenses, which are coming due soon.
As it is cloud-based, this solution is always providing us with the latest version to use.
I'd rate the solution at an eight out of ten. We're mostly quite happy with its capabilities.
I would recommend the solution to other users and organizations.
We are using Microsoft BI for various reports, such as overall cost management, credit overview, and sales.
The solution is very intuitive, you do not need to have too much programming knowledge to use it. Advanced Excel users can use it very easily.
The solution could improve by providing more free visuals. A lot of them are very nice but you have to purchase them.
I have been using Microsoft BI for approximately four years.
The solution has been stable. Everything has been running smoothly.
Microsoft BI is scalable.
The support has been good.
We previously used Cognos.
We used a consultant for the implementation of the solution.
The solution is affordable.
I rate Microsoft BI a nine out of ten.
There were two systems that the client was looking at creating some BI visualizations for. There was the system called ICE, which is a system that takes in automated calls, for service calls. They go into voicemail, however, if there's not an agent to take the call, it'll track statistics like how long a client was on hold, if an agent actually took the call, if the person hung up, et cetera. There are all of these different statuses and it would take all of those statistics. The product was used primarily to take that data in that case.
The other system was an ITSM system, which was the ticketing system. It would
it was my first time using Power BI, I can say that it was fairly easy to learn, especially if you already know BI-type tools. But the one thing that I noticed specifically was the graphical features, and some of the analytical features, I think they were stronger on the Tableau side. But the data preparation features, I much preferred the Power BI with the Power Query type features, at least for the datasets that we were working with. They were helpful in preparing the data.
combine those two data sets and reduce a merged analytical set of reports to just show when the call volumes were and what the performance rate was and the nature of the calls and things like that.
As it was my first time using Power BI, I can say that it was fairly easy to learn, especially if you already know BI-type tools.
The data preparation features were great. I much preferred the Power BI with the Power Query type features - at least for the datasets that we were working with. They were helpful in preparing the data.
The one thing that I noticed specifically was the graphical features, and some of the analytical features. They were stronger on the Tableau side.
There are two areas where the solution can be enhanced. One is natural language, where you can have third parties, however, it would be nice to have it built-in within Power BI, where a graph could be automatically explained in whatever language so that somebody can just kind of read through and have the graphic as an extra for exploring. That's something that I was looking at.
The other is the explained data, which is more of a lineage-type feature. My understanding is Tableau has that feature and Power BI doesn't. I was looking at a graph of comparisons and I noticed Tableau had this explained data feature, which gives, some lineage where data has come from, and Power BI doesn't have that. Or, if it does, it's not as well developed as Tableau.
I used the solution a few months ago. I'm semi-retired, however, I did have a two-month contract and did some work with it. On the project, I did work a lot with Tableau, however, I also used Power BI, and would like to continue to use it more.
It was a short project on the Power BI side. I didn't really identify any glitches. Certainly, over time things might've come out, however, I enjoy doing experimenting and playing around with things. The one definite thing that I noticed, as I do art with Tableau, is some really neat art stuff, so I tried to replicate that with Power BI and the quality just wasn't there.
With ITSM, there's a lot of data that they collect on that side. That could be an issue, scalability. It wasn't moved to production yet. I basically produced a report as there were some issues with the ICE data, the voicemail system, and data collection. They had some issues that they had to solve. However, they were collecting data on a daily basis. You get thousands and thousands of calls on a daily basis. It depends on how they want to store that data historically. There may be some performance issues and scalability issues, however, for me, I can't really address that, as I didn't see that side. There's always a potential for trouble.
It was a really short project for me. I was really there just to analyze those two data sources and produce a report for them, with some examples, using Power BI. I was able to do that without getting tech support involved.
I've used other solutions, such as Tableau and Power Query. Tableau from a graphical point of view is much better than Power BI, however, from a data manipulation standpoint and things like that, I prefer Power BI and Power Query to prep the data.
Tableau got bought out by Salesforce, which is a cloud-based company, so they're all going to push everything to the cloud. I'm sure they'll incorporate all these new features.
I downloaded the solution from their website.
The server side was already set up, therefore, I don't know much about the initial setup process. As a contractor, I came in just on the development side, which was just downloading the Power BI tool as a client. It was fairly easy. That's the nice thing is it's easy to set up. It is similar to Tableau, in that Tableau is fairly easy to set up. It is all built towards this whole idea of self-service.
I'm just a customer.
As I downloaded the solution right off the website, I assume it was the latest version. I don't know the version number off-hand.
I would do the development on-pre
There were two systems that they were looking at creating some BI visualizations for. So there was the system called ICE, which is a system that takes in automated calls, for service calls. And so goes into voicemail, but if there's not an agent to take the call, it'll track statistics like how long you were on hold, if an agent actually took your call, if you hung up. All of these different statuses would take all of those statistics. And so it was to take that data.
And the other system was the ITSM system, which is the ticketing system. And kind of combine those two data sets and reduce a merged analytical set of reports to just show when were the call volumes were, and what was the performance rate, and the nature of the calls and things like that.
It's easy to set up, however, regarding scalability and high-end analytical features, Tableau is still better on that. It's always important to keep up, as technology's changing all the time. They're always adding new features. There are no negatives in exploring these tools. In the end, you just want some visualizations that help you make decisions. Both Tableau and Power BI tools are great for that. However, if you want advanced stuff, then it's just about experimenting and seeing what will help you solve your problems.
I'd rate the solution at an eight out of ten.
The dashboard features are really valuable, you can connect to multiple data points. Overall, the solution is easy to use.
The solution could be more customizable and you do not have enough freedom on the code.
I have used the solution for one year.
The install was straightforward and the advanced dashboards operate on well-branded tablets.
The premium licensing is approximately $5,000 per month which is pretty expensive.
If you are planning on implementing Microsoft BI, having experience with other Microsoft products will make using this solution easy and straightforward.
I rate Microsoft BI a seven out of ten.
We use Microsoft BI for all of our data analysis processes.
In comparison to Excel, this is a much more powerful tool.
It is a very good software solution. It makes life very easy for us. It is easy to grasp, and it is useful. The set-up visualization format was very helpful.
I've been working with connecting with the right platforms and we need to use other connectors to link with the data. This would be a nice feature for BI to add in the future. For example, we needed another connector in order to make our Wordpress functional. More options for the connectors are really important for us.
At times it is not stable, and we need to use other connectors to link with the data.
We have not had a need for technical support for this solution.
When evaluating a new product, I would first ask my colleagues or other friends that I know based on what their opinions are about the product. Also, I would be concerned with the level of crashes. The less they crash, the less problems and the easier our life will be. Furthermore, the level of support that the product provides is important, as well.
I used Excel and Rapidminer in the past. But, I really like the BI product.
In my experience one area that can get left behind is the distribution of reports. I've yet to find an instance where a company doesn't need to share information with an external party. Having an environment where distribution mechanisms are managed in one place only reduces risk. Here again we find that the MS stack can place more restrictions on end users.