SPEAKER 1: The ability to create
a composite model
when using a Power BI dataset
or an Azure Analysis Services model is in
preview since December 2020.
However, many of you asked for
the ability to do the same
when using SQL Server Analysis Services
instead of Azure Analysis Services.
I'm excited to tell you that
with CTP2 for SQL Server 2022,
we will introduce this capability.
Once you install that CTP on the server that
hosts your Analysis Services model,
you can then create a composite model on top.
Of course, keep in mind
that this is not ready for production workloads,
as both the composite model feature related to this
and the SQL Server version required
have not reached general availability yet.
The way this works in private desktop, however,
is very similar to building a composite model
using a Power BI dataset
or an Azure Analysis Services model.
In this scenario,
I'm a data analyst
at AdventureWorks
and we offer two types of shipping to our users,
standard and express.
I want to check the average freight cost
for each of these types.
To check everything is in
the automated system is working as expected.
In this report,
I've marked the orders
with express or standard,
depending on how
our shipping partners said
they were handled.
As you can see, about 8,500 orders
were handled as express
and the vast majority
was handled as standard,
which is what I would expect.
Now, let's get access
to the corporate information
to see if the freight charges
for these match
our expectations as well.
I'll start by going to Get Data
and then I choose Analysis Services.
I'll enter the server name
that I want to connect to.
I'll leave the connection tied to DirectQuery
because that's what I want to do.
Now here I can obviously
select which tables I want to connect to.
I'll just select all of them and hit "Submit."
Now that we're connected
to that enterprise data model,
let's take a quick look at the model view
and add a relationship between
the two data types that I have here.
I want to add a relationship between
my sales order classification
and the sales order,
at the Internet sales order table.
I'll build the relationship on
the sales order number and click "Okay."
Great.
Now let's add a scatter plot
that will help me do
the analysis that I want.
I'll put in margin,
the unit price, freight.
I'll change it to the average
and I'll also put in the class
for the sales order,
which came from my other source.
Immediately, this jumps out to me
that as expected,
notice how the ones
that have an average higher freight
are all marked as express.
Makes sense, you pay more for express.
But there there's a couple of outliers here
and especially here,
there's a group of orders
that are marked as express
but are, well, on average
lower in freight.
One of the possible explanations
could be a promotion.
I know we run regular promotions
where we might offer free express
shipping to our customers.
Just to check if that is actually the case here,
let me add a quick table to just see
the sales order number and the promotion id.
Now if I select a group here,
I can see that all these orders
had a promotion id of 13.
That makes sense as this promotion
I know was indeed a free upgrade
from standard to express shipping.
Now we can easily see that
that caused the outlier in chart.
As you've seen in this demo,
I was able to successfully create a composite model
using data from my SQL Server Analysis Services model.
Again, as this feature is currently in preview
and the ability to connect
to SQL Server Analysis Services
requires a CTP version of SQL Server.
This is not ready for you to use
in production workloads.
However, it's an exciting improvement
and one we cannot wait
to make fully available soon.