{"id":86736,"date":"2020-03-29T22:22:58","date_gmt":"2020-03-29T22:22:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86736"},"modified":"2020-03-29T22:22:58","modified_gmt":"2020-03-29T22:22:58","slug":"microsoft-data-platform-2020","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-data-platform-2020\/","title":{"rendered":"Microsoft Data Platform 2020"},"content":{"rendered":"

From last November 4 to 8 I have been at\u00a0PASS Summit<\/strong> in Seattle, attending excellent technical sessions and having the honour to deliver a\u00a0 lightning session, which is a small 10 minutes presentation.<\/p>\n

Why should you read this?
\n<\/strong><\/h3>\n

Being one of the biggest data events in the world, PASS Summit<\/strong> usually exposes the present and future technologies that will drive the following year or more, pointing the direction for every data-related professional.<\/p>\n

This summary, full of links and details about these technologies, can guide you during the exploration and learning of these new technologies.<\/p>\n

Azure SQL Database Serverless<\/strong><\/h3>\n

In the beginning, we had only Azure SQL Databases<\/strong> or custom VM’s, Platform as a Service (PaaS<\/strong>) or Infrastructure as a Service (IaaS<\/strong>) two extreme opposites in relation to price and features.<\/p>\n

Want to know more? Take a look on this link: <\/em>https:\/\/azure.microsoft.com\/en-us\/overview\/what-is-paas\/<\/em><\/a><\/p>\n

The first change happened in pricing: The creation of Elastic Pools<\/strong>. By using an Elastic Pool,<\/strong> we could aggregate many databases in a single pool of resources, expecting that when one database is consuming too many resources, the others would not be consuming that much.<\/p>\n

More about <\/em>Elastic Pool<\/em><\/strong>: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-elastic-pool<\/em><\/a><\/p>\n

However, Elastic Pool<\/strong> is better for pools with 5 or more databases, with fewer databases it becomes too expensive, doesn’t worth it.<\/p>\n

Microsoft decided we were missing something and create the Azure SQL Server Managed Instances<\/strong>, a halfway between the existing solutions. Azure SQL Server Managed Instances<\/strong> is intended to be cheaper than a custom VM and have more features than an Azure SQL Database<\/strong>, allowing us to access features of an entire instance.<\/p>\n

On my first experiences with Managed Instances<\/strong>, they were difficult to provision, taking hours to provision. During the PASS Summit<\/strong>, there was one demonstration provisioning Managed Instances in seconds using Azure ARC<\/strong>. I will go on details later on this blog.<\/p>\n

Learn more about <\/em>Azure SQL Server Managed Instances<\/em><\/strong> on this link: <\/em>https:\/\/blogs.msdn.microsoft.com\/sqlserverstorageengine\/2018\/03\/07\/what-is-azure-sql-database-managed-instance-2\/<\/em><\/a><\/p>\n

Now we have one more option: Azure SQL Database Serverless<\/strong>. It’s not a product, but a pricing tier of Azure SQL Databases<\/strong>, although it offers slightly different features.\u00a0<\/p>\n

\"A<\/p>\n

The best way to understand this new pricing tier is comparing it with the database option “auto close\u201d: When no connection is active on the database, the database shuts down and stop charging our azure account. When the following connection request arrives, the database will start again, making the connection slower than the rest, but only the connection, all further requests will be regular. It’s cheaper, of course, since we are only charged by the time the database is in use, this makes this tier cheaper than the usual Azure SQL Database<\/strong>, but the “auto-close” behaviour maybe not good for some environments.<\/p>\n

Serverless<\/strong> databases have an additional configuration called Auto-Close Delay<\/strong>. You can define after how long time of inactivity the database should shut down. The minimum amount of time is 1 hour. This configuration can prevent disruptions during work hours. If we find the correct delay configuration the database may shut down only out of working hours.<\/p>\n

\"auto-close\"<\/p>\n

In the future, a new feature to allow us to automate the start and shutdown may appear, providing us with better control and more savings.<\/p>\n

More about <\/em>Azure SQL Database Serverless<\/em><\/strong>: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-serverless<\/em><\/a><\/p>\n

SQL Database Edge<\/strong><\/p>\n

IOT<\/strong> devices, especially in industries, send constant information to servers in order to the server monitor the device and discover if there is something wrong. More than just monitor after the device failure, the servers can run machine learning models over the data to identify potential future failures according to the device’s history.<\/p>\n

The weakness of this architecture is the need of the devices to send the data to the server in order to be analysed and only then the server will be able to take an action, maybe changing some device configuration, or sending some alert.<\/p>\n

SQL Database Edge<\/strong> is intended exactly to solve this weakness. It’s a SQL Server Edition<\/strong> which can be installed inside the IOT<\/strong> device. Instead of sending the data to the server, the IoT device can save the data in SQL Database Edge<\/strong> and execute the machine learning models using SQL Server<\/strong>, which has the SQL Server Machine Learning Service<\/strong>. In this way, the own device can identify possible future failures and take the possible actions to work around it or send an alert.<\/p>\n

\"A<\/p>\n

Empowering the device, we distribute the load from the server to hundreds or thousands of devices, allowing for faster and more reliable problem identification and workaround using machine learning models.<\/p>\n

One more interesting detail is the ability to distribute Azure SQL Database Edge<\/strong> to the devices using IOT Hub<\/strong>.<\/p>\n

You can read more about <\/em>Azure SQL Database Edge<\/em><\/strong> here: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/sql-database-edge\/<\/em><\/a><\/p>\n

Learn more about SQL Server Machine Learning Service on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/sql\/advanced-analytics\/what-is-sql-server-machine-learning?view=sql-server-ver15<\/em><\/a><\/p>\n

More about <\/em>IOT Hub<\/em><\/strong> on this link: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/iot-hub\/<\/em><\/a><\/p>\n

Accelerate Data Recovery<\/strong><\/h3>\n

This is a new SQL Server 2019<\/strong> feature which is being much highlighted, not only in PASS Summit<\/strong>.<\/p>\n

Long-running operations are something very common to happen. However, sometimes, after 1 or 2 hours of a running operation, some junior DBA may decide it was too much and try to kill the process and rollback. Here the problem begins.<\/p>\n

In order to stop the running transaction, all the activities need to be rolled back and this can take almost or even the same time the transaction has taken until the kill was requested. There is no solution, the DBA can only wait for the rollback.<\/p>\n

The Accelerated Data Recovery<\/strong> (ADR) arrived with Hyperscale<\/strong> cloud database, announced in\u00a0PASS Summit\u00a02018<\/strong>. This year, ADR<\/strong> was included in SQL Server 2019<\/strong>. It allows for a fast recovery, without all the rollback waiting time that usually happens.<\/p>\n

ADR<\/strong> uses row versioning techniques, but with a different internal structure than the one used by snapshot isolation level<\/strong>.<\/p>\n

\u00a0<\/p>\n

In order to know more about <\/em>ADR<\/em><\/strong>, check these links:\u00a0<\/em><\/p>\n

https:\/\/www.linkedin.com\/pulse\/sql-server-2019-how-saved-world-from-long-recovery-bob-ward\/<\/em><\/a><\/p>\n

https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/how-does-accelerated-database-recovery-work\/<\/em><\/a><\/p>\n

Azure ARC<\/strong><\/h3>\n

Microsoft knows many companies use more than a single cloud provider. If Microsoft relies only on their cloud services, the war among cloud providers would be difficult, Microsoft can win some, lose others. So, why not a complete change in the battlefield?<\/p>\n

Azure ARC<\/strong> allows the deployment and management of Microsoft cloud services to many different clouds: Azure<\/strong>, Google<\/strong>, AWS<\/strong>, all the deployment and management made from a single tool – Azure ARC<\/strong>. This completely changes the battlefield, even if one company has some reason to use a different cloud provider, they can still use Azure ARC<\/strong> to manage the entire environment and even deploy Microsoft services to other cloud providers.<\/p>\n

Even more: Azure ARC<\/strong> also allows the deployment of Azure Services<\/strong> to on-premises services.<\/p>\n

During the demonstration, Azure ARC<\/strong> was able to provision an Azure SQL Managed Instance<\/strong> in 30 seconds. It was incredible! Just a few weeks before I have talked about Azure SQL Managed Instance<\/strong> and the provisioning time was 3 hours or more. So, I asked at the Data Clinic, how was it possible? How could Azure ARC<\/strong> cause a so huge difference in the provision time?<\/p>\n

The answer was very interesting: Because Azure ARC<\/strong> was provisioning the managed instance inside a container. In this case, would the container version of a SQL Managed Instance<\/strong> have any difference in relation to the regular version of a SQL Managed Instance<\/strong>? Yes, they will be slightly different, but the difference is not clearly documented yet.<\/p>\n

In order to learn more about <\/em>Azure Arc<\/em><\/strong> you can access this link: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/azure-arc\/<\/em><\/a><\/p>\n

\"A<\/p>\n

Azure Synapse Analytics<\/strong><\/h3>\n

It’s an oversimplification think that Azure Synapse Analytics<\/strong> is a new name for Azure SQL Datawarehouse<\/strong>. Azure Synapse Analytics<\/strong> contains Azure SQL Datawarehouse<\/strong>, but also contains many new features. It seems that it’s going to be a central point for all, or almost all, Azure Data Platform<\/strong>.<\/p>\n

One very important new feature is the Azure Synapse Studio<\/strong>. This new tool creates a unified experience among many data platform services: Azure SQL Datawarehouse<\/strong>, Spark<\/strong>, Power BI<\/strong> and more being used from a single front-end tool.<\/p>\n

Explaining in a better way: from the Azure Synapse Studio<\/strong> we have access to the Data Warehouse<\/strong> model in Azure SQL Datawarehouse<\/strong> (this name will die, now it\u2019s Azure Synapse Analytics<\/strong>), manipulation of the data using Spark<\/strong> (I’m not sure if it’s linked with Databricks<\/strong>), machine learning models with Azure Machine Learning<\/strong> and visualization dashboards with Power BI<\/strong>, this one connected to the Power BI<\/strong> portal. You can see the changes made in the portal and anything you change from Synapse Analytics<\/strong> will affect the portal.<\/p>\n

\"A<\/p>\n

\nCan you make any kind of Power BI<\/strong> implementation inside Azure Synapse Studio, <\/strong>such as dataflows<\/strong> and so on? Not yet, but it’s just a beginning, it’s expected to evolve a lot.<\/p>\n

In summary, Azure Synapse Analytics<\/strong> is much more than only Azure SQL Datawarehouse<\/strong>, it\u2019s not a only a name change.<\/p>\n

You can learn more about <\/em>Azure Synapse Analytics<\/em><\/strong> on these links:<\/em><\/p>\n

https:\/\/azure.microsoft.com\/en-us\/blog\/simply-unmatched-truly-limitless-announcing-azure-synapse-analytics\/<\/em><\/a><\/p>\n

https:\/\/azure.microsoft.com\/en-us\/services\/synapse-analytics\/<\/em><\/a><\/p>\n

Data Tools for Synapse Analytics<\/strong><\/h3>\n

SQL Server Data Tools<\/strong> \u2013 SSDT<\/strong> \u2013 is around for many years, even more than Entity Framework Migrations<\/strong>, which is broadly used today.<\/p>\n

I have used SQL Server Data Tools<\/strong> as part of the deployment process in many projects, it\u2019s great to control the version of the database and make schema compares.<\/p>\n

The most recent versions were a bit disappointing, the features for schema compare were reduced, I never discovered why. Due to that, the best recommendation was to go for 3rd<\/sup> part tools when needed.<\/p>\n

It seems our relationship with SSDT<\/strong> is getting into a new chapter. SSDT<\/strong> has a new database project for Synapse Analytics<\/strong>, allowing us to create our data warehouse structure in SSDT<\/strong> and control the CICD process.<\/p>\n

You can learn more about SSDT and Synapse Analytics on this link: <\/em>https:\/\/cloudblogs.microsoft.com\/sqlserver\/2019\/11\/07\/new-in-azure-synapse-analytics-cicd-for-sql-analytics-using-sql-server-data-tools\/<\/em><\/a><\/p>\n

Azure Data Share<\/strong><\/h3>\n

\"A
\nData Share<\/strong> is a new azure service which allows a company to share data with other companies.<\/p>\n

Is it better than creating services for the other company?<\/p>\n

Well, using Data Share<\/strong> we are manipulating data, sharing parts of our data lake without need to develop something. In the same way, the other company will receive data in order to input in their own system. Is this a benefit? Maybe.<\/p>\n

Another possible reason to use\u00a0Data Share<\/strong> would be for governance: all shared pieces of data would be controlled in the same place, the data shares. However, it may only work well among companies using Azure<\/strong>, if the other company is using another cloud service, I believe data share may not work.<\/p>\n

You can read more about <\/em>Data share<\/em><\/strong> on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/data-share\/<\/em><\/a><\/p>\n

Big Data Cluster<\/strong><\/h3>\n

Big Data Clusters are the most highlighted feature in SQL Server 2019<\/strong>. A big summary of what it is would be saying it’s a Microsoft implementation of a Hadoop<\/strong> and Data Lake<\/strong> solution.<\/p>\n

Would you like to know more about <\/em>Hadoop<\/em><\/strong>? Check this link: <\/em>https:\/\/hadoop.apache.org\/<\/em><\/a><\/p>\n

Learn more about <\/em>Data Lake<\/em><\/strong> on this link: <\/em>https:\/\/en.wikipedia.org\/wiki\/Data_lake<\/em><\/a><\/p>\n

\nLet’s simplify the explanation, step by step. Big Data Cluster<\/strong> is an implementation of a Cluster processing solution. This means this feature allows to create a set of servers, with a master node and slave nodes. The master node can receive jobs to process, break these jobs among the slave nodes and collect the results later, resulting the job being processed by many servers as a set, allowing SQL Server<\/strong> to process huge amounts of data.<\/p>\n

In the past, this solution was built before using the name of Parallel Data Warehouse<\/strong>. PDW<\/strong> is a SQL Server<\/strong> appliance, but it can be easily understood as a different SQL Server<\/strong> edition. It’s a cluster for parallel processing, exactly as Big Data Cluster<\/strong>. What’s the difference?<\/p>\n

You can learn more about Parallel Data Warehouse on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/sql\/analytics-platform-system\/parallel-data-warehouse-overview?view=aps-pdw-2016-au7<\/em><\/a><\/p>\n

\nThe difference is in relation to the language and storage of the data. PDW<\/strong> is totally based on SQL architecture and language. On the other hand, we have for a long time an open architecture called Hadoop<\/strong> and some variations of it, such as Spark<\/strong>.<\/p>\n

During the growth of Azure<\/strong> and the cloud environment, both solutions were created in the cloud. PDW<\/strong> was created as SQL Data Warehouse<\/strong> while Hadoop was created as HD Insight<\/strong>, which provides many flavours of Hadoop<\/strong>, such as Spark<\/strong> and more. It became a kind of a race to see which technology would conquer the heart of the market, parallel processing clusters based in SQL or based in Hadoop<\/strong>.<\/p>\n

Learn more about HD Insight on this link: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/hdinsight\/<\/em><\/a><\/p>\n

\"A
\nBig Data Cluster<\/strong> is a Hadoop-like<\/strong> solution which can be used in the on-premises environment. However, it’s way more than a simple Hadoop-like<\/strong> solution. Big Data clusters use HDFS Tiering<\/strong> to create Data Virtualization<\/strong> and allow us the creation of a Data Lake<\/strong>, an environment where the data doesn’t need to be moved from its source location in order to be processed.<\/p>\n

HDFS tiering<\/strong> was also introduced to us during a sponsored breakfast organized by DELL. It\u2019s a feature linked to Big Data Cluster<\/strong> in SQL Server 2019<\/strong> which allow us to mount external storages into a single HDFS<\/strong> storage. This leads us to the Data Lake<\/strong> concept: instead of moving the data among storages and technologies, leave the data in its place and process the data where it already is.<\/p>\n

You can read more about <\/em>HDFS tiering<\/em><\/strong> here: <\/em>https:\/\/docs.microsoft.com\/en-us\/sql\/big-data-cluster\/hdfs-tiering?view=sql-server-ver15<\/em><\/a><\/p>\n

Learn more about <\/em>Data Virtualization<\/em><\/strong> on this link: <\/em>https:\/\/blogs.technet.microsoft.com\/machinelearning\/2017\/06\/21\/data-virtualization-unlocking-data-for-ai-and-machine-learning\/<\/em><\/a><\/p>\n

Another huge difference is the use of Polybase<\/strong>. In order to reach the data sources, Big Data Cluster<\/strong> uses Polybase<\/strong>. However, that’s not the old Polybase<\/strong> we discovered in SQL Server 2016<\/strong>. That’s a new Polybase<\/strong> with support to many different data sources, such as Oracle and much more. Polybase<\/strong> uses its pushdown technology to delegate the processing of SQL predicates to the remote machine responsible for the data.<\/p>\n

I still have a lot to understand and write about the evolution of Polybase<\/strong>, because the old Polybase<\/strong> in SQL Server 2016<\/strong> required a server configuration to set the technology to which the connection would be made, so one SQL Server<\/strong> instance would be tied with a single technology source for Polybase<\/strong>. Besides that, the pushdown technology was very difficult to configure with Hadoop<\/strong>. I believe all these have changed, resulting in the Big Data Clusters<\/strong>, an on-premise (or IaaS<\/strong>) solution for the creation of a Data Lake<\/strong>.<\/p>\n

Learn more about <\/em>Polybase<\/em><\/strong> on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/polybase\/polybase-guide?view=sql-server-ver15<\/a><\/p>\n

You can read more about <\/em>Big Data Clusters<\/em><\/strong> on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/sql\/big-data-cluster\/big-data-cluster-overview?view=sql-server-ver15<\/em><\/a><\/p>\n

Azure Databricks<\/strong><\/h3>\n

\"A<\/p>\n

\nDatabricks<\/strong> is Spark<\/strong> with a new name. This was everything I knew about Databricks<\/strong> before the PASS Summit<\/strong> and this leaves a lot of questions.<\/p>\n

Since we already had Spark working as one of the flavours of HDInsight<\/strong>, why we need Databricks<\/strong>?<\/p>\n

I got two answers for that:<\/p>\n

A) Databricks<\/strong> is a more complete spark, since it loads a more complete set of modules by default than the Spark<\/strong> we can provision with HDInsight<\/strong>.<\/p>\n

