Sunday, October 30, 2016

Pin SSRS 2016 Reports to Power BI

To pin SSRS 2016 Report to Power BI,

  • Connections must use stored credentials
  • Only the following items can currently pinned using SSRS 2016 and Power BI Integration
    • Charts
    • Gauge panels
    • Maps
    • Images

a) Open SQL Server 2016 Reporting Services Configuration Manager and select Power BI Integration

SSRSPowerBI-Integration1

b) Register your SSRS Report Server with Power BI by clicking the Register with Power BI button and provide the necessary Power BI credentials.

SSRSPowerBI-Integration2

c) Go to your SSRS 2016 Portal and click on “My Settings”

SSRSPowerBI-Integration3

d) Make sure your SSRS is connected with Power BI

SSRSPowerBI-Integration4

e) Select the SSRS report to pin to Power BI and click the Power BI icon.

SSRSPowerBI-Integration5

f) Clicking the Power BI icon allows to select only the Chart and not the table. Currently it is a limitation and need to watch the space for any updates in the future.

SSRSPowerBI-Integration6

g) Click the Chart and Pin to PowerBI Dashboard dialog will appear and Select the Group, Dashboard and Frequency updates.

SSRSPowerBI-Integration8

h) Open Power BI and view the SSRS Chart

SSRSPowerBI-Integration9

Technorati Tags: ,

Create a Mobile Report using SQL Server Dataset

a) Create a new Connection on SSRS 2016 Portal connecting to AdventureWorks Database.

MobileReportSQLServer1

b) Create a new Dataset on SSRS 2016 using the AdventureWorks2012 connection created in Step a

MobileReportSQLServer2

c) Add the query to the Dataset using Report Builder.

MobileReportSQLServer3

d) Now you can add data using the above Dataset in the Mobile Report

MobileReportSQLServer4

e) Final dashboard using the above dataset

MobileReportSQLServer5

Technorati Tags: ,

Saturday, October 29, 2016

Building your first SQL Server Mobile Report

a) Download SQL Server Mobile Report Publisher tool from https://www.microsoft.com/en-us/download/details.aspx?id=50400

b) Open Mobile Report Publisher and click Data

MobileReport1

c) Click on Add Data

MobileReport2

d) Select Excel and Sheet2MobileReport3

e) Change Title of the Mobile Report and Save Mobile Report to Server (SSRS Report Server – native mode)

MobileReport4

f) Here is fully built dashboard.

IndianPlayersDataAnalysis

Download Mobile Report

Technorati Tags: ,

Friday, October 28, 2016

Build modern BI solutions using SSRS 2016 – Slides and Useful Links

As promised, here are the responses for the questions I remembered and I will watch the recorded video and post some responses over the next week just in case if I missed any.

PowerPoint Slides: https://1drv.ms/b/s!AqQY0KY6_6uRmQiXQ6wP1Xrq4hEm

Custom Brand Package using SSRS 2016

https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/03/20/how-to-create-a-custom-brand-package-for-reporting-services-with-sql-server-2016/
https://msdn.microsoft.com/en-us/library/mt710551.aspx


SSRS 2016 Native mode vs SharePoint Integrated mode


Feature
Integrated Mode
Native Mode
HTML 5 based rendering Engine
X
X
New Chart Types
X
X
PDF based printing (No ActiveX)
X
X
PowerPoint rendering and Export
X
X
New UI for Report Builder
X
X
Customisable Parameter Panes
X
New Web Portal
X
Mobile Reports
X
KPIs
X
Pin to Power BI
X
Render Power BI Reports in SSRS Portal
X (preview released)

SSRS 2016 and Windows Authentication and Mobile Reports
https://msdn.microsoft.com/en-us/library/cc281253.aspx

Whether Mobile Reports are available for Standard Edition? – No, it is available only for Enterprise Edition currently. Check Data Sheet for SQL Server 2016

Checklist to decide on Paginated Reports vs Mobile Reports – Coming soon

Pin Reporting Services item with Power BI
https://msdn.microsoft.com/en-us/library/mt604784.aspx

Check blog post - http://codespot.blogspot.co.nz/2016/10/pin-ssrs-2016-reports-to-power-bi.html

Note: You can pin only certain report elements only to the Power BI Portal. e.g. tables cannot be pinned for now. Sorry, I wasn't able to validate that perfectly until now as I had to fix an issue with linking Power BI and SSRS 2016.

Is it possible to access the Reports both through Report Server and Reports URL?
Yes, it is still available for Paginated Reports but not yet for Mobile Reports and KPIs.https://msdn.microsoft.com/en-us/library/ms153586.aspx
ReportServerURL1

