Kategorie: Microsoft Azure

Creating your first data model in Azure Analysis Services

Azure Analysis Services is a new preview service in Microsoft Azure where you can host semantic data models. Users in your organization can then connect to your data models using tools like Excel, Power BI and many others to create reports and perform ad-hoc data analysis.

To understand the value of Azure Analysis Services, imagine a scenario where you have data stored in a large database. You want to make that data available to your business users or customers so they can do their own analysis and build their own reports. To do this, one option would be to give those users access to that database. Of course, this option has several drawbacks. The design of that database, including the names of tables and columns may not be easy for a user to understand. They would need to know which tables to query, how those tables should be joined, and other business logic that needs to be applied to get the correct results. They would also need to know a query language like SQL to even get started. Most often this will lead to multiple users reporting the same metrics but with different results.

With Azure Analysis Services, you can encapsulate all the information needed into a semantic model which can be more easily queried by those users in an easy drag-and-drop experience. And you can ensure that all users will see a single version of the truth. Some of the metadata included in the semantic model includes; relationships between tables, friendly table/column names, descriptions, display folders, calculations and row level security.

Once your data is properly modeled for your users to consume, Azure Analysis Services offers additional features to enhance their querying experience. The biggest of which is the option to put the data in an in memory columnar cache which can accelerate queries to sub second performance. This not only improves the query experience but by hitting the cache also reduces the query load on your underlying database.

Ready to give it a try? Follow the steps in the rest of this blog post and you’ll see how easy it is.

Before getting started, you’ll need:

Azure Subscription – Sign up for a free trial.

SQL Server Data Tools – Download the latest version for free.

Power BI Desktop – Download the latest version for free.

Create an Analysis Services server in Azure

1. Go to http://portal.azure.com.

2. In the Menu blade, click New.

3. Expand Intelligence + Analytics, and then click Analysis Services.

4. In the Analysis Services blade, enter the following and then click Create:

Server name: Type a unique name.
Subscription: Select your subscription.
Resource group: Select Create new, and then type a name for your new resource group.
Location: This is the Azure datacenter location that hosts the server. Choose a location nearest you.
Pricing tier: For our simple model, select D1. This is the smallest tier and great for getting started. The larger tiers are differentiated by how much cache and query processing units they have. Cache indicates how much data can be loaded into the cache after it has been compressed. Query processing units, or QPUs, are a sign of how many queries can be supported concurrently. Higher QPUs may mean better performance and allow for a higher concurrency of users.

Now that you’ve created a server, you can build your first model. In the next steps, you’ll use SQL Server Data Tools (SSDT) to create a data model and deploy it to your new server in Azure.

Create a sample data source

Before you can create a data model with SSDT, you’ll need a data source to connect to. Azure Analysis Services supports connecting to many different types of data sources both on-premises and in the cloud. For this post, we’ll use the Adventure Works sample database.

1. In Azure portal, in the Menu blade, click New.

2. Expand Databases, and then click SQL Database.

3. In the SQL Database blade, enter the following and then click Create:

Database name: Type a unique name.
Subscription: Select your subscription.
Resource group: Select the same resource group you created for your Analysis Services server.
Select source: Select Sample (Adventure Works LT).
Server: Choose a location nearest you.
Pricing tier: For your sample database, select B.
Collation: Leave the default, SQL_Latin1_General_CP1_CI_AS.

Now that you’ve created a sample data source, you’ll have some data to connect to when you build your data model.In the next steps, you’ll use SQL Server Data Tools (SSDT) to connect to your new data source, create a data model, and deploy it to your new server in Azure.

Create a data model

To create Analysis Services data models, you’ll use Visual Studio and an extension called SQL Server Data Tools (SSDT).

1. In SSDT, create a new Analysis Services Tabular Project.

If asked to select a workspace type, select Integrated.

2. Click the Import From Data Source icon on the toolbar at the top of the screen.

3. Select Microsoft SQL Azure as your data source type and click Next.

4. Fill in the connection information for the sample SQL Azure database created earlier and click Next.

Server Name: Name of SQL Azure server to connect to.
User Name: Name of the user which will be used to login to the server.
Password: Password for the account.
Database Name: Name of the SQL database to connect to.

Note: If using SQL Azure ensure that you have allowed your IP address access through the firewall. Also, ensure that “Allow access to Azure Services” is set to “on” for the firewall.

5. Select Service Account for the impersonation mode and click Next.

6. Select the tables you wish to import into cache and click Finish:

