Guess who is appearing in Joseph Sirosh’s PASS Keynote?

This girl! I am super excited and please allow me to have one little SQUUEEEEEEE! before I tell you what’s happening. Now, this is a lifetime achievement for me, and I cannot begin to tell you how absolutely and deeply honoured I am. I am still in shock!

I am working really hard on my demo and….. I am not going to tell you what it is. You’ll have to watch it. Ok, enough about me and all I’ll say is two things: it’s something that’s never been done at PASS Summit before and secondly, watch the keynote because there may be some discussion about….. I can’t tell you what… only that, it’s a must-watch, must-see, must do keynote event.

We are in a new world of Data and Joseph Sirosh and the team are leading the way. Watching the keynote will mean that you get the news as it happens, and it will help you to keep up with the changes. I do have some news about Dr David DeWitt’s Day Two keynote… so keep watching this space. Today I’d like to talk about the Day One keynote with the brilliant Joseph Sirosh, CVP of Microsoft’s Data Group.

Now, if you haven’t seen Joseph Sirosh present before, then you should. I’ve put some of his earlier sessions here and I recommend that you watch them.

Ignite Conference Session

MLDS Atlanta 2016 Keynote

I hear you asking… what am I doing in it? I’m keeping it a surprise! Well, if you read my earlier blog, you’ll know I transitioned from Artificial Intelligence into Business Intelligence and now I do a hybrid of AI and BI. As a Business Intelligence professional, my customers will ask me for advice when they can’t get the data that they want. Over the past few years, the ‘answer’ to their question has gone far, far beyond the usual on-premise SQL Server, Analysis Services, SSRS combo.

We are now in a new world of data. Join in the fun!

Customers sense that there is a new world of data. The ‘answer’ to the question Can you please help me with my data?‘ is complex, varied and it’s very much aimed at cost sensitivities, too. Often, customers struggle with data because they now have a Big Data problem, or a storage problem, or a data visualisation access problem. Azure is very neat because it can cope with all of these issues. Now, my projects are Business Intelligence and Business Analytics projects… but they are also ‘move data to the cloud’ projects in disguise, and that’s in response to the customer need. So if you are Business Intelligence professional, get enthusiastic about the cloud because it really empowers you with a new generation of exciting things you can do to please your users and data consumers.

As a BI or an analytics professional, cloud makes data more interesting and exciting. It means you can have a lot more data, in more shapes and sizes and access it in different ways. It also means that you can focus on what you are good at, and make your data estate even more interesting by augmenting it with cool features in Azure. For example, you could add in more exciting things such as Apache Tika library as a worker role in Azure to crack through PDFs and do interesting things with the data in there. If you bring it into SSIS, then you can tear it up and down again when you don’t need it.

I’d go as far as to say that, if you are in Business Intelligence at the moment, you will need to learn about cloud sooner or later. Eventually, you’re going to run into Big Data issues. Alternatively, your end consumers are going to want their data on a mobile device, and you will want easy solutions to deliver it to them. Customers are interested in analytics and the new world of data and you will need to hop on the Azure bus to be a part of it.

The truth is; Joseph Sirosh’s keynotes always contain amazing demos. (No pressure, Jen, no pressure….. ) Now, it’s important to note that these demos are not ‘smoke and mirrors’….

The future is here, now. You can have this technology too.

It doesn’t take much to get started, and it’s not too far removed from what you have in your organisation. AzureML and Power BI have literally hundreds of examples. I learned AzureML looking at the following book by Wee-Hyong Tok and others, so why not download a free book sample?

How do you proceed? Well, why not try a little homespun POC with some of your own data to learn about it, and then show your boss. I don’t know about you but I learn by breaking things, and I break things all the time when I’m  learning. You could download some Power BI workbooks, use the sample data and then try to recreate them, for example. Or, why not look at the community R Gallery and try to play with the scripts. you broke something? no problem! Just download a fresh copy and try again. You’ll get further next time.

I hope to see you at the PASS keynote! To register, click here: 

Learning pathway for SQL Server 2016 and R Part 2: Divided by a Common Language