Below is the error you get for Mobile Reports and KPIs if you try to access it through the Report Server URL.
ReportServerURL2

Mobile Reports Samples

Thanks Christopher Finlan

Report Authoring Tools - https://msdn.microsoft.com/en-us/library/ms155792.aspx

Mobile Reports and Version Control – Coming soon

List of Data Sources supported by SSRS and mobile reports - https://msdn.microsoft.com/library/ms159219.aspx
https://msdn.microsoft.com/en-nz/library/mt631348.aspx

MS Ignite NZ 2016 – Day 3

Day 3 was personally special to me as I was doing a talk at the MS Ignite NZ 2016 for the first time. Was I nervous? Of course, a little bit but Thanks to Microsoft for providing Speakers Training especially for first time speakers like me.

I attended the following sessions even though I had a talk to do.

Learn about the Dynamics 365 Roadmap

Nicole and Mike presented on the Roadmap of Dynamics 365 where AX and CRM are integrated to one. It really made sense why MS made that decision but interesting to know it only available on the cloud. Cloud First, Mobile First Microsoft Strategy definitely could understand that. Really good session and so impressed with the capabilities Dynamics 365 is going to offer customers and of course we are a Dynamics customer too.

Azure SQL Data Warehouse Made Simple

Chris Lowe and Myles Matheson took us through the journey of moving data (large volumes) to the Azure SQL Data Warehouse and really showed us how simple that was. The demo on using Power BI to then connect to the Azure SQL Data Warehouse and how quickly you can visualise it using Direct Query even though you are aggregating billions of data in real time. Really cool demo and thanks for sharing it with us.

Women in Technology Lunch Session

I was looking forward for this session and unfortunately could attend the entire session as I had to do a talk following this session. I attended it briefly and really had a great panel of speakers who really shared their experiences with us and need to watch the recording for the missed part. Truly inspiring.

Build modern Enterprise BI Solutions using SSRS 2016

It was my time to give my first presentation at MS Ignite NZ 2016. What a great experience it was? First of all thanks to Microsoft for giving me this wonderful opportunity to present at the Ignite Conference, Speakers Training was really useful too and Thanks Olivia Mitchell for that and of course Meridian Energy Limited (the place I work, best workplace) for the encouragement and support from day one all the way up to the final presentation day. My colleagues who attended the Ignite session with me and thoroughly supported me throughout. Few more mentions, Kirk Jackson for encouraging me to speak at the Local .NET User Group sessions in Wellington and also being there attending my session to support me. Bevan Arps and James from Spark whom I know from .NET user group days.

Last but not the least the technical crew who assisted me and made everything so easy and simple as I didn’t find anything hard all the way from setting up my laptop and doing my demos. You were really great and thanks for assisting me being a first time speaker at Ignite NZ 2016.

Coming to the audience, my session was full of questions and made my session more interactive. It was really good to understand what people wanted to know about the topic and the questions helped me to cover off anything I missed. Personally thoroughly enjoyed presenting at the Ignite Conference.

Thanks to everyone who gave some wonderful feedback about my session and it is really encouraging and motivating to do more in the future. As promised at the session, I will put a blog post on responses for some questions along with some useful links.

Thanks for my family too who were supporting me from India and US and encouraging me through their WhatsApp messages.

Feel free to contact me on twitter handle @shantha05 if you have any more questions.

Thanks to Kirk for taking some photos during the session.
20161027_13413720161027_13420620161027_14021620161027_14022520161027_14362220161027_143905
Technorati Tags: ,

MS Ignite NZ 2016 – Day 2

Day 2 was pretty interesting as it was full of technical sessions to attend. I attended the following sessions:

Power BI for Developers - An Introduction to the API, the new Custom Visual SDK and Power BI Embedded

Custom Visuals is something I am really excited with Power BI and the ability to generate Visuals and sharing it with the community through the Custom Visuals Gallery is great. The session was with the demo of creating a Custom Visual from scratch and also showed us how to take D3,js code and turn into a Custom Visual for Power BI. Thoroughly enjoyed though the speaker couldn’t cover much on Power BI embedded which is really a cool functionality where by you can now embed Power BI reports in your application quite easily. This will avoid developing reports within applications and try and re-use existing Power BI reports within it.

DevOps in the real world: a true story

DevOps, I really like that. It’s not about developers develop the App and throw it over the fence with Operations instead Developer and Operations need to work together starting from the initial development of the application through to deployment. It definitely involves a bit of culture change too. The session talked briefly on what DevOps is and how VSTS DevOps pack could be used mostly from a real world perspective. Great talk.

The NZ Fire Service Journey to the Cloud

