I am a reseller for Sage business solutions and will start to sell Microsoft BI.
Microsoft BI is primarily used for sales, financial, and production reports.
In SQL Server it is common to use an INT or BIGINT set as IDENTITY to create unique, synthetic keys. The number is a sequence and a new value is generated when we execute an insert. There are some issues with this. Quite often we need this value in our Integration Services solution to do logging and efficient loads of the data warehouse (there will be a separate tip on this). This means that sometimes we need the value before an insert and sometimes after. You can obtain the last value generated by issuing a SCOPE_IDENTITY command but this will require an extra trip to the server per row flowing through your pipeline. Obtaining the value before an insert happens is not possible in a safe way. A better option is to generate the keys yourself through a script component. Google for “ssis surrogate key” and you will find a lot of examples.
I know this is a little bit controversial. Some say Excel lacks the power of a “real” BI tool. Others say it writes inefficient queries. But hear me out. Firstly, if you look at where Microsoft is making investments in the BI stack, Excel is right up there at the top. Contrast that to what they are doing with PerformancePoint and Reporting Services and its pretty clear that Excel is the most future proof of the lot. Microsoft have added lot of BI features over the last couple of releases and continue to expand it through new add-ins such as data explorer and geoflow. Additionally, the integration with SharePoint gets tighter and tighter. The Excel web client of SharePoint 2013 is pretty on par with the fat Excel client when it comes to BI functionality. This means that you can push out the new features to users who have not yet upgraded to the newer versions of Excel. When it comes to the efficiency with which Excel queries SSAS a lot has become better. But being a general analysis tool it will never be able to optimize its queries as you would if you wrote them specifically for a report.Please note that I am saying “default” not “best”. Of course there are better, pure bred, Business Intelligence front-ends out there. Some of them even have superior integration with SSAS. But its hard to beat the cost-value ratio of Excel if you are already running a Microsoft shop. If you add in the fact that many managers and knowledge workers already do a lot of work in Excel and know the tool well the equation becomes even more attractive.
Like most IT solutions, Microsoft BI solutions are only as good as the hardware and server configurations they run on. Getting this right is very difficult and requires deep knowledge in operating systems, networks, physical hardware, security and the software that is going to run on these foundations. To make matters worse, BI solutions have workloads that often differ fundamentally from line of business applications in the way they access system resources and services. If you work with a person that knows both of these aspects you should give him or her a hug every day because they are a rare breed. Typically BI consultants know a lot about the characteristics of BI workloads but nothing about how to configure hardware and software to support these. Infrastructure consultants on the other hand know a lot about hardware and software but nothing about the specific ways BI solutions access these. Here are three examples: Integration Services is mainly memory constrained. It is very efficient at processing data as a stream as long as there is enough memory for it. The instant it runs out of memory and starts swapping to disk you will see a dramatic decrease in performance. So if you are doing heavy ETL, co-locating this with other memory hungry services on the same infrastructure is probably a bad idea. The other example is the way data is loaded and accessed in data warehouses. Unlike business systems that often do random data access (“Open the customer card for Henry James”) data warehouses are sequential. Batches of transactions are loaded into the warehouse and data is retrieved by reports / analysis services models in batches. This has a significant impact on how you should balance the hardware and configuration of your SQL Server database engine and differs fundamentally from how you handle workloads from business applications. The last example may sound extreme but is something I have encountered multiple times. When businesses outsource their infrastructure to a third party they give up some of the control and knowledge in exchange for an ability to “focus on their core business”. This is a good philosophy with real value. Unfortunately if you do not have anyone on the requesting side of this partnership that knows what to ask for when ordering infrastructure for your BI project what you get can be pretty far off from what you need. Recently a client of mine made such a request for a SQL Server based data warehouse server. The hosting partner followed their SLA protocol and supplied a high availability configuration with a mandatory full recovery model for all databases. You can imagine the exploding need for disk space for the transaction logs when loading batches of 20 million rows each night. As these examples illustrate, it is critical for a successful BI implementation to have people with infrastructure competency on your BI team that also understand how BI solutions differ from “traditional” business solutions and can apply the right infrastructure configurations.
A couple of years ago putting Microsoft BI projects under source control was a painful experience where the benefits drowned in a myriad of technical issues. This has improved a lot. Most BI artifacts now integrate well with TFS and BI teams can greatly benefit from all the functionality provided by the product such as source control, issue tracking and reporting. Especially for larger projects with multiple developers working against the same solution TFS is the way to go in order to be able to work effectively in parallel. As an added benefit you will sleep better at night knowing that you can roll back that dodgy check-in you performed a couple of hours ago. With that said there are still issues with the TFS integration. SSAS data source views are a constant worry as are server and database roles. But all of this (including workarounds) is pretty well documented online.
This is mostly related to SSAS dimensional but you should also keep it in mind when working with tabular. Attribute relationships define how attributes of a dimension relate to each other (roll up into each other). For example would products roll up into product subgroups which would again roll into product groups. This is a consequence of the denormalization process many data warehouse models go through where complex relationships are flattened out into wide dimension tables. These relationships should be definied in SSAS to boost general performance. The magic best-practice analyzer built into data tools makes sure you remember this with its blue squiggly lines. Usually it takes some trial and error before you get it right but in the end you are able to process your dimension without those duplicate attribute key errors. If you still don’t know what I am talking about look it up online such as here. So far so good. Problems start arising when these attribute relationships are not enforced in your data source, typically a data warehouse. Continuing with the example from earlier over time you might get the same product subgroup referencing different product groups (“parents”). This is not allowed and will cause a processing of the dimension to fail in SSAS (those pesky duplicate key errors). To handle this a bit more gracefully than simply leaving your cube(s) in an unprocessed state (with the angry phone calls this brings with it) you should enforce the relationship at the ETL level, in Integration Services. When loading a dimension you should reject / handle cases where these relationships are violated and notify someone that this happened. The process should make sure that the integrity of the model is maintained by assigning “violators” to a special member of the parent attribute that marks it as “suspect”. In this way your cubes can still be processed while highlighting data that needs attention.
I am a reseller for Sage business solutions and will start to sell Microsoft BI.
Microsoft BI is primarily used for sales, financial, and production reports.
I believe that it's a good solution.
It is not an expensive solution. The price is very important in the solution selection process. In my opinion, Microsoft BI is a low-cost solution, and it could be an interesting solution for a tourism company.
I have been using Microsoft BI for a few months.
As a cloud solution, we are always working with the latest version.
In our company, we have five users.
The price is an important consideration when upgrading or adding new users. In my opinion, it would be better for the price to be degressive by the user. For example, if you have five users, you simply multiply five by the unit price to find the total cost of the solution.
I believe the price should be degressive, and we should have a lower, or better unit price if we have more users.
I hadn't really needed Microsoft's technical support until now. We have not had any issues to resolve.
The installation is not a difficult thing, it is very simple.
We only need one IT manager, to deploy this solution.
Licensing fees are paid on a yearly basis.
I would recommend this solution to others who are considering this solution, which is the reason that I will be selling it.
I would rate Microsoft BI an eight out of ten.
The most valuable features of Microsoft BI are the variety of possibilities to connect to various data sources. The visualizations are easily done, have useful rollover functions, and there are continuous updates being made to the system. You can benefit from the various improvements.
I'm missing collaborations functionality to operate or to work connected with multiple people on a data source or on virtualization. There should be more collaborations functions, such as in Confluence. We haven't explored the solution sufficiently in this area, but at this time it doesn't look sufficient.
I would want one platform, which can be used for top management meetings where you see and comment on the data. That would be a perfect combination. Everybody has access, sees the status, the data, and the comments, and that will make life easier for us.
I have been using Microsoft BI for approximately three years.
Microsoft BI is stable.
I have found Microsoft BI to be scalable.
The technical support from Microsoft has been good.
The setup is good. Everybody can test and try the solution, it's not rocket science. There is a lot of training and courses available. We decided to have a separate workforce for that purpose which is doing nothing else than Microsoft BI every day in India. It has been very effective.
I rate Microsoft BI an eight out of ten.
We were using Microsoft BI but it could not do what we wanted it to do. Then we moved to an open-source platform, which was Apache Tika, Kafka, and third solution. We ended up moving back to Power BI only to display the information because displaying information with Power BI is better than open-source software. We are not using all aspects of the solution at the moment.
In the process of using data there is the Extract, Transform and Load (ETL). For extracting we are using another software, for transforming we are using our own coding in C#, and then we use Microsoft BI for loading the information. Microsoft BI displays information very well.
The solution could improve the extraction and transformation of data. For example, you transform the data and then send it to Microsoft BI without having to use your own API. We are only providing the API to Power BI, and then Power BI is doing the job.
In an upcoming release, Microsoft BI should increase the functionality of the solution.
I have been using Microsoft BI for approximately six years.
We were satisfied with the stability of Microsoft BI.
We have 30 users using the solution in my organization.
We are in contact with the support of Microsoft but I have not been in contact directly.
I have used other solutions, such as Apache Tika and Kafka.
The initial setup was straightforward.
The solution only needs one person to do the maintenance.
There are a few options available for purchasing a license. Typically the number of users you have will determine the price of the license. The more users you have the more you will pay.
The solution has had a lot of changes over the years and it is very good. However, there is more work to be done on extract and transform functions for it to be done properly.
I rate Microsoft BI an eight out of ten.
I like data modeling. You can connect with your database, which is quite useful for me. It is a good tool if you have a large amount of data and you want to gather different data and interconnect it.
The Power Query functionality is quite an interesting feature. If you have a query in Excel, you can also copy your query and run it in Power BI.
Its dashboard is also very nice and not complicated. You don't need to be a developer to be able to use it. I am not an IT guy, and it is quite easy to use for somebody who is not an IT person.
It is not the right tool to do deeper analysis or predictions. When you have some data and you want to do some deep analysis, there is no feature to help you with this.
I have been using this solution for one year.
I would rate Microsoft BI an eight out of ten.
We primarily sell Power BI licenses.
Mostly, we focus on the retail industry. Usually, we are using the Power BI reports for dashboards. We are also providing our customers with some data warehouse reporting. Basically, we do long-term categorization of stock and inventory numbers and sales figures so that they can compare the sales and stocks to the inventory numbers.
We tend to deal with physical inventory reports. We tend not to deal with the solution for financial operations.
The solution has different licensing tiers.
The product can scale if you need it to.
Technical support is quite good.
With the cloud deployment, there's no setup required.
The product works well for small or mid-level organizations.
The solution's stability could be improved. In the retail industry, due to issues with information updating and data uploading. Clients may end up pricing items wrong as they could accidentally base their choices on old or wrong data.
The licensing needs improvement. There needs to be a middle option between Pro and Premium versions. It could also be less expensive.
While we have experience with Microsoft tools for the past ten years, I personally have had experience with Microsoft BI for the past two or so years.
The solution is not really stable. I've faced a lot of limitations especially in terms of some data flows updates. We have faced some exceptions. Right now, we have created a case for Microsoft in order to solve some problems we've been having as, right now, there's some sort of system fault.
While the platform is a little more stable, the big problem is the latency issues around customer updates.
In a recent project, in Sweden, we faced an exception in Power BI from the update path. Whenever we refresh the animations and the fact tables, we faced something that said we couldn't upload the data. After 20 minutes we tried to refresh again. We didn't change anything with our reports or platform or anything related to infrastructure or schema. We didn't touch anything. An yet, the data is updated successfully. That leads us to ask the question: what was the problem 20 minutes ago? We still don't know the answer.
That is why we need some stability for the update service. We are deciding some business decisions based on this data. If this data is not updated, we may decide to take the wrong path.
This is very important in the retail industry. For example in the grocery field, sometimes with vegetables or fruits or other products, customers need to decide to change the prices within lock days. They need to decide, based on demand, if they should increase or lower prices. They can't make the correct decisions if the numbers don't update.
The scalability potential actually depends on your licenses. Microsoft provides three licenses. One of them is absolutely free. Another is called Pro. Yet another is per user or Premium. There is a huge difference between the Pro license and the Premium license. There is no need to scale the Pro license or other license models.
When you put the on-premise reports, you can scale out or scale up. It depends on your existing resources. However, in the services as a software (SaaS) version, it actually comes with Microsoft's units or Microsoft resources.
We have an ASFP agreement with Microsoft. As a Gold Partner, Microsoft is pretty good and responding automatically and helping us resolve issues.
If a partner does not have an ASFP agreement, they tend to have to go to forums or try to Google answers to their problems.
We are aware that sometimes when we make changes to BI, the solutions might be complex. There might be SQL involved, which needs to be taken into account, for example. We may have to look into other resources and other tools to see if there are interactions that are the issue. However, Microsoft is quite helpful as we work through issues. We're quite satisfied with their level of support.
We also work with and sell MicroStrategy in Turkey. 95% of the MicroStrategy projects have been completed with our company. It is a direct competitor with Power BI. My company commonly does consulting for some small and medium markets and for that we recommend Power BI. Whenever we talk about enterprise-level solutions, the company we will use is MicroStrategy.
There isn't much of an installation process, as the solution is on the cloud.
There is a free license, however, it is limited. The Premium license and the Pro license functionalities are very different. Whenever we talk with our customers, they ask "Should we chose the Premium?" We say that "No, you don't need to, as it's too expensive. It is much more expensive than the Pro license." However, the Pro license functionality is not enough for some customers. There is no middle between Pro and the Premium. We need something in between.
The Pro license is maybe suitable for SMBs, small and medium-sized businesses. The premium version is ideal for companies that need to scale up and out.
There also may be some additional costs that can drive up the price.
I prefer this product and I suggest Power BI to all customers, however, we know that if Power BI is a new idea, we make sure to show company-specific data or analytics for analyzing the data and how this solution can analyze everything quickly. That said, it's not for everybody. If all companies tried to put all their reporting expectations in the Power BI, it would not fit their expectations.
It's not a standalone solution. You need other items in your toolbelt. You need, for example, something that can handle raw data, you need warehousing, et cetera.
In general, I would rate the solution eight out of ten.
We primarily use Power BI for data science solutions.
This is a self-service BI tool, so it doesn't require a huge amount of technical training, knowledge, or a background in BI in order to induct new people into it. This is true even for business people who want to do a little bit of self-service. The whole thing has become quite simplified, as compared to what it was.
There are many good features in this product.
The most valuable features are a combination of the DAX and the inquiry capability, plus the visual gallery that we have. I think that these three things, or the combination of them, is the best that we have found.
I would like to have the capability to schedule exports in different versions. For example, if I want to schedule a subscription and attach an Excel file, we don't have this capability right now. A lot of people work on Excel, so being able to export an Excel file as part of a subscription would be an improvement.
We have been working with Power BI for more than five years.
Overall, it is pretty stable. The cloud version is definitely very stable, and I have found very few flaws or areas for improvement. We have been using it for more than five years and we have every intention to continue with it.
It has been pretty nice for us, in terms of scalability. There hasn't really been a problem, as such. We have approximately 300 users.
Technical support from Microsoft gets a big thumbs up from me. They have blogs, a support page, and the community is very active. Most of the answers we need, we find them there.
With this amount of information available, we don't need to use technical support extensively. When we do post something on the blog or in the community, the response that we get is pretty prompt.
The last time we utilized technical support was when we were investigating creating a subscription with an Excel file attached to it. We found out that it isn't yet possible with the current version.
The initial setup is straightforward, to say that least. It took just a few days to deploy.
The pricing is very reasonable compared to any other tool.
My advice for anybody who is considering this tool is that if you want to move to a self-service BI, this is probably the best tool available right now. The self-service is pretty good in terms of os user acceptance, usage, and simplicity. It is something that everybody wants and Power BI does really well in that area.
The biggest lesson that I have learned from using Power BI is that if the data is not according to what you want, that really isn't a problem. It has been a problem in the past, especially where the data is not in the form or the shape that you need, but with Power BI, that problem can be solved very easily.
I would rate this solution an eight out of ten.
The primary use case was mainly to create dashboards and present them for reporting purposes. We use Microsoft Power BI to create dashboards, provide insights, and use its various analytical and insight functions to send to either the departmental manager or the general manager of marketing and sales or retail ops.
In short, we had to create sales reports. The sales reports needed to be emailed to the different sales managers all across New Zealand. The Excel file data had to be filtered manually, whereas, in Power BI, you can set restrictions. For example, if you have North, South, East and West branches, you can set corresponding restrictions, while allowing the national sales manager to view the entire data without having to do a lot of filtering, because of the restrictions involved.
It is time-saving for the person who creates the report and also a lot of that is automated, whereas with Excel you've got to keep working on it and keep filtering it all the time.
Our most valuable features are the dashboard and the reporting feature because, before that, our organization used to only use Excel. There was a lot of manual work involved with Excel, whereas once we moved on to Microsoft Power BI, it was a cleaner dashboard and it was faster too. The end user also had an opportunity to alter the reporting as per requirements with Power BI.
I haven't experienced any issues with it right now, so I can't really advise on that part.
I think because I'm moving more into RPA, I'd definitely like to see integration across the RPA, Power BI, or Tableau platforms because that integration could then make automation and mundane reporting much faster.
You'll always require some more complex reports that the analyst could work on, but the very standard sales reports could then be automated very quickly using Power BI and RPA together.
Stability is pretty good. I've been using other services from Microsoft and the support also has been really good so it's a very stable platform and the support services are excellent.
I've been using it for a year and it's only across my single department so I've not yet had to scale it to a very large level.
Across my department, there are two users. Across other departments at my company, there are many more. Most users are commercial or insights analysts.
Across our organization, Power BI is not extensively used, although I'm not privy to every department and their plans. I think it's being used moderately, but could be used more.
In terms of Microsoft Power BI, if there was ever a query, you can find support online. There's a lot of help available but what I generally meant was I have been using Microsoft Office products through the Office 365 subscription business and the support of Microsoft Office, in general, has always been really good. They usually come back on the very same day and try and solve the problem. This is not something I've experienced with other platforms in the past.
We hadn't previously used another solution. We were just using Excel and decided to go to a much better platform.
The setup was straightforward for me because as part of an organization, I didn't have to set it up myself. It was set up via the organization, through the IT department, so it was pretty straightforward.
I adopted it in my team, but it was already being implemented across other parts of the company. I just brought it into my department, so I wouldn't be able to tell the time frame for the actual implementation strategy. For my team alone, it probably took me around two months or so.
The other departments in my company were using Power BI, so it would not make sense to get another solution when they already had Power BI in other parts of the organization.
Microsoft Power BI is definitely a good product and Microsoft has a good integration of a good suite of products across the range. I think if you take UI parts recent release, for example, I'm not sure that does it with Power BI but it definitely integrates it across the Microsoft Office activities, so I think it's only a matter of time before a majority of the Microsoft Office products start integrating with UI parts. That would take analysis, reporting, and automation to the next level and we could free up time for the general reporter.
I would rate this solution as seven out of ten because it's easy to use and there is a lot of support online. I think Microsoft Office itself has a lot of support being provided. In addition, I like its features and it's growing much faster than some of the other analytical tools out there, although it started off later.
Hi Peter !
Nice article, now we discuss from point 11 to 15 in detail;
#11: I do agree with you partially on this, because I don't understand the need for creating a separate surrogate key for SSIS. My point is using the keys from Production tables; personally I use Change Table method to perform incremental loads. If a separate key is required in your Data warehouse Model, you can create in using a combination or reading the value from source table or by loading a value into SSIS variable and then assigning this to your table.
#12: I prefer to use Excel as a tool where i can perform quick data verification or number reconciliation by connecting to my cube. I know Microsoft has been investing lot in Excel through Power Pivot and all. But what about the future of "Power BI" which we heard a new tool which will have the capabilities to become the number one BI tool for reporting. Personally I think excel can't be used as enterprise reporting tool.
#13: A rare to have thing. Another thing to add is really hard to find BI Consultant which has experiences in not only Cube optimization, but also in Report and Database optimization as well. If you have one of these, I called them as a "Real Asset", because they not only help you in OLAP, they will help you in OLTP, in your SSIS and in your reporting as well. I must suggest including at least one of these guys in a BI project, this will actually save your time and money.
#14: I have been using TFS for keeping my SSRS reports to source control, and it’s been nice that it doesn't act up badly. But i do have a reservation about keeping my SSIS to TFS, because it happens to me multiple times where it got corrupted somehow, luckily I am not only relying on TFS so I have the source back with me. Always use a backup strategy if your source control might fail how you can do the recovering. So be prepared for this because it might be happening anytime soon.
#15: Always good to define hierarchies and attribute relationships, whenever possible define hierarchies. Remember once you define the Hierarchy, hide the attribute so that it won't be duplicated in reporting tool like if you are using Performance Point, end user might see same attribute both inside hierarchy and in the dimension as well. So do set the visibility of attribute to hidden.
Designing a BI Solution is an interesting job; in each development you will learn new things. Always plan your development, choose the right tools to be used for your final solution, if you are unsure about something better discuss it with some other Consultants to pick the right product for your solution.
Regards,
Hasham Niaz