[MUSIC]
Hello, everyone.
I'm Christian Wade, and I'm a Senior Program Manager for
Analysis Services.
Today, we're gonna talk about SQL Server
vNext Business Intelligence Enhancements,
specifically focusing on analysis services.
Enterprise organizations today have data all over the place in various
formats and locations.
It's very difficult for business users to keep track of this.
Even for organizations who have invested in information architecture
and data warehouses, it's still very difficult for business users to
access that data and unlock that data for consistent decision making.
When pulling data from a data warehouse,
they require SQL query skills and intimate familiarity with the data.
This is where analysis services really shines in SQL Server vNext.
And their services allows the definition of a semantic model
covering large integrated data sets that thereby define the user
interface that users see when browsing that data through business
intelligence tools.
Like Power BI, Excel Pivot Tables and
various other third-party client tools.
Analysis Services also allows the abstraction of business rules for
reusability.
For example, the derivation of Key Performance Indicator metrics
to drive the business can be reused throughout
an Enterprise organization promoting consistent decisions and
various efficiencies around the management of data.
Analysis Services is a very scalable product.
We can fit vast amounts of data into memory compressed at high
compression ratios.
And thereby enabling super-fast queries and
interactive analysis at the speed of thought.
Analysis Services also integrates very well with IT processes like
DevOp processes and automated build and deployment to ensure
the reliability and predictability of mission critical systems.
In vNext,
Analysis Services is bringing in a lot of functionality from Power BI.
We are enabling a consistent modeling experience with Power BI.
And great Power BI reports on top of analysis services.
We're bringing in the connectivity stack from Power BI,
which gives us access to a range of exciting,
new data sources including big data and cloud-based data sources.
As well as the Power Query Formula Language, which allows us
to perform transformations and data cleansing operations,
whilst ingesting the data into analysis services.
And where historically we may have required a data mart
just to support and add services, we don't need that anymore.
On the modeling and analytics side, we're introducing Detail Rows,
which allows a business user to see the transactional records behind
an aggregated cell value.
And the modeler can customize that behavior.
We're introducing enhanced support for ragged hierarches so
the modeler can very easily present a very clean
version of ragged hierarches to business users.
Enhanced time intelligence, where we can have for example the calendar
and fiscal hierarches presented and surfaced in the appropriate
location in the Power BI filled list for a guided analytics experience.
And object level security that allows us to secure not
only the data but the metadata of the model as well.
In previous versions of Analysis Services, we could use row level
security to secure for example, social security numbers.
With vNext, we can even secure the fact that there is a column
somewhere in the model that is called social security numbers.
So we can secure the metadata in addition to the data.
And we've made various enhancements to the developer tools,
some of which I'll show you in the demo.
So let's get stuck into the demo.
So here we are in Visual Studio 2017.
And the fact that Analysis Services projects are hosted in Visual Studio
gives us access to source control integration and
develops processes like automated build and deployments.
Here in the Get Data dialogue, you can see that we now have access
to an extensive list of data sources.
In fact, this list includes some data sources that haven't
been released yet but
we intend to release them in the not-too-distant future.
So here we have some traditional data sources, cloud data sources,
web, OData Feeds, big data, Facebook,
SAP HANA, Salesforce, the list goes on.
And if I were to continue through this wizard, I'd be able to define
transformations that are performed when loading the model.
I'm now gonna switch to Excel and
if I'm a business user browsing this model.
And I'm interested in this particular cell.
Maybe there's a spike in sales that I'm interested in for
2014 in the UK.
The default behavior if I double-click this cell is that I see
all of the columns in the Internet sales table,
which may not be very meaningful for the user.
It would be better if the modeler can customize this column list.
In order to do so, we can write a DAX expression.
So here I am in SQL Server Management Studio,
we now have a new DAX query toolbar button.
And we can now author DAX queries with full intellisense,
which is great news.
So here I'm using this select columns function
which we anticipate will be commonly used with detail rows expressions.
So I'm using select columns over Internet Sales where I can specify
columns from internet Sales and columns from related tables.
And I can also provide column name aliases.
I'm gonna copy this expression.
And paste it into the Detail Rows Expression property.
And if I now switch back to the pivot table, and double-click
on the cell again, I now get a much more meaningful output for
the user, where the user can see the orders and the customers.
And you can see also that we're only viewing orders that took place in
2014 in cities in the UK.
So it automatically inherited the filter context for
the cell that I double-clicked on.
And in some cases, we may wanna take this further.
So I have another pivot table here, and
here we have the same measure and it's filtered by California.
So if I double-click on January 11th, I'm gonna get the same column
list, but now just for January 11th for cities in California.
However, for this cell, this is a different measure,
which is YTD Internet Total Sales, which is the running total.
So as you can see,
the sum is 41,558 which is the same value we have here.
And if I double-click on this cell, naturally I would wanna see the same
columns as we're in the same business domain of Internet Sales.
But I want to see all the orders that took place between
January 1st and January 11th.
I happen to have created the Detail Rows Expression ahead of time, so
here we have the same columns, and
again, with orders between January 1st and January 11th.
And lastly, here we have a ragged hierarchy which is presented in
a very clean way for the users.
And all I have to do as the modeler to enable that was
to set the property on the hierarchy object to hide
blank members for the Hide Members property.
So there we have it.
That's just a taster for the many new features that we have coming in
vNext to enhance the modeling experience to provide
access to a wide range of new data sources to
provide transformation capabilities upon ingesting of the data.
So thank you for watching.
Please download the community technology preview,
we'd love to get your feedback.
[MUSIC]