Britain has “really everything in common with America nowadays, except, of course, language.” Said Oscar Wilde, in the Centerville Ghost (1887) whilst George Bernard Shaw is quoted as saying that the “The United States and Great Britain are two countries separated by a common language.”

There are similarities and differences between SQL and R, which might be confusing. However, I think it can be illuminating to understand these similarities and differences since it tells you something about each language. I got this idea from one of the attendees at PASS Summit 2015 and my kudos and thanks go to her. I’m sorry I didn’t get  her name, but if you see this you will know who you are, so please feel free to leave a comment so that I can give you a proper shout out.

If you are looking for an intro to R from the Excel perspective, see this brilliant blog here. Here’s a list onto get us started. If you can think of any more, please give me a shout and I will update it. It’s just an overview and it’s to help the novice get started on a path of self-guided research into both of these fascinating topics.

R SQL / BI background
A Factor has special properties; it can represent a categorical variable, which are used in linear regression, ANOVA etc. It can also be used for grouping. A Dimension is a way of describing categorical variables. We see this in the Microsoft Business Intelligence stack.
in R, dim means that we can give a chunk of data dimensions, or, in other words, give it a size. You could use dim to turn a list into a matrix, for example Following Kimball methodology, we tend to prefix tables as dim if they are dimension tables. Here, we mean ‘dimensions’ in the Kimball sense, where a ‘dimension’ is a way of describing data. If you take a report title, such as Sales by geography, then ‘geography’ would be your dimension.
R memory management can be confusing. Read Matthew Keller’s excellent post here. If you use R to look at large data sets, you’ll need to know
– how much memory an object is taking;
– 32-bit R vs 64-bit R;
– packages designed to store objects on disk, not RAM;
– gc() for memory garbage collection
– reduce memory fragmentation.
SQL Server 2016 CTP3 brings native In-database support for the open source R language. You can call both R, RevoScaleR functions and scripts directly from within a SQL query. This circumvents the R memory issue because SQL Server benefits the user, by introducing multi-threaded and multi-core in-DB computations
Data frame is a way of storing data in tables. It is a tightly coupled collections of variables arranged in rows and columns. It is a fundamental data structure in R. In SQL SSRS, we would call this a data set. In T-SQL, it’s just a table. The data is formatted into rows and columns, with mixed data types.
All columns in a matrix must have the same mode(numeric, character, and so on) and the same length. A matrix in SSRS is a way of displaying, grouping and summarizing data. It acts like a pivot table in Excel.
 <tablename>$<columnname> is one way you can call a table with specific reference to a column name.  <tablename>.<columname> is how we do it in SQL, or you could just call the column name on its own.
To print something, type in the variable name at the command prompt. Note, you can only print items one at a time, so use cat to combine multiple items to print out. Alternatively, use the print function. One magic feature of R is that it knows magically how to format any R value for printing e.g.


PRINT returns a user-defined message to the client. See the BOL entry here.

CONCAT returns a string that is the result of concatenating two or more string values.

Variables allow you to store data temporarily during the execution of code. If you define it at the command prompt, the variable is contained in your workspace. It is held in memory, but it can be saved to disk. In R, variables are dynamically typed so you can chop and change the type as you see fit. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Variables are not dynamically typed, unlike R. For in-depth look at variables, see Itzik Ben-Gan’s article here.
ls allows you to list the variables and functions in your workspace. you can use ls.str to list out some additional information about each variable. SQL Server has tables, not arrays. It works differently, and you can find a great explanation over at Erland Sommarskog’s blog. For SQL Server 2016 specific information, please visit the Microsoft site.
A Vector is a key data structure in R, which has tons of flexibility and extras. Vectors can’t have a mix of data types, and they are created using the c(…) operator. If it is a vector of vectors, R makes them into a single vector. Batch-mode execution is sometimes known as vector-based or vectorized execution. It is a query processing method in which queries process multiple rows together. A popular item in SQL Server 2016 is Columnstore Indexes, which uses batch-mode execution. To dig into more detail, I’d recommend Niko Neugebauer’s excellent blog series here, or the Microsoft summary.