At this step, you can optionally provide a friendly name for each table. For large tables, which may not fit into cache, you can also specify a filter expression to reduce the number of rows. When complete, click next.
Data will now be read from the database and pulled into a local cache within Visual Studio.
Once loading is complete, you will have your first model created and will be able to see each table and the data within them. You can also switch to a diagram view by clicking the little diagram option at the bottom right of the screen:

The diagram view makes it really easy to see all of the tables and the relationships between them.

Improving the model

Now that your basic model is built, you could start querying it now or you could enhance it further by using more of the available modeling features. Some of these features include:

Create or edit relationships. You can add, remove or change relationships between tables by going to the diagram view and dragging a line between two columns in different tables. Once tables are joined together, they can automatically be queried together when a user selects columns from both tables.
Edit properties for a table or column. You can update multiple properties for tables and columns by clicking on them and updating the values in the properties pane.

Add more business logic to the model by creating calculations and measures.

Deploy

Once your model is complete, you can now deploy it to the Azure AS server which you created in the first step. This can be done with the following steps:

1. Copy your Azure Analysis Services server name for the Azure portal. This can be found at the top of the overview section of your server.

2. In the solution explorer in Visual Studio, right click on the project and click properties.

3. Change the deployment server to the name of your Azure AS server and click OK.

4. Right click the project name again, but this time click Deploy.

Connect

Now that you model has been creating you can connect with it through tools like the Power BI Desktop or Excel.

Power BI Desktop

If you don’t already have the Power BI Desktop, you can download it for free.

1. Open the Power BI Desktop

2. Click Get Data.

3. Select Databases/SQL Server Analysis Services and then click connect.

4. Enter your Azure AS server name and click OK.

5. On the Navigator screen, select your model and click OK.

You will now see your model displayed in the field list on the side. You can drag and drop the different fields on to your page to build out interactive visuals.

Excel

Learn more about connecting through Excel.

Learn more about Azure Analysis Services.
Quelle: Azure

Query Store ON is the new default for Azure SQL Database

We are happy to announce that Query Store is turned ON in all Azure SQL databases (including Elastic Pools) which will bring benefits both to the end users and the entire Azure SQL Database platform.

Why is this important?

Query Store acts as a “flight data recorder” for the database, continuously collecting critical information about the queries. It dramatically reduces resolution time in case of performance incidents, as pre-collected, relevant data is available when you need it, without delays.

You can use Query Store in scenarios when tracking performance and ensuring database performance predictability is critical. The following are some examples where Query Store is going to significantly improve your productivity:

Identifying and fixing application performance regressions (more details in this blog article)
Tuning the most expensive queries considering different consumption metrics (elapsed time, CPU time, used memory, read and write operations, log I/O, etc.)
Keeping performance stability with compatibility level 130 in Azure SQL Database (more details in this blog article)
Assessing impact of any application or configuration change (A/B testing)
Identifying and improving ad-hoc workloads (more details here)

Query Store also provides the foundation for performance monitoring and tuning features, such as the SQL Database Advisor. Query Store powers SQL Database Performance Insights which allows you to monitor and troubleshoot database performance directly from the Azure portal. With Query Store turned ON we ensure that relevant information about your most critical queries is available first time you open the queries chart on SQL Database Performance Insights:

We strongly recommend keeping Query Store ON. Thanks to an optimal default configuration and automatic retention policy, Query Store operates continuously using an insignificant part of the database space with a negligible performance overhead, typically in the range of 1-2%.

The default configuration is automatically applied by Azure SQL Database. If you want to switch to a customized Query Store configuration, use ALTER DATABASE with Query Store options. Also check out Best Practices with the Query Store to learn how to choose optimal parameter values.

Next steps

For more detailed information, check out the online documentation:

Query Store: A flight data recorder for your database
Query Store Usage Scenarios
Monitoring Performance by Using the Query Store

Quelle: Azure

Project Bletchley – New Blockchain partners come to Azure Marketplace

As promised, we continue to release early and release often in the blockchain space together with our partners.  I am pleased to be back to announce more great additions to our blockchain offering on Azure.

In just the last couple of weeks, we have added two new partners and solutions to the growing blockchain ecosystem in the Azure Marketplace.

Parity – Ethcore recently published their high performance, low footprint, reliable Ethereum blockchain client, Parity, on Azure.  This offering simplifies setting up a new Parity node in the cloud, with little configuration from the user.  In a matter of minutes, a user can have a single node, private Ethereum network up and running for development and test.  