I never want to miss Customer case studies as they are really good examples on understanding what pains and approaches people take in real world scenarios. Chris from NZ Fire Service took us through their journey of moving to the Cloud and also gave us a good intro about NZ Fire and how they serve the NZ community. Great talk and truly honest and valuable information and Thanks a lot for sharing a real world case study with us.

Architecting IOT for Smart Buildings: Deep dive into Microsoft's first engagement on energy smart buildings

Where do I start on this? IOT is big but Energy Smart Buildings, taking us to the next level of imagination. Really cool stuff and thoroughly enjoyed the Videos in the session and again how Microsoft applied this in their own premises to show how it added value to them before taking it to their customers. Really cool case study.

Integrating SaaS into your I.T. Strategy

Sonia Cuff and Regan Murphy took us through what things we need to be considering when we think about integrating SaaS applications with our application landscape. Really great session and highlighted the list of questions you need to asking your SaaS vendor before you integrate your app. It’s all about understanding the risks before we start to use them. Well presented session and personally really useful one for me as Architect where I get challenged to integrate with a lot of SaaS applications.

On the whole had a really wonderful day at MS Ignite and once again thanks to the Sponsors and Microsoft for such an event where you get to see experts within your community as well as across the world coming and sharing their experiences.

This time I got the photo of our proud sponsors for the MS Ignite 2016 event.

Ignite2016sponsors

Technorati Tags: ,

Wednesday, October 26, 2016

MS Ignite NZ 2016 - Day 1

MS Ignite is the major technology conference in New Zealand and it started in its own style yesterday with the Keynote. Initially I was surprised to see a whole list of speakers for the Keynote but it really made sense in the way they presented it. There are some key topics that was covered as part of the Keynote

  • Chris Auld, talking about Surface-HubHololens and it was cool to have a look at it and pre-orders are available for NZ now.
  • Jordanna Murray, on Surface Hub with 84'' and 55'' screen sizes. The highlight is its multi touch capability and that will take collaboration to the next level.
  • Hannah Gray, from Xero walked us through Power BI and how simple it is to create interactive dashboards and use them
  • Hannes Nel, who celebrated his birthday yesterday gave us a demo on Universal Windows Platform
  • Donovan Brown, talked to us about Cross Platform Mobile Development using Xamarin and I have used Xamarin personally from MonoTouch days but Xamarin Test Cloud was really good.
  • Regan Murphy, demonstrated the use of Azure Dev/Test Labs, a solution of sorting out DEV and TEST environments for organisations
  • Thiago Almedia, showed us a cool demo on Bots using Microsoft Bot Framework using Skype.
  • Nigel Parker, gave us a demo on Cognitive Services and it how it can recognize vision, text, speech, face, language etc. Clearly can see the future...
  • Vishesh Oberoi, also gave us a demo on the Bot Framework
  • Jennifer Marsman, gave us an example on Machine Learning
  • Charlotte Walshe, talked to us about Internet of Things (IoT)
  • Angie Judge, on Big Data and Analytics and showing us how Dexibit provide Data Driven Insights to museums
  • Dona Sarkar, she really inspired the audience through her Keynote speech.

I attended 3 sessions yesterday.

What's new in Data Platform - SQL Server and Microsoft Azure - Really got a lot of information from this session. There was one diagram in the presentation which clearly outlined about the different products under SQL Sever on-premises and Azure. This session also lightly touched on Bots and Cognitive Services.

Managing Complex Implementations in the era of the Digital Transformation (DT) - Really good presentation which more or less explained on what things to be taken into account when you are working on DT projects. Also highlighted the fact that planning in DT projects must be a team activity and also how Architect are centric for DT projects.

Using Azure API Management, Logic Apps and Functions to speed up Integration projects - Good overview and demo on using Azure API management and how to use Logic Apps to orchestrate Services together and of course tips and tricks we need to consider.

So really a great day yesterday and looking forward for Day 2 - full of technical sessions. Thanks to all the sponsors of MS Ignite NZ 2016.

Sunday, October 23, 2016

PowerApps – Turning Data into Apps

PowerappsMicrosoft PowerApps is a solution to build custom business applications that enables increased productivity with business apps that are easily created, shared and managed. PowerApps offers a modern, intuitive experience for LOB application development

Power Apps allows you to create applications automatically without writing any code and can be built over existing datasets stored in the form of Excel spreadsheets, SQL Databases, Custom SharePoint List, CRM Online, Salesforce or using Microsoft Common Data Model.

This may sound similar to the LightSwitch Application which Microsoft introduced back in 2011. But PowerApps are not only just used to create applications but also allows applications to integrate with other SaaS products in the market.

PowerAppsArchitecture