There will be plenty of other examples, but I hope that helps for now.

PASSBAC 2014: Empowering the Data Citizen; datacapability on any device

The main theme here is that Microsoft are opening up their data tools to any device. Bringing the power of data to everyone in the organisation. The journey for the data culture starts today!

How successful is it? Over at Copper Blue, we are big Power BI users. As a small organisation, the setup suits us just fine. What about other organisations? Well, according to Amir Netz and Kamil Hathi’s keynote, there’s been over one million Q and A queries in Power BI in the last month. That is amazing adoption.

I love that Microsoft are becoming more open. The truth is, we live in a heterogenous world when it comes to software and devices. Microsoft recognise this, and today their demo showed – not a Surface – but a Chromebook and HTML 5 enabled browser for their demo with Power BI. Microsoft have heard the message that users simply want their data to work, on any device. Although I’m a diehard Surface user, I do like the message. This is further crystallised in the fact that they announced that there will be a native app for iOS, which will be released in the summer.

I was delighted to see that Reporting Services will be in Power BI in the cloud by the end of the summer. For me, SSRS is an old, dear friend and I’m delighted to hear this news. I will always have a special place in my heart for SSRS. Although it’s probably impossible to quantify how many SSRS reports are being run across the world, I’d really love to know. All I know is that my customers often have SSRS reports in play, and they’re usually pretty happy with it when it is in place.

It’s also possible to create dashboards and KPIs using natural language typing.  This was very cool to see. I loved the flexibility and I know it will engage users. I was also happy to see a treemap. It’s something that customers often use.

What does this mean? It means that the full toolset in front of the business intelligence developer and user, in order to answer their questions. It is about making the masses datacapable.

Predictive analytics for the masses – wow! Describing the forecasting models in Power View is a new feature which was announced, and you can find it here.

Overall, the keynote was great, and it set the scene for a great day at the PASS BA Conference.
There was only a slight hiccup in the demo. I’ve said it before and I will say it again: stay away from the pie chart. Everything was great until the pie chart made an appearance, and everything went  fine again when it disappeared. Well, that’s my interpretation and I’m sticking to it…. 🙂

Joining the Digital Dots: Windows Azure SQL Reporting ends, replaced with Windows Azure VMs running SSRS – ringed with the Azure world

Apologies to Lord Tennyson for misquoting his wonderful poem “The Eagle”!
In the technical community, we do need to be ‘eagle eyed’ because there are a lot of infrastructure changes and people will need to keep watching out for them! If you like an in-person event, please take a look at SQLRelay and the Cloud OS CommunityRelay for news and technically-oriented sessions on SQL Server 2014, Windows Server 2012 R2 and Systems Center 2012 R2.
What’s the news here? On the October 31, 2013, notification went out to current Windows Azure SQL Reporting customers that the service will be discontinued in 12 months.  What was Windows Azure SQL Reporting? It was is a cloud-based reporting service for the Windows Azure Platform built on SQL Server Reporting Services technologies. As a big SSRS fan, who has presented on SSRS and Azure together, I was disappointed that the service was going. So what about the move to Windows Azure Virtual Machines for SSRS?
So, what’s the alternative? Welcome to SSRS on Windows Azure Virtual Machines

However, my disappointment was quickly dissipated when I realised that the Microsoft vision is users will have VMs with SSRS for their Azure reporting instances. My belief is that people will probably find it pretty easy to move their Windows Azure SQL Reporting solutions towards SSRS on Windows Azure VMs.
IT departments are used to VMs, and I see an increasing trend towards virtualisation amongst many of my customers already. Using a VM, you can deploy an operational reporting solution in the cloud that supports either the Native or SharePoint mode feature set.
Will I get the same features that I had before? Fortunately, a VM with SQL Server 2008 R2 or 2012 supports all Reporting Services features, including all supported data sources, customization and extensibility, and scheduled report execution and delivery. This means that users should see no change from their perspective, and that is a good thing.
What’s the benefits?