B) Databricks<\/strong> doesn’t have the provision problem. Spark<\/strong> in HDInsight<\/strong> needs to be provisioned, used and deleted because the cost to have the Spark<\/strong> servers provisioned is huge. Databricks<\/strong>, on the other hand, doesn’t have this problem, it charges you for the time you use it.<\/p>\n

You can learn about this provisioning challenge on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/data-factory\/v1\/data-factory-compute-linked-services#azure-hdinsight-on-demand-linked-service<\/em><\/a><\/p>\n

I loved the second explanation, and this leads us to the next question. However, I would find later this was an incomplete answer. The correct answer, but incomplete.<\/p>\n

Since Databricks<\/strong> doesn’t have the provisioning problem anymore, it’s competing with Azure Data Lake<\/strong>, isn’t it? Aren’t both doing the same thing?<\/p>\n

The unofficial answer I received was: Yes, they are. However, Azure Data Lake<\/strong> its language, U- SQL<\/strong>, are dead, you shouldn’t build anything new with them<\/p>\n

First, it\u2019s important for you to mind that in this context I’m not talking about Data Lake Storage<\/strong>, which is alive and kicking. I’m talking about Azure Data Lake<\/strong> and its language, U-SQL<\/strong>, which were a cluster processing solution on-demand, without the provisioning challenges. At some point, Azure Data Lake<\/strong> started to be called Azure Data Lake Analytics<\/strong>.<\/p>\n

