Category Archives: Azure

Welcome to Azure Synapse at SQL Saturday Tampa 2020

See the source image

SQL Saturday Tampa is coming up again in a couple of weeks on February 29th and I’m excited to announce that I’ll be there and presenting my session Welcome to Azure Synapse. In this session, I’m planning to deliver a overview of Azure Synapse as well as demo Synapse Studio!

Microsoft’s powerful new service, Azure Synapse, brings together the worlds of data warehousing and big data analytics by providing a unified experience for ingesting, cleansing, modeling, and sharing data. In this session, we’ll discuss the basics of Azure Synapse, ideal uses cases for Azure Synapse, and demonstrate how attendees can use Azure Synapse. By the conclusion of this session, attendees will have a deeper understanding of Azure Synapse and how to begin using Azure Synapse.

So if you’re in the area and interested in learning more about Azure Synapse, this will be a great session for you. You’ll need to register for SQL Saturday Tampa here. Don’t hesitate though, this event will fill up very quickly! I hope to see you there!

Dynamic Column Level Security with Power BI and SSAS

Last week I was asked to tackle a requirement by a customer adopting Analysis Services to enable data exploration and ad hoc analysis by their users. One of their requirements was to secure columns based on a grant related to a cost center. For example, a grant has several attributes, with some attributes being considered “sensitive” and other attributes considered “non-sensitive”. Non-sensitive grant attributes would accessible to all users while a subset of the attributes in the grant table considered “sensitive” would be accessible to users related to the corresponding cost center. The challenge here is that while Analysis Services supports column level security, dynamic column level security is not supported. So my colleague and friend, the great Steve Pontello, and I put our heads together to address the requirement.

Continue reading Dynamic Column Level Security with Power BI and SSAS

Power BI Architecture Diagram v4 is now available!

I’ve finally updated my Power BI Architecture Diagram to include some of the new features that are now available and will soon be available. There are three new updates I made to the diagram in v4.

Download the Power BI Architecture Diagram v4

Download the Power BI Architecture Diagram Legend

First and most importantly, I updated the Power BI logo in the diagram to the latest version of the logo!

power bi icon

Secondly, I included Power BI Dataflows in the diagram tagged #6. Power BI Dataflows are used to ingest, transform, integrate, and enrich big data by defining data source connections, ETL logic, refresh schedules, and more. Data is stored as entities in the Common Data Model in Azure Data Lake Storage Gen2. Dataflow entities can be consumed as a data source in Power BI and by using Power BI Desktop. Read more about Dataflows here.

Lastly, I also included item #14 to illustrate the upcoming capability Power BI users will have to directly access Power BI hosted data models via XMLA. This will allow you to connect to the Analysis Services data models using Tableau, Qlik, SSRS, SSMS, Dax Studio, SQL Server Profiler and other tools. Initially only read-access will be supported. Eventually write-access will be supported, meaning that I’ll be able to refresh Power BI data models using other client tools. This capability is not currently available at the time of me writing this blog post, but stay tuned.

And don’t forget to download the updated legend that goes with the Power BI Architecture Diagram used to describe and explain the components.

Thoughts and feedback?

Take a look at the diagram and let me know your thoughts and any feedback you have! And if you found it useful, leave a comment and let me know and share it with your colleagues!

Monitoring and Tuning Azure SQL Database at SQL Saturday Jacksonville and 24 Hours of Pass

I’m speaking at two upcoming events that I wanted to share with you! My topic is Monitoring and Tuning Azure SQL Database. Being able to monitor and tune the performance of your database is an important part of any database professional’s job. Azure introduces a host of new tools and capabilities designed to make optimizing your managed databases easier than ever. In this session, you’ll be introduced to using tools such as  Continue reading Monitoring and Tuning Azure SQL Database at SQL Saturday Jacksonville and 24 Hours of Pass

Converting a Power BI Desktop File from Import to Live Query

