Transforming your business through business intelligence - with Microsoft modern BI & adv. analytics
Updated: Aug 9, 2021
This is part 3 of a 4 part business intelligence (Microsoft Power BI) series.
A Power BI gateway is software that you install within an on-premises network; it facilitates access to data in that network. It's like a gatekeeper that listens for connection requests, and grants them only when a users' requests meet certain criteria. This lets organizations keep databases and other data sources on their on-premises networks, yet securely use that on-premises data in Power BI reports and dashboards. A gateway can be used for a single data source or multiple data sources.
Power BI offers two gateways, each for a different scenario:
On-premises data gateway (personal mode) – allows one user to connect to sources, and can’t be shared with others. Can only be used with Power BI. This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share the data sources with others.
On-premises data gateway – allows multiple users to connect to multiple on-premises data sources. Can be used by Power BI, PowerApps, Flow, Azure Analysis Services, and Azure Logic apps, all with a single gateway installation. This gateway is well-suited to more complex scenarios with multiple people accessing multiple data sources.
Analysis Services is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools. A typical workflow includes creating a tabular or multidimensional data model project in Visual Studio, deploying the model as a database to a server instance, setting up recurring data processing, and assigning permissions to allow data access by end-users. When it's ready to go, your semantic data model can be accessed by client applications supporting Analysis Services as a data source.
Analysis Services is available in two different platforms:
Azure Analysis Services - Supports tabular models at the 1200 and higher compatibility levels. DirectQuery, partitions, row-level security, bi-directional relationships, and translations are all supported. To learn more, see Azure Analysis Services.
SQL Server Analysis Services - Supports tabular models at all compatibility levels, multidimensional models, data mining, and Power Pivot for SharePoint.
With Microsoft Azure and SQL Analysis Services and Power BI, you can turn your data processing efforts into analytics and reports that provide real-time insights into your business. Whether your data processing is cloud-based or on-premises, straightforward or complex, single-sourced or massively scaled, warehoused or real-time, Azure and Power BI have the built-in connectivity and integration to bring your business intelligence efforts to life.
Modern data exploration and visualization tools like Power BI, Excel, Reporting Services, and third-party tools are all supported, providing users with highly interactive and visually rich insights into your model data.
Power BI has a multitude of Azure connections available, and the business intelligence solutions you can create with those services are as unique as your business. You can connect as few as one Azure data source, or a handful, then shape and refine your data to build customized reports. If you have more complex data Azure Analytics Services can connect to all sorts of sources of data through the Get Data dialog. Within the same Query you can connect to your Azure SQL Database, your Azure HDInsight data source, and your Azure Blob Storage (or Azure Table Storage), then select only the subsets within each that you need, and refine it from there.
Azure Analysis Services helps you transform complex data into a single business friendly data model making it easy for business users to understand and analyze data across different data sources.
The success of any modern data-driven organization requires that information is available at the fingertips of every business user, not just IT professionals and data scientists, to guide their day-to-day decisions. Self-service BI tools have made huge strides in making data accessible to business users. However, most business users don’t have the expertise or desire to do the heavy lifting that is typically required, finding the right sources of data, importing the raw data, transforming it into the right shape, and adding business logic and metrics, before they can explore the data to derive insights. With Azure Analysis Services, a BI professional can create a semantic model over the raw data and share it with business users so that all they need to do is connect to the model from any BI tool and immediately explore the data and gain insights.
Transform complex data into one version of the truth. Combine data from multiple sources into a single, trusted BI semantic model that’s easy to understand and use. Enable self-service and data discovery for business users by simplifying the view of data and its underlying structure.
Azure Analysis Services integrates with many Azure services enabling you to build sophisticated analytics solutions. Integration with Azure Active Directory provides secure, role-based access to your critical data. Integrate with Azure Data Factory pipelines by including an activity that loads data into the model. Azure Automation and Azure Functions can be used for lightweight orchestration of models using custom code.
You can also scale out resources for fast query responses. With scale out, client queries are distributed among multiple query replicas in a query pool. Query replicas have synchronized copies of your tabular models. By spreading the query workload, response times during high query workloads can be reduced. Model processing operations can be separated from the query pool, ensuring client queries are not adversely affected by processing operations.
Finally, your data is secure. Azure Analysis Services provides security for your sensitive data at multiple levels. At the server level: Firewall, Azure authentication, server administrator roles, and Server-Side Encryption. And at the data model level, user roles, row-level, and object-level security ensure your data is safe and gets seen by only those users who are meant to see it.
Whether business users use Power BI, Excel or other 3rd party tools they don’t have to worry about transforming and cleansing the data, or identifying relationships across disparate data sources. By accessing a pre-built data model, all users can access a consistent view of the data. This means that organizations can easily extend the value of your existing data and access virtually any data wherever it is. With the in-memory cache capabilities of Azure Analysis Services, users can gain insights over billions of rows of data at the speed of thought.
Azure Analysis Services is based on SQL Server 2016 Analysis Services technology. Data can be accessed on-premises and in the cloud and business users and BI professionals can consume the data on-mobile devices, on the web and in custom apps.
This means that BI professionals who are familiar with SQL Server Analysis Services, tabular models can get started quickly and do not need to learn new tools or skills.
That includes the flexibility to develop in the familiar Visual Studio environment and take advantage of Visual Studio Application Lifecycle Management.
Get started quickly and scale with efficiency
Use Azure Resource Manager to create and deploy an Azure Analysis Services instance within seconds, and use backup restore to quickly move your existing models to Azure Analysis Services and take advantage of the scale, flexibility and management benefits of the cloud. Scale up, scale down, or pause the service and pay only for what you use.
Provide secured access anytime, from anywhere
Make sure only authorized users can access your data models, no matter where they are, with role-based security and Azure Active Directory support. With 99.9% availability, your users can access critical information when they need it.
Access data when you need it
With 99.9% availability data can be accessed when needed, and secured access to data can be provided from virtually anywhere.
Scale up and down and pause
Customers can also pause the service to only pay for what they use. With the ability to scale the service up and down –customers get the performance they need when needed.
With Azure Analysis Services customers, can rely on Microsoft’s experience running trusted enterprise cloud services!
Microsoft SQL Server Analysis Services, SSAS, is an online analytical and transactional processing (OLAP) and data mining tool in SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files.
SQL Server Analysis Services helps organizations transform complex data into business user friendly semantic models that can be consumed using their preferred data visualization tools.
Build enterprise-ready analytic solutions to deliver meaningful insights using familiar data visualization tools, such as Power BI and Excel.
Ref Links for data sources
Use Analysis Services analytics engine to enable business users gain insights instantly even if connecting to billions of rows of data. Fast response times mean your BI solution can meet the needs of your users and keep pace with your business. Additionally, connect to real-time operational data and gain insights without any data movement
Business users get insights from data easily using their preferred data visualization tool, whether it is Power BI, Excel or other major data visualization tools. IT can scale easily scale Analysis Services to meet their business users needs.
The picture above provides a visual representation of the value of a business user friendly semantic model that can be developed and published on SSAS.
The example shows a typical transactional data record in a data source on the left. To make it easy for business users to explore and dive into the data, a rich semantic model is developed Analysis Services that includes business logic and metrics and makes it easy for business users to connect quickly gain insights
Model complex data into business user friendly semantic datasets
Combine into a single model for one version of the truth
Build scalable solutions over billions of rows of data
Deliver trusted data models that business users can easily understand
In the insurance industry, the biggest risk of all might just be business as usual. Running risk models consumes and produces large volumes of data. Increasingly complex regulations and compressed reporting timelines are pushing legacy systems to their limits, revealing that the old way of working is fundamentally flawed. This is why insurance providers look to Milliman, one of the world’s largest actuarial and consulting firms, and its state-of-the-art financial and risk management platform Integrate. Powered by the Microsoft Azure cloud, Integrate gives insurance companies access to unlimited computing resources to perform compute- and data-intensive, mission-critical work. With the advanced reporting capability of Microsoft Power BI Embedded built in, Integrate provides a rich, immersive experience that allows users to simply and intuitively visualize and analyze data in one place.
For the first time, insurance providers can run large, highly complex actuarial jobs on demand, and easily process, access, and manipulate huge volumes of data across the business during reporting periods—or other times—and pay only for the compute resources they use. Milliman clients quickly gained value from the aggregation of data in Integrate’s cloud-based data model because they could access data much more easily and quickly. Integrate leverages Azure Data Factory and Azure Batch for scheduling, to scale compute resources up and down, and for extract, transform, and load (ETL) processes in data management. Integrate Data Management uses the advanced reporting and visualization capabilities of Microsoft Power BI Embedded. Working with Power BI Embedded inside Integrate, users experience a feature-rich and immersive reporting solution without ever having to leave the system. This enables Integrate to deliver a scalable and cost-effective solution that meets the ever-growing BI demands of users.
By automating and improving actuarial workflows, Integrate frees highly-skilled actuaries from tabulating data and managing reporting systems so they can use their time more strategically. Now they can analyze results, and better understand risk and how it will affect management decisions, which provides real value for their clients. One Integrate client has cut the number of manual tasks involved in the financial-modeling process from 900 to 44 and has reduced the time it takes to produce quarterly reports from four months to just three days.
Contidis is building the new Candando superstore chain in Angola, which gives people one place to buy groceries, home goods, and other items. With U.S.$400 million in investment capital, the stakes for success are high. When the organization’s SAP for Retail solution in Amazon Web Services couldn’t deliver the business intelligence (BI) managers needed, Contidis engaged Microsoft partner DevScope for a solution based on Microsoft Power BI, Azure SQL Database, and Azure Analysis Services. Today, more than 100 employees—including managers and store personnel—use the Microsoft solution to get faster, more in-depth insight into critical financial and operational data. The reports and alerts help boost customer service and uncover fraud, inventory errors, and the effects of store promotions on revenue. With Power BI, staff can instantly compare Candando’s pricing with its competitors’. And IT employees are more efficient, now that they have increased control and flexibility when it comes to reporting, data storage, and security.
Before it opened its first store, Contidis started building a business intelligence (BI) model with tools in its SAP for Retail solution. However, everyone quickly recognized that the application could not deliver the flexible and rapid insight required to drive success. It was taking IT staff too long to build reports, and business users couldn’t get all the data they needed to make informed decisions. To boost efficiency, Contidis engaged Microsoft partner DevScope to help design a BI solution because some IT employees had worked with the organization in the past.
Today, Contidis has the BI it needs to effectively run its stores, maintain a strategic business plan, and build a successful superstore chain.
Stay Up To Date:
Stay tuned for Part IV next week, and in the meantime if you have any Power BI related questions please do not hesitate to contact us.