You can read more about <\/em>Azure Data Lake Analytics<\/em><\/strong> here: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/data-lake-analytics\/<\/em><\/a><\/p>\n

Learn more about <\/em>U-SQL<\/em><\/strong> language on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/u-sql\/<\/em><\/a><\/p>\n

\nAnother thing you should mind is that this is an unofficial answer. If you will consider this or not, it’s your choice, according how usually unofficial answers from people close to Microsoft are proven to be true sometime later.<\/p>\n

These are not the only advantages of Databricks<\/strong>. The demonstrations during the sessions highlighted how Databricks<\/strong> keeps a record of machine learning training models in such a way we can identify a history of improvement according we work in our ML models.<\/p>\n

It’s not clear how much would this be linked to Azure Machine Learning<\/strong>, but it doesn’t seem to be a kind of link such as Synapse Analytics<\/strong> has with Power BI<\/strong>.<\/p>\n

Databricks also has an improved storage which received its own name: Delta Lake<\/strong>.<\/p>\n

You can read more about <\/em>Databricks<\/em><\/strong> on this link: <\/em>https:\/\/azure.microsoft.com\/en-us\/services\/databricks\/<\/em><\/a><\/p>\n

Learn more about <\/em>Delta Lake<\/em><\/strong> on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/databricks\/delta\/delta-intro<\/em><\/a><\/p>\n