Note: Microsoft have released a blog post on LightSwitch update and advised that Visual Studio 2015 is the last release of Visual Studio that includes the LightSwitch tooling and we recommend users not begin new application development with LightSwitch.

Creating your first PowerApp

a) PowerApps are still in preview mode but you can sign in using an organisation email address. Register for PowerApps

Signup

b) Once logged in, click on “Create an app”

PowerApp-1

c) Select “PowerApps studio for Web”

PowerApp-2

d) Select One Drive for Business from the list of Datasets

Screen1

e) Choose OneDrive and connect to your OneDrive with your credentials and provide PowerApps the necessary permissions.

Screen2

Screen3

Screen4

f) Choose the sample Excel spreadsheet from your OneDrive folder.

Screen5

g) Choose the table from the Spreadsheet and click Connect

Screen6

h) PowerApp will be created for the selected dataset.

Screen9

Technorati Tags: ,

Google BigQuery – Analytics Data Warehouse

GoogleCloudPlatformGoogle handles Big Data every second of every day to provide services like Search, YouTube, Gmail and Google Docs. Google created a Query Service named “Dremel” which was used just internally within Google.

Dremel is a query service that allows you to run SQL-like queries against very, very large data sets and get accurate results in mere seconds. You just need a basic knowledge of SQL to query extremely large datasets in an ad hoc manner.

BigQuery is the public implementation of Dremel. BigQuery provides the core set of features available in Dremel to third party developers. It does so via a REST API, a command line interface, a Web UI, access control and more, while maintaining the unprecedented query performance of Dremel.

BigQuery can scan billions of rows in a highly performant manner for ad hoc query analysis. It does achieve high performance through Columnar Storage and Tree Architecture. BigQuery Client Libraries - https://cloud.google.com/bigquery/client-libraries

Currently Microsoft is planning to provide Google BigQuery connector for Power BI. In the interim, you can import data from Google BigQuery using an ODBC driver, which is fully supported for Import scenarios in Power BI Desktop, and Personal/Enterprise Gateway for Refresh purposes.

BigQuery vs MapReduce

MapReduce is a distributed computing technology that allows to implement custom “mapper” and “reducer” functions programmatically and run batch processes with them on hundreds or thousands of servers concurrently. MapReduce is designed as a batch processing framework, so it’s not suitable for ad hoc and trial-and-error data analysis.

BigQuery is designed to handle structured data using SQL.MapReduce is a better choice when you want to process unstructured data programmatically. The mappers and reducers can take any kind of data and apply complex logic to it.

Use BigQuery

  • Finding particular records with specified conditions. For example, to find request logs with specified account ID.
  • Quick aggregation of statistics with dynamically-changing conditions. For example, getting a summary of request traffic volume from the previous night for a web application and draw a graph from it.
  • Trial-and-error data analysis. For example, identifying the cause of trouble and aggregating values by various conditions, including by hour, day and etc...

Use MapReduce

  • Executing a complex data mining on Big Data which requires multiple iterations and paths of data processing with programmed algorithms.
  • Executing large join operations across huge datasets.
  • Exporting large amount of data after processing.

Power BI Desktop – Google Analytics Integration

PowerBIDesktop

Download Power BI Desktop or from the Power BI Portal. Power BI provides out of the box integration with Google Analytics through the Google Analytics Core Reporting API. Google Analytics Core Reporting API change log to track any changes released by Google.

I tried to get some analytics on my blog through Power BI Desktop – Google Analytics Integration.

Note: The Google Analytics content pack and the connector in Power BI Desktop rely on the Google Analytics Core Reporting API. As such, features and availability may vary over time.

a) Open Power BI Desktop – Free tool provided by Microsoft to install on desktops and immediately start pulling data from disparate sources and start building Visualisations over that.

b) Click on “Get Data” from the tool bar and select “Online Services” and choose “Google Analytics”.

PowerBI-GA-1

c) You will then displayed with the message advising that Power BI connects to a third party service which in this case is the Google Analytics Core Reporting API. You can check the Don’t warn me again for this connector checkbox and click “Continue”

PowerBI-GA-2

d) If you haven’t already connected your Google Analytics account with Power BI Desktop then you will be provided with the below screen to connect to your Google Analytics account.

PowerBI-GA-2a

PowerBI-GA-2b

PowerBI-GA-2bb

PowerBI-GA-2c

e) Once provided the credentials and access to Power BI you can then connect to your Google Analytics account from Power BI Desktop.’

PowerBI-GA-2d

f) Select the Google Analytics account and start choosing your dimensions and measures you would like to analyse and Power BI Desktop will import them for you.

PowerBI-GA-3

PowerBI-GA-4

g) Now you can start building your visualisations on your Google Analytics data using Power BI Desktop similar to the below one.

FinalOutput