A customer of mine is in the midst of a proof of concept using SQL Server and Power BI. During the POC, all the modeling was done in Power BI Desktop. Now that the POC is coming to the next phase, the customer is ready to move the Power BI data model to Analysis Services. But the problem is that all the visualizations in the Power BI Desktop file based on the imported data model will need to be recreated in a new Power BI Desktop file using a Live Query connection to Analysis Services. If the visualizations and reports are extensive, this could be quite a bit of work.

In this blog post, I’m going to walk you through modifying a Power BI Desktop file with an imported data model to use an external data model hosted in Azure Analysis Services or SQL Server Analysis Services 2017. This isn’t supported by any stretch of the imagination but if you’re in a pinch and have to convert a Power BI Desktop file from an imported data model to Live Query then this may be helpful to you. Also, this method works as of the January 2018 release of Power BI Desktop but there’s no guarantee that this method will work in future releases of Power BI Desktop. Continue reading Converting a Power BI Desktop File from Import to Live Query

Creating a Knowledge Base Chat Bot w/ Azure Bot Service

A while back you may have caught my blog post introducing the Power BI Chat Bot 9000. Since posting that blog, I’ve received a lot of requests on the steps required to build the bot. The good new is that its super easy and really only take a few minutes to get it set up. In this blog post, I’m going to walk you through how to set up your own chat bot by creating a chat bot based on the SQL Database FAQ, which can be found here. Continue reading Creating a Knowledge Base Chat Bot w/ Azure Bot Service

Making Sense of Data Migration Assistant Assessments with Power BI

The Data Migration Assistant is a great tool developed to assess your SQL Server environment for a migration to a modern SQL Server platform such as SQL Server 2017 or Azure SQL Database. If you’re assessing a single SQL Server with only a few databases, reading the exported results (which can be in .JSON or .CSV format) may not be that difficult. But if you’re assessing dozens of environments including hundreds of databases, we need a better way to understand the results. Continue reading Making Sense of Data Migration Assistant Assessments with Power BI

Power BI FAQ Chat Bot 9000 lives!

So I’ve been working on a fun little project over the past couple weeks. A lot of my customers have questions related to Power BI and I spend a lot of my time answering those questions. So I said to myself, “Self, why don’t you build a chat bot that can help field some of those basic questions?” Thus was gloriously born the Power BI FAQ Chat Bot 9000!

The Power BI FAQ Chat Bot 9000 uses Azure Bot Service leveraging a knowledge base created using QnA Maker. Later I’m going to do a write-up on how exactly I created the Power BI FAQ Chat Bot 9000 (it only took me about 10 minutes), but for now I just want to share the bot with you. Start with “Hello” and then try Continue reading Power BI FAQ Chat Bot 9000 lives!

How to Automate Processing of Azure Analysis Services Models

I’ve been working on a proof of concept for a customer that involved using Azure Analysis Services as a cache for some data in an Azure Data Warehouse instance. One of the things I’ve been working on is scheduling the automatic processing of the Azure AS database. I did find the following documentation on the process, but the screenshots of the Azure portal are out of date and I did find some errors in the instructions. I also found this very extensive project for partition management in Azure AS, but this was a little overkill for my purposes and I was just interested in the very basics.

Read my recap for MS Data Summit here

These previously mentioned resources led me to write this blog post. In this post I’m going to leverage the previously mentioned article and walk through creating an Azure Function App to automatically refresh my Azure Analysis Services model, while correcting a few errors and updating the screenshots.

If you’re new to Azure Analysis Services, take a look at this documentation. For the purposes of this post, I’m going to assume you have a basic understanding of Analysis Services.

Continue reading How to Automate Processing of Azure Analysis Services Models

Setting up Cross Database Queries in Azure SQL Database

A question that I answer what seems like all the time is how to run cross-database queries against Azure SQL Database. So rather than answering those questions repeatedly I figured I should blog my response up so from here on out I can just forward the link to this blog on to my customers and others interested.

Most people I talk to still think that cross-database queries in Azure SQL DB aren’t possible. But thanks to a new feature called “Elastic Query”, we can issue cross database queries. There’s actually a few different types of cross database queries that Continue reading Setting up Cross Database Queries in Azure SQL Database