Get more information about <\/em>Azure Machine Learning<\/em><\/strong>: <\/em>https:\/\/docs.microsoft.com\/en-us\/azure\/machine-learning\/overview-what-is-azure-ml<\/em><\/a><\/p>\n

Power BI <\/strong><\/h3>\n

One of the main highlights for Power BI<\/strong> was the Data Protection and Governance<\/strong> new features. This show how Microsoft<\/strong> is engaged in turning Power BI<\/strong> into an enterprise product.<\/p>\n

In summary, Power BI<\/strong> will have a link with Sensitive Labels<\/strong> already used in Microsoft Office<\/strong> in order to classify information. Users will be able to classify the information as confidential and other levels and this new feature comes with additional features for monitoring, permissions, governance and so on.<\/p>\n

You can learn more about <\/em>Sensitive Labels<\/em><\/strong> on this link: <\/em>https:\/\/docs.microsoft.com\/en-us\/microsoft-365\/compliance\/sensitivity-labels<\/em><\/a><\/p>\n

Well, I’m not so sure if this will become something usual, Sensitive Labels<\/strong> require an additional level of management and planning in the organization, I don’t see many clients using this feature.<\/p>\n

You can read more about these features here: <\/em>https:\/\/powerbi.microsoft.com\/en-us\/blog\/announcing-new-data-protection-capabilities-in-power-bi\/<\/em><\/a><\/p>\n