Performance – It was well known that Windows Azure SQL Reporting report execution was slower than SSRS running on premises. Moving to a VM makes sense, because performance of SSRS on a Windows Azure Virtual Machine corresponds to an on-premises SSRS instance. Faster reports is always good news! Side by side testing has shown that performance gains are attributed to having the report server catalog reside on a local disk in the VM.
What about my custom code? SSRS on an Azure VM supports custom code and assembly references in a report. Similarly, developers can replace or supplement report server operations by adding custom extensions. See Custom Code and Assembly References in Expressions and Reporting Services Extensions for details.
Mobility – this was my favourite feature of Windows Azure SQL Reporting but all is not lost with the new vision. If it is in the cloud, then you can look at mobilising the SSRS report from the VM as you did previously with SSRS as a service.
Scheduled report execution and delivery yes! See Schedules and Subscription and Delivery.
Integration with hybrid solutions – yes! You can join a Windows Azure VM to your corporate network. This is particularly useful for small to medium businesses who prefer an operational cost (OPEX) than a capital expenditure (CAPEX) cost. This means that SMEs can add capacity quickly, without making large hardware costs. You can get more information here Windows Azure Virtual Network Overview
Considering a new Reporting Solution on Windows Azure?
Here are some points to note:
A Windows Azure VM can use Windows authentication to support single sign on. The configuration depends on your setup and your requirements e.g. whether you require validation at the report server or the backend, for example. 
In order to help you to get started, take a look at the table below to help you evaluate a cloud-based Azure VM reporting solution for new software development projects:
Before you start, learn about the basic capabilities of a Windows Azure VM by watching the videos and clicking the Explore links on the Virtual Machine page on the web site.
Compare licensing costs between a predefined image and Windows Server VM running a licensed copy of SQL Server that you purchase and install separately on the VM. Depending on which SQL Server features you require, you might find it more cost-effective to purchase a Windows VM and SQL Server (Enterprise, Standard or Web edition) separately. In that case, you might want create a .vhd in-house using the installation media of the licensed copy of SQL Server, and then attach the disk to your Windows VM.
As alternative to SQL Reporting, you can use the Standard edition of SQL Server, but you might choose other editions depending on the feature requirements and workloads.
Choose the report server mode and features that best satisfy business requirements. The report server mode will determine which authentication subsystems and authorization models are available. While Native mode is closest to SQL Reporting, SharePoint mode provides out-of-box support for claims authentication, multi-tenancy, and load balancing.
Note that claims identity cannot be flowed to most backend data sources that exist outside of the SharePoint environment, so if you use claims, realize that stored credentials of a single user identity will most likely be required for backend data access.
Confirm your decisions about deployment, provisioning, report server mode, and features through proof-of-concept testing. Proof-of-concept testing includes building and publishing simple reports that allow you to validate connections from client applications so that you can test configuration, authentication, and authorization behaviors. During preliminary testing, retrieve enough data in each report to understand the expected latency for data retrieval and rendering, especially if you are testing a hybrid solution that combines cloud and on-premises services.
Finally, evaluation should include a review of high availability and scalable architectures that might be necessary to support a large volume of users or report executions.
Existing Projects using SQL Reporting on Windows Azure?

IT teams are accustomed to VMs, so this already leverages the skills in-house in order to make the transition. Here is some guidance below to help you to move existing SQL Reporting over to Azure VMs. Here are a few take-away points:
·         You will need to replace it with an alternative technology by October 2014.
·         Microsoft recommend a Windows Azure VM running SSRS in Native mode.
·         Choosing an SSRS VM preserves your existing investment in report design, so no real changes made to the reports themselves.
You are not charged for VMs that are turned off. This saves you money! If you only use reports at scheduled times, for example, month end reporting, you can export a report to a static format, such as PDF. You could then stop the VM when the report server is inactive.
How do you migrate to a VM? Simple! You can deploy a report server project to SSRS on a VM, setting the target server to the VM endpoint. For instructions on how to configure SSRS, set endpoints, configure the firewall, and publish and test reports, see SQL Server Business Intelligence in Windows Azure Virtual Machines.
Other aspects of a transition will require replacement functionality or manual changes, such as replacing report server authentication, or changes in how client applications connect to a report server. At a minimum, you will need to update the endpoint used on the connection. 
SSRS Native Mode on a VM versus SQL Reporting