Blockstack Core v14 – Blockstack is building a new decentralized web of server-less applications where users can control their own data.  Applications run locally and utilize user-specific data stores as their backend to maintain decentralization and control.  Users can seamlessly deploy Blockstack Core nodes on Microsoft Azure.  Blockstack Core nodes provide the core functionality of the Blockstack stack, processing data from a standard blockchain layer to construct a global view of security and ownership mappings.

In addition to our growing blockchain partner ecosystem, over the last few weeks, we have focused on functionality to improve deployment resiliency for our own consortium network blockchain solution releasing several updates that should improve overall deployment success rates.  More new features are already in the works, so stay tuned for updates!
Quelle: Azure

New features in Azure Application Insights Metrics Explorer

Over the past weeks, Application Insights Metrics Explorer introduced several new features that allow more options for visualizing metrics, and simplify chart configuration. This includes new percentage aggregation charts, pinning standard and custom grids to dashboards, ability to control over y-axis boundaries of the charts, and sorting basic and advanced configuration options in chart settings.

Percentage aggregation charts

In many cases the actual value of a segmented metric is not as important as how it compares to the other values in the group. For example, you might want to visualize the percentage of the failed vs. successful requests, where they would sum up to 100%, instead of looking at the raw counts. Or you might want to see the percentage number of the HTTP requests handled by each server instance. To make it possible, we introduced a new percentage aggregation type, allowing users to switch between “Average” and “Average (%)”, “Sum” and “Sum (%)”, etc. Below is a sample chart that is based on the percentage aggregation:

To configure percentage aggregation, you need to turn on grouping, and select percentage aggregation from the Aggregation dropdown selector of chart settings:

 

Hiding less commonly used advanced settings, unless the user really wants to see them

A common feedback that we caught from new Application Insights users is that there are too many options in the chart settings pane. Some of these options are quite advanced but critical for the experienced users. Mixing basic and advanced options resulted in frustration for the new users who could not identify the essential settings and thus could not configure the charts the way they wanted. What did we do? A new checkbox on top of chart configuration now allows filtering basic and advanced settings. New users are looking at a simpler view. As the user becomes more proficient with metrics explorer, he can add advanced settings by checking the box on top of the chart details dialog:

Pinning Metrics Explorer grids to Azure dashboards

In the past, you could pin metrics explorer charts to dashboards. Now in addition to pinning charts, you can also pin grids. This small but very useful icon lets you customize your dashboards with those metrics that are better represented in a grid format:

Ability to freeze lower and upper boundaries of the y-axis on the charts

Freezing y-axis of the charts becomes important when looking at smaller fluctuations of larger values. For example, when the volume of successful requests drops from 99.99% to 99.5%, it may represent a significant reduction in the quality of service. But from the charting perceptive, noticing a small numeric value fluctuation would be difficult or even impossible. With the new option, you can freeze the lowest boundary of the chart to 99%, which would make this small drop more apparent. Another example can be a fluctuation in the available memory, where the value will technically never reach 0, so fixing the range to a higher value may make the drops in available memory easier to spot. The two charts below duplicates the same metric but with and without fixed y-axis boundary:

To freeze the y-axis boundaries, you need to check advanced settings and specify the desired range under the Y-axis range section of the chart details dialog:

Quelle: Azure

Telemetry Platform Features in Azure Media Services

We are excited to announce new Azure Media Services (AMS) telemetry platform features, generally available through our new Telemetry API. Media Services telemetry allows you to monitor and measure the health of your services through a suite of telemetry data. Telemetry data is written to your Azure Storage account and can be processed and visualized using a wide array of data visualization tools.

Consuming Telemetry Data

This release includes telemetry metrics for Channel, Streaming Endpoint, and Archive entities. Telemetry data is written to an Azure Storage table in the storage account specified when configuring telemetry for your media services account. Telemetry data is stored in aggregate in a table, “TelemetryMetricsYYYYMMDD,” for each day’s data (where “YYYYMMDD” denotes the date timestamp).

Each table entry contains a set of common fields and a record with a set of entity-specific fields. The entry identifying fields include the following:

Property
Value
Example

PartitionKey
{Account ID}_{Entity ID}
e49bef329c29495f9b9570989682069d_64435281c50a4dd8ab7011cb0f4cdf66

RowKey
{Seconds to Midnight}_{Random Value}
01688_00199

Timestamp
The time at which the row entry was created
2016-09-09T22:43:42.241Z

Type
The type of the entity providing telemetry
Channel

Name
The name of the telemetry event
ChannelHeartbeat

ObservedTime
The time at which the event occurred
2016-09-09T22:42:36:924Z