In some ways linked to this, but way more important is the Data Lineage<\/strong> feature, an important feature for data warehouse environments.<\/p>\n

In the past, Microsoft tried to include this feature inside ETL<\/strong> tools, it was when SSIS<\/strong> was still called DTS<\/strong> (ops, this may reveal my age!). It didn’t get the attention it deserves, in my opinion not because the feature was bad, but because usually data lineage, although very important, is way down in the list of concerns when building a data warehouse and many people working on this don’t even know exactly what data lineage is and why it’s important.<\/p>\n

As the name stands for, the Data Lineage<\/strong> feature keeps track of the source data used to produce a report or dashboard. This feature can save your job when, after building a very expensive data warehouse, two dashboards built by different users show opposite information about the company business. Using the Data Lineage<\/strong>, you can track the information used by the users and identify why the dashboards are different, understanding each dashboard point of view.<\/p>\n

\"A<\/p>\n

So, will this feature work this time? I hope so.<\/p>\n

More about Power BI Data Lineage: <\/em>https:\/\/docs.microsoft.com\/en-us\/power-bi\/service-data-lineage<\/em><\/a>
\n<\/em><\/p>\n

Now, let’s talk about the most surprising news about Power BI: the fact Microsoft is moving Power BI towards a position to replace Azure Analysis Service. Yes, replace Azure Analysis Services.<\/p>\n

