In the second half of this session, we'll connect to Azure SQL DW, load some risk data, improve the data model, create some insightful reports and dashboards, ask some questions then analyse in Excel. We'll use an example of financial market risk data. This is stored in data warehouses since a bank can easily generate a few hundred million rows of each day and risk managers need to analyse this data over several days and months. Such large volumes make this data a prime candidate for moving to Azure SQL DW. In addition, the elastic capabilities of SQL DW are very useful for example at UAT phases when users need an additional large dataset available for a (hopefully) short period of time. In the demo, we will use the scenario of an equity trading division with a bank. I will spend a couple of minutes introducing a small fictional dataset of the profit and loss (P&L) and VaR over a few years. (VaR, stands for Value At Risk, and is a common measure of the riskiness of the portfolio of trades.) We will load the data into Power BI desktop. We will improve the data model; build a hierarchy, and hide columns of no interest to our users, and calculate a few useful quantities using DAX. We will build a typical market risk report known as a back test chart which compares our P&L and VaR. We'll do this firstly using the standard and custom visuals then using the R Script visual to give us a more precise visualisation that meet the demands of regulators. Once done, we are ready to publish data and reports to the Power BI service. There will we pin a few visuals to a dashboard and then interrogate the data using plain English with the Q&A feature. Finally we will analyse our data in Excel. This is a very exciting new(ish) feature of Power BI and very useful to the risk managers in our scenario who traditionally do analyse their risk data in Excel pivot tables. This allows them to have their cake and eat it – to be able to visualise, explore and share their data with Power BI but also to take advantage of all the analytical power of Excel.
For this interview I got to talk to Amir Rajan on dynamic types in C#. He showed a very interesting use case (working with databases and tables) for the dynamic type and introduced us to some open source code he created to facilitate this kind of work. Here's some links to learn more:
Want to see what the future of web development will be like? Azure App Service combines three formerly separate development platforms—Azure Websites, Mobile Services, and BizTalk Services, into one simplified application development and delivery platform. We'll share how Web Apps helps developers build mission critical Web apps that scale with your business.