SQL Reporting customers who are unfamiliar with SSRS can use the following table to compare the two platforms.
SSRS Native Mode on a Windows Azure VM
SQL Reporting
No feature restrictions for Reporting Services instances on a VM, except for features that vary by report server mode or by SQL Server edition. On a VM, reports can retrieve data from any supported data source. See Data Sources Supported by SSRS for details. For feature comparison by mode or edition, see Reporting Services Report Server and Features by Edition SQL Server 2012.
SQL Reporting is limited to un-federated Windows Azure SQL Databases that are part of the same Windows Azure subscription. On-demand report execution and rendering is supported, but scheduling and subscription delivery is not available.
Billing model
Billing is based on the compute resources required to support a VM in the data center.
Microsoft recommends Medium or Large VMs for SQL Server BI server applications, depending on report volume and number of SQL Server features you plan to use. For operational reporting, you will need both Reporting Services and a Database Engine instance for the report server database.
Different rates apply depending on the size of the VM, as VM size determines how much CPU, memory, and disk storage are allocated. See Pricing Details for SQL Server for more information.
Note that you are not charged for VMs that are turned off, so if you only use reports at certain times, you can export a report to a static format, such as PDF, and then stop the VM when the report server is inactive.
Billing is based on the number of report executions rather than compute resources. If additional capacity is required, an additional instance is added dynamically in the background. Your bill goes up incrementally, in response to the higher number of report executions.
Authentication and Authorization
Users can authenticate to SSRS on VM using Windows authentication or Forms authentication. Support for commonly used authentication subsystems allows for greater software integration opportunities and supports identity delegation across multiple applications.
For database platforms that support Windows authentication, you can take advantage of identity delegation to flow a user identity from the calling application, to the report server, to the backend database. See Authenticate to a report server and Microsoft BI Authentication and Identity Delegation for more information.
A report server on a VM uses a role-based authorization model. See Granting Permissions on a Native Mode Report Server.
SQL Reporting has a proprietary report server authentication subsystem, limited to defining report user identities used for sign in and role assignments. User identity cannot be deleted to other server applications.
SQL Reporting uses Native mode Reporting Services roles.
Software integration and architecture
Reporting Services is a middle tier service that sits between backend data sources and front-end clients, such as a browser or custom web page hosting a report. When evaluating Reporting Services on a VM as your operational reporting solution, your design should position Reporting Services as a middle tier service accordingly.
Architecturally and programmatically, a report server VM is equivalent to an on-premises server. Parity between cloud and on premises architecture is best achieved when other applications, such as backend data sources or front-end applications providing embedded reports, also run within the same Cloud service as the report server VM. In most cases, an end-to-end solution designed to run on-premises can be duplicated using a collection of VMs in a Cloud service. See Developer’s Guide (Reporting Services) for more information about SSRS programmability.
In SQL Reporting, report access is primarily through the HTTP endpoint for URL access, or the SOAP management endpoint, often using the ReportViewer control embedded in a form or web page.
Note that on SQL Reporting, the ASP.NET MVC Web Application templates do not support the ReportViewer control.
More information
·         Sign up for Windows Azure<!–[if mso & !supportInlineShapes & supportFields]> SHAPE  \* MERGEFORMAT <![endif]–><!–[if mso & !supportInlineShapes & supportFields]> <![endif]–>

 I hope that helps someone!
Kind Regards,

SSRS tidbit: quick and easy squares to serve as KPIs

Key Performance Indicators are extremely useful for helping to direct a business from an executive perspective. Alternatively, they can act as a quick ‘at a glance’ warning for operational reports too.

KPIs can often take the form of traffic lights, smiley faces and so on. However, what about if you want to go for the ‘minimalist’ approach whereby you just want to show a square, or a colour, or something very simple? This is easy to do in Reporting Services.

To show a square in a cell:

Using the Expression Editor, type in a 0 (ie a zero), highlight it, and then choose ‘wingdings’ as a font in the property settings. It will then come up as a square.

Once it is in the square format, you can do things to make the square convey more information.