ServiceID
Service ID
f70bd731-691d-41c6-8f2d-671d0bdc9c7e

Entity-specific Properties
{Record as defined by the event}
{Record}

The account ID is included in the partition key to simplify workflows where multiple media services accounts are writing data to the same storage account. The row key start with the number of seconds to midnight to allow top n style data queries within a partition (see the log tail table design pattern for more information). The observed timestamp is an approximate measure provided by the entity reporting telemetry.

Entity-Specific Telemetry

The data in each telemetry row represents an aggregation of telemetry events raised over an aggregation time window, listed below. Each entity pushes telemetry with the following frequencies:

Channels: Every 60 seconds
Streaming Endpoints: Every 30 seconds
Archive: Every 60 seconds

Below is the schema description for channels, streaming endpoints, and archive entities.

Channels

Property
Value
Example

TrackType
Type of track
video

TrackName
Name of the track
video

Bitrate
Expected bitrate of the track
785,000

IncomingBitrate
Incoming bitrate of the track
784,548

OverlapCount
Number of overlapping fragments received in ingest
0

DiscontinuityCount
Number of discontinuities detected in ingest
0

LastTimestamp
Last ingested data timestamp
1800488800

NonincreasingCount
Count of fragments discarded due to non-increasing timestamp
0

UnalignedKeyFrames
Boolean on whether we received fragments where key frames are not aligned
False

UnalignedPresentationTIme
Boolean on whether we received fragments where presentation time is not aligned
False

UnexpectedBitrate
Boolean on whether the IncomingBitrate and Bitrate differ by more than 50% or if IncomingBitrate for an audio or video track is less than 40 kbps if Bitrate is 0
False

Healthy
Boolean on whether OverlapCount, DiscontinuityCount, NonincreasingCount, UnalignedKeyFrames, UnalignedPresentationTime, and UnexpectedBitrate are all zero or false
True

CustomAttributes
Placeholder for custom attributes
 

Streaming Endpoints

Property
Value
Example

HostName
Hostname of the endpoint
builddemoserver.origin.mediaservices.windows.net

StatusCode
HTTP status code
200

ResultCode
HTTP result code detail
S_OK

RequestCount
Total requests received within the last aggregation window
3

BytesSent
Total bytes sent within the last aggregation window
2,987,358

ServerLatency
Average server latency including storage in milliseconds
130

E2ELatency
Average end-to-end latency in milliseconds
250

Archive

Property
Value
Example

ManifestName
Name of the manifest
asset-eb149703-ed0a-483c-91c4-e4066e72cce3/a0a5cfbf-71ec-4bd2-8c01-a92a2b38c9ba.ism

TrackName
Name of the track
audio_1

TrackType
Type of track
audio

Bitrate
Track bitrate
785,000

Healthy
Boolean on whether there were no discarded fragments or archive acquisition errors in storage
True

CustomAttributes
Placeholder for custom attributes
 

The schema above is designed to give good performance within the limits of Azure Table Storage:

Data is partitioned by account ID and service ID to allow telemetry from each service to be queried independently
Partitions contain the date to give a reasonable upper bound on the partition size
Row keys are in reverse time order to allow the most recent telemetry items to be queried for a given service

This should allow many of the common queries to be efficient:

Parallel, independent downloading of data for separate services
Retrieving all data for a given service in a date range
Retrieving the most recent data for a service

Visualizing Telemetry Data

Your Azure Storage account can export data to data visualization tools such as PowerBI, Application Insights, and AMS Live Monitoring Dashboard, among many others. Below is an example of how this data can be imported into and visualized directly with PowerBI.

First, select the telemetry tables for the days you are interested in visualizing. To import your data, select Microsoft Azure Table Storage from the Get Data menu. Enter your storage account credentials and select the tables to import. Next, by selecting Content.ObservedTime as the time axis and Average of Content.Healthy (casted to decimal representation) as the value, plot the health of your channels and archive entities as a line graph. To plot channel health and archive health separately, add a filter on Content.Name. This visualization illustrates entity health where a value of 1 represents perfectly healthy and a value of 0 represents perfectly unhealthy for the given time. Below are examples of the channel and archive health plotted over time.

To visualize the overall health of your services, collapse the time dimension by plotting the data as a pie chart. Selecting Content.Healthy as the legend and Count of Content.Healthy as the value, produces the visualization below.

We’re excited to share these telemetry features and hope they provide useful information about the health of your Azure media services.

Thanks,

Azure Media Services Streaming Team
Quelle: Azure