In order to better understand this, let\u2019s go back in time. SQL Server Analysis Services<\/strong> (SSAS<\/strong>) is an on-premises server built to create Semantic Models<\/strong> as Data Marts<\/strong>.<\/p>\n

Data Mart<\/strong> is a focused piece of the Data Warehouse which can be distributed through the company. That\u2019s why Data Marts<\/strong> are usually built as Semantic Models<\/strong>, a model which can be easily understood by business people, less complex than the entire Data Warehouse<\/strong><\/p>\n

On this link, you will find more information about this move: <\/em>https:\/\/powerbi.microsoft.com\/en-us\/blog\/power-bi-premium-and-azure-analysis-services\/<\/em><\/a><\/p>\n

Before mentioning the most surprising news about Power BI<\/strong>, let’s analyse the current relationship between SSAS<\/strong> and Power BI<\/strong>.<\/p>\n

SSAS<\/strong> is a tool which allows us to create a semantic model for our Data Warehouse<\/strong>. According to the data warehouse architecture, this is a Data Mart<\/strong>, a focused piece of the entire DW<\/strong>. The SSAS<\/strong> data mart is not only focused, but it’s a semantic model, meaning it’s built to be self-explaining to businesspeople. One of the best client tools for SSAS<\/strong> is Excel<\/strong>, once connected to SSAS<\/strong>, the data mart is exposed as a cube, allowing the business user to create pivot table reports using the measures they need at the moment, in a tool they are already used to.<\/p>\n