For example, you could change the colour of the square, dependent on the value. For example, if you were using a sequential palette, you might want to increase the intensity of the colour dependent on the value. To do this, use the Expression Editor for the ‘font color’ setting and type in an expression similar to the following:

=SWITCH(Fields!RatingCount.Value < 2, “CornflowerBlue”, Fields!RatingCount.Value  “RoyalBlue”,

 Fields!RatingCount.Value >=3, “MediumBlue”

Remove the quotation marks

Similarly, if you want to change the size of the square, go to the ‘font size’ property setting and type in the following:

=SWITCH(Fields!RatingCount.Value < 2,

“6pt”, Fields!RatingCount.Value “8pt”, Fields!RatingCount.Value >=3,“10pt”)

In the 17th Century, Thomas Hobbes asserted he had succeeded in squaring the circle. Whilst this facility is interesting, it is important not to go overboard. Have fun with the settings, but not too much fun – you want to be sure that information is conveyed, not drowned out in the noise!

Mobile Business Intelligence Series: Part 1 – Mobilising Native SSRS

This is the first part in a series about implementing Mobile Business Intelligence. Interested readers may like to know that I’m presenting on this topic at a number of events, including SQLSaturday 162 in Cambridge, UK, and at SQLSaturday 170 in Munich, Germany. I also presented on this topic at SQLMidlands in July 2012, and also in SQLSaturday South Florida straight after TechEd North America 2012.

In this series, I will suggest different ways in which people can mobilise reports using a variety of technologies. If I am missing some from the list, please let me know! I should add that Copper Blue or myself are not affiliated or partnered with any of the organisations that  appear here. These are simply solutions to business problems that I’ve come across, and thought it might be useful to share here. 
One question is this: how can we mobilise standalone SSRS reports without SharePoint?  Here are some suggestions, which are dependent on your mobile device:

At the time of writing, Reporting Services isn’t viewable on an iPad in SharePoint 2010.  Issues that you might find include the following:

Native Mode Report Manager not fully functional
Some reported drop-down issues, for example, if you have more than 6 parameters. In this case, you might want to consider PowerPivot. I will look at this later on in the current series.
Vertical text does not render properly. In any case, are you sure that you need to use it?
Calendar control issues

How is it possible to view Reporting Services on an iPad? One option is to use MobiWeave, which displays SQL Server Reporting Services reports.  It’s possible to download an evaluation copy from the MobiWeave site. Here are the main features: 
  • Download and View Reporting Services Reports from multiple reporting servers
  • Supports SSRS in SQL Server 2005, 2008, 2008 R2, 2012
  • It also supports Azure SSRS in Native and Sharepoint modes with SSL
  • Parameters support – remember that this requires specific ways of interacting on the iPad
  • Interactive mode which supports Drill down and Drill through reports
  • Bookmarks and History will also work
As a Windows fan ( I can’t wait until I get my hands on a Surface!) I like Blue Granite Nitro. Blue Granite uses XML-formatted data to produce dashboards and reports. The XML data is generated using an application server, such as SQL Server Reporting Services. This tool is available from the Zune MarketPlace (how I love saying that!), and you can take a look here.

These solutions might be a good option for people who don’t have SharePoint but still want mobile business intelligence. 
Next up, we will look at the various options for mobile Business Intelligence in SharePoint 2010 and SharePoint 2013 Preview. I’m a SharePoint fan so it’s next up! I look forward to your comments.

SQL Server 2008 R2 Service Pack 2 (SP2) is Released!

Note the release of SQL Server 2008 R2 Service Pack 2 (SP2). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. 
This a free upgrade, so please head over to the Download Center for a look.
This particular pack is interesting because it contains a few key community Reporting Services requests

  • Reporting Services Charts Maybe Zoomed & Cropped
    Customers using Reporting Services on Windows 7 may occasionally find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.This is tackled in the Service Pack
  • Batch Containing Alter Table not Cached
    In certain situations with batch files containing the alter table command, the entire batch file is not cached.
  • Collapsing Cells or Rows, If Hidden Render Incorrectly
  • Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.