SSAS<\/strong> supports two types of models: Multi-dimensional and Tabular. They have many differences between them, but these differences are disappearing with the evolution of the tool, although we can\u2019t say yet they have the same features.<\/p>\n

In order to better understand the difference between the two models, you can follow the two articles in which I illustrate how to build the same data mart with each of the models, highlighting the differences.<\/p>\n

Multi-dimensional model: <\/em>https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/creating-a-date-dimension-in-an-analysis-services-ssas-cube\/<\/em><\/a><\/p>\n

Tabular model: <\/em>https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/creating-a-date-dimension-in-a-tabular-model\/<\/em><\/a><\/p>\n

You can learn more about <\/em>SSAS<\/em><\/strong> semantic model on this link: <\/em>https:\/\/blogs.msdn.microsoft.com\/microsoft_press\/2017\/10\/23\/designing-a-multidimensional-bi-semantic-model\/<\/em><\/a><\/p>\n

Azure Analysis Services<\/strong> only implements the tabular model, not the multi-dimensional model. This creates questions about the future of the multi-dimensional model, although the differences\/limitations of the tabular in relation to the multi-dimensional. For example, I\u2019m not a fan of the tabular limitation to have only one active relationship between two tables. What do you think about? Let\u2019s talk more in the comments.<\/p>\n

Power BI<\/strong>, on the other hand, was created to be a Visualization<\/strong> and Self-Service BI<\/strong> tool. The idea is to allow the business users to access the corporate Data Warehouse<\/strong>\/Data Mart<\/strong> and merge it with any additional information the business needs, even information collected from the web. In this way, the user doesn’t need to make special requests to the corporate IT department, avoiding delays and overwhelming the IT department. As part of the Self-Service BI<\/strong> concept, Power BI<\/strong> also offers Self-Service ETL<\/strong> using Power Query<\/strong> and, more recently, Power BI Dataflows<\/strong>, allowing even a business user to create ETL<\/strong> code to retrieve the needed information.<\/p>\n

Power BI<\/strong> was born from Excel<\/strong> plug-ins. Each part of Power BI was a different Excel<\/strong> plug-in, such as Power Query<\/strong> and Power Pivot<\/strong>. Power Pivot<\/strong>, on the other hand, was created from the same tabular engine than SSAS<\/strong>, just with some additional size limitations to fit into Excel<\/strong>.<\/p>\n

Therefore, Power BI<\/strong>, SSAS<\/strong> and Azure Analysis Services<\/strong> share the same core engine for the tabular model and Microsoft is working to eliminate the differences. It makes sense the idea of replacing Azure Analysis Services<\/strong> with Power BI<\/strong>, leaving only two questions without a clear answer:<\/p>\n

    \n
  1. The complete set of tabular model features is only available in Power BI Premium, which is expensive. Power BI Pro has limitations which prevent the development of a complete tabular semantic model. Analysis Services, on the other hand, has SSAS as an on-premises option, allowing the company to start the development on the correct way and scale up for bigger and cloud editions in the future. How to solve this dilemma?<\/li>\n
  2. Will the tabular model, an in-memory solution, really replace the multi-dimensional model, which could store pre-calculations of many combinations of the dimensions?<\/li>\n<\/ol>\n

    On this link, you will find more information about the replacement move between power bi premium and Azure Analysis Services: <\/em>https:\/\/powerbi.microsoft.com\/en-us\/blog\/power-bi-premium-and-azure-analysis-services\/<\/em><\/a><\/p>\n

     <\/p>\n

    Conclusion<\/strong><\/h3>\n

    2020 is starting full of new and exciting technologies for Data Platform<\/strong> in the cloud, it\u2019s time to recycle our knowledge.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    From last November 4 to 8 I have been at\u00a0PASS Summit in Seattle, attending excellent technical sessions and having the honour to deliver a\u00a0 lightning session, which is a small 10 minutes presentation. Why should you read this? Being one of the biggest data events in the world, PASS Summit usually exposes the present and……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6810],"class_list":["post-86736","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86736","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86736"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86736\/revisions"}],"predecessor-version":[{"id":86785,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86736\/revisions\/86785"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86736"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}