Part Archives - Business Intelligence Info

Tag Archives: Part

How to Create an Ubuntu PowerShell Development Environment – Part 3

May 31, 2020 BI News and Info

The series so far:

Over the last few years, Microsoft has made great strides in making their software products available on a wider range of platforms beyond Windows. Many of their products will now run on a variety of Linux distributions (often referred to as “distros”), as well as Apple’s macOS platform. This includes their database product, SQL Server.

One way in which Microsoft achieved cross-platform compatibility is through containers. If you aren’t familiar with containers, you can think of them as a stripped-down virtual machine. Only the components necessary to run the application, in this case, SQL Server, are included. The leading tool to manage containers is called Docker. Docker is an application that will allow you to download, create, start and stop, and run containers. If you want a more detailed explanation of containers, please see the article What is a Container on Docker’s website.

Assumptions

For this article, you should understand the concepts of a container, although no experience is required. See the article from Docker referenced in the previous section if you desire more enlightenment on containers. Additionally, this article assumes you are familiar with the SQL language, as well as some basics of PowerShell. Note that throughout this article, when referencing PowerShell, it’s referring to the PowerShell Core product.

The Platform

The previous articles, How to Create an Ubuntu PowerShell Development Environment Part 1 and Part 2, walked through the steps of creating a virtual machine for Linux development and learning. That VM is the basis for this article. All the code demos in this article were created and run in that specific virtual computer. For best results, you should first follow the steps in that article to create a VM. From there, you will be in a good place to follow along with this article. However, they have been tested on other variations of Ubuntu, CentOS, as well as on macOS.

In those articles, I showed not just the creation of the virtual machine, but the steps to install PowerShell and Visual Studio Code (VSCode), tools you will need in order to complete the demos in this article should you wish to follow along.

For the demo, I am assuming you have download the demo files and opened them in Visual Studio Code within the virtual machine, and are executing individual samples by highlighting the code sample and using the F8 key, or by right-clicking on the selected text and picking run.

The Demo

The code samples in this article are part of a bigger sample I provide on my GitHub site. You’ll find the entire project here. There is a zip file included that contains everything in one easy download, or you can look through GitHub and pick and choose the files you want. GitHub also displays Markdown correctly, so you may find it easier to view the project documentation via GitHub rather than in VSCode.

This article uses two specific files, located in the Demo folder: m11-cool-things-1-docker.ps1 and m11-install-docker.sh. While this article will extract the relevant pieces and explain them, you will find it helpful to review the entire script in order to understand the overall flow of the code.

The Beginning

The first thing the PowerShell script does is use the Set-Location cmdlet to set the current location to the folder where you extracted the demo code. This location should have the Demo, Notes, and Extras folders under it.

Next, make sure Docker is installed, and if not, install it. The command to do this is rather interesting.

bash ./Demo/m11-install-docker.sh

bash is very similar to PowerShell; it is both a terminal and a scripting language. It is native to many Linux distros, including the Ubuntu-based ones. This code uses PowerShell to start a bash session and then executes the bash script m11-install-docker.sh. When the script finishes executing, the bash session ends.

Take a look inside that bash script.

if [ -x “$ (command -v docker)“ ]; then

echo “Docker is already installed“

else

echo “Installing Docker“

sudo snap install docker

The first line attempts to run a command that will complete successfully if Docker is installed. If so, it simply displays that information to the screen via the echo command.

If Docker is not installed, then the script will attempt to install Docker using the snap utility. Snap is a package manager introduced in the Ubuntu line of distros; other distros use a manager known as flat packs. On macOS, brew is the package manager of choice. This is one part of the demo you may need to alter depending on your distro. See the documentation for your specific Linux install for more details.

Of course, there are other ways to install Docker. The point of these few lines was to demonstrate how easy it is to run bash scripts from inside your PowerShell script.

Pulling Your Image

A Docker image is like an ISO. Just as you would use an ISO image to create a virtual machine, a Docker image file can be used to generate one or more containers. Docker has a vast library of images, built by itself and by many companies, such as Microsoft. These images are available to download and use in your own environments.

For this demo, you are going to pull the image for SQL Server 2017 using the following command.

sudo docker pull mcr.microsoft.com/mssql/server:2017-latest

The sudo command executes the following docker program with administrative privileges. Docker, as stated earlier, is the application which manages the containers. Then you give the instruction to Docker, pull. Pull is the directive to download a container from Docker’s repositories.

The final piece is the image to pull. The first part, mcr.microsoft.com, indicates this image is stored in the Microsoft area of the Docker repositories. As you might guess, mssql indicates the subfolders containing SQL Server images, and server:2017-latest indicates the version of SQL Server to pull, 2017. The -latest indicates this should be the most currently patched version; however, it is possible to specify a specific version.

Once downloaded, it is a good idea to query your local image cache to ensure the download was successful. You can do so using this simple command.

sudo docker image ls

image tells Docker you want to work with images, and ls is a simple listing command, similar to using ls to list files in the bash shell.

word image 36 How to Create an Ubuntu PowerShell Development Environment – Part 3

Running the Container

Now that the image is in place, you need to create a container to run the SQL Server. Unlike traditional SQL Server configuration, this turns out to be quite simple. The following command is used to not only create the container but run it. Note the backslash at the end of each line is the line continuation character for bash, the interpreter that will run this command (even though you’re in PowerShell). You could also choose to remove the backslashes and just type the command all on one line.

sudo docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=passW0rd!’ -p 1433:1433 –name arcanesql -d mcr.microsoft.com/mssql/server:2017-latest

The first part of the line starts by passing the run command into Docker, telling it to create and run a new container. In the first -e parameter you are accepting the end user license agreement. In the second -e parameter, you create the SA (system administrator) password. As you can see, I’ve used a rather simple password, you should definitely use something much more secure.

Next, we need to map a port number for the container using the -p parameter. The first port number will be used to listen on the local computer, the second port number is used in the container. SQL Server listens on port 1433 by default, so we’ll use that for both parts of the mapping.

The next parameter, --name, provides the name for the container; here I’m calling it arcanesql.

In the final parameter, -d, you need to indicate what image file should be used to generate the container. As you can see, the command is using the SQL Server image downloaded in the previous step.

word image 37 How to Create an Ubuntu PowerShell Development Environment – Part 3

You can verify the container is indeed running using the following command.

sudo docker container ls

As with the other commands, the third parameter indicates what type of Docker object to work with, here containers. Like with image, the ls will produce a list of running containers.

word image 38 How to Create an Ubuntu PowerShell Development Environment – Part 3

Installing the SQL Server Module

Now that SQL Server is up and running, it’s time to start interacting with it from PowerShell Core. First, though, install the PowerShell Core SQL Server module.

Install-Module SqlServer

It won’t hurt to run this if the SQL Server module is already installed. If it is PowerShell will simply provide a warning message to that effect.

If you’ve already installed it, and simply want to make sure it is up to date, you can use the cmdlet to update an already installed module.

Update-Module SqlServer

Do note that normally you would not want to include these in every script you write. You would just need to ensure the computer you are running on has the SQL Server module installed, and that you update it on a regular basis, testing your scripts of course after an update. (For more about testing PowerShell code, see my three-part article on Pester, the PowerShell testing framework, beginning with Introduction to Testing Your PowerShell Code with Pester here on SimpleTalk.)

Running Your First Query

The first query will be very simple; it will return a listing of all tables in the master database so you can see how easy it is to interact with SQL Server and learn the basic set of parameters.

The basic cmdlet to work with SQL Server is Invoke-SqlCmd. It requires a set of parameters, so you’ll place those in variables for easy reference.

$ serverName = ‘localhost,1433′

$ dbName = ‘master’

$ userName = ‘sa’

$ pw = ‘passW0rd!’

$ queryTimeout = 50000

$ sql = ‘SELECT * FROM master.INFORMATION_SCHEMA.Tables’

For this exercise, you are running the Docker container on the same computer as your PowerShell session, so you can just use localhost for the server name. Obviously, you’ll replace this with the name of your server when working in other environments. Note that you must append the port number after the server name.

Next, you have the database name you’ll be working with, and for this example, it will be master.

The next two parameters are the username and password. In a real-world environment, you’d be setting up real usernames and passwords, but this demo will be simple and just use the SA (system administrator) account built into SQL Server. The password is the same one used when you created and ran the container using the docker run command.

Next up is the query timeout. How long do should PowerShell wait before realizing no one is answering and give up? The timeout is measured in terms of seconds.

The last parameter is the query to run. Here you are running a simple SELECT statement to list the tables in the master database.

Now that the parameters are established in variables, you are ready to call the Invoke-SqlCmd cmdlet to run the query.

Invoke-Sqlcmd -Query $ sql `

-ServerInstance $ serverName `

-Database $ dbName `

-Username $ userName `

-Password $ pw `

-QueryTimeout $ queryTimeout

Here you pass in the variables to each named parameter. Note the backtick symbol at the end of each line except the last. This is the line continuation character; it allows you to spread out lengthy commands across multiple lines to make them easier to read.

In the output, you see a list of each table in the master database.

How to Create an Ubuntu PowerShell Development Environment – Part 3

Splatting

As you can see, Invoke-SqlCmd has a fairly lengthy parameter set. It will get tiresome to have to repeat this over and over each time you call Invoke-SqlCmd, especially as the bulk of these will not change between calls.

To handle this, PowerShell includes a technique called splatting. With splatting, you create a hash table, using the names of the parameters for the hash table keys, and the values for each parameter as the hash table values.

$ sqlParams = @{ “ServerInstance” = $ serverName

“Database” = $ dbName

“Username” = $ userName

“Password” = $ pw

“QueryTimeout” = $ queryTimeout

}

If you look at the syntax in the previous code example, you’ll see that the key values on the left of the hash table above match the parameter names. For this example, load the values from the variables you created, but you could also have hardcoded the values.

So how do you use splatting when calling a cmdlet? Well, that’s pretty simple. In this next example, you’ll load the $ sql variable with a query to create a new database named TeenyTinyDB, and then execute the Invoke-SqlCmd.

$ sql = ‘CREATE DATABASE TeenyTinyDB’

Invoke-Sqlcmd -Query $ sql @sqlParams

Here you call Invoke-SqlCmd, then pass in the query as a named parameter. After that, you pass in the hash table variable sqlParams, but with an important distinction. To make splatting work, you use the @ symbol instead of the normal $ for a variable. When PowerShell sees the @ symbol, it knows to deconstruct the hash table and use the key/values as named parameters and their corresponding values.

There are two things to note. I could have included the $ sql as another value in the hash table. It would have looked like “Query” = $ sql (or the actual query as a hard-coded value). In the demo, I made them separate to demonstrate that it is possible to mix named parameters with splatting. On a personal note, I also think it makes the code cleaner if the values that change on each call are passed as named parameters and the values that remain fairly static to become part of the splat.

Second, the technique of splatting applies to all cmdlets in PowerShell, not just Invoke-SqlCmd. Feel free to implement this technique in your own projects.

When you execute the command, you don’t get anything in return. On the SQL Server, the new database was created, but because you didn’t request anything back, PowerShell simply returns to the command line.

Creating Tables

For the next task, create a table to store the names and URLs of some favorite YouTube channels. Because you’ll be working with the new TeenyTinyDB instead of master, you will need to update the Database key/value pair in the hash table.

$ dbName = ‘TeenyTinyDB’

$ sqlParams[“Database”] = $ dbName

Technically I could have assigned the database name without the need for the $ dbName variable. However, I often find myself using these values in other places, such as an informational message. Perhaps a Write-Debug “Populating $ dbName” message in my code. Placing items like the database name in a variable makes these tasks easy.

With the database value updated, you can now craft a SQL statement to create a table then execute the command by once again using Invoke-SqlCmd.

$ sql = @’

CREATE TABLE [dbo].[FavoriteYouTubers]

(

[FYTID] INT NOT NULL PRIMARY KEY

, [YouTubeName] NVARCHAR(200) NOT NULL

, [YouTubeURL] NVARCHAR(1000) NOT NULL

)

‘@

Invoke-Sqlcmd -Query $ sql @sqlParams

In this script, you take advantage of PowerShell’s here string capability to spread the create statement over multiple lines. If you are not familiar with here strings, it is the ability to assign a multi-line string to a variable. To start a here string, you declare the variable then make @ followed by a quotation mark, either single quote or double quote, the last thing on the line. Do note it has to be last; you cannot have anything after it such as a comment.

The next one or more lines are what you want the variable to contain. As you can see, here strings make it easy to paste in SQL statements of all types.

To close out a here string, simply put the closing quotation mark followed by the @ sign in the first two positions of a line. This has to be in the first two characters if you attempt to indent the here string won’t work.

With the here string setup, call Invoke-SqlCmd to create the table. As with the previous statement, it doesn’t produce any output, and it simply returns us to the command line.

Loading Data

In this example, create a variable with a SQL query to load multiple rows via an INSERT statement and execute it.

$ sql = @’

INSERT INTO [dbo].[FavoriteYouTubers]

([FYTID], [YouTubeName], [YouTubeURL])

VALUES

(1, ‘AnnaKatMeow’, ‘https://www.youtube.com/channel/UCmErtDPkJe3cjPPhOw6wPGw’)

, (2, ‘AdultsOnlyMinecraft’, ‘https://www.youtube.com/user/AdultsOnlyMinecraft’)

, (3, ‘Arcane Training and Consulting’, ‘https://www.youtube.com/channel/UCTH58i-Gl1bZeATOeC4f25g’)

, (4, ‘Arcane Tube’, ‘https://www.youtube.com/channel/UCkR0kwYjQ_gngZ8jE3ki7xw’)

, (5, ‘PowerShell Virtual Chapter’, ‘https://www.youtube.com/channel/UCFX97evt_7Akx_R9ovfiSwQ’)

‘@

Invoke-Sqlcmd -Query $ sql @sqlParams

For simplicity, I’ve used a single statement. There are, in fact, many options you could employ. Reading data from a file in a foreach loop and inserting rows as needed, for example.

Like the previous statements, nothing is returned after the query executes, and you are returned to the command prompt.

Reading Data

People are funny. They love putting their data into databases. But then they actually expect to get it back! Pesky humans.

Fortunately, PowerShell makes it easy to return data from SQL Server. Follow the same pattern as before–set up a query and store it in a variable, then use Invoke-SqlCmd to execute it.

$ sql = @’

SELECT [FYTID]

, [YouTubeName]

, [YouTubeURL]

FROM dbo.FavoriteYouTubers

‘@

Invoke-Sqlcmd -Query $ sql @sqlParams

Unlike the previous queries, this actually generates output.

How to Create an Ubuntu PowerShell Development Environment – Part 3

Here you can see each row of data, and the values for each column. I want to be very precise about what PowerShell returns.

This is a collection of data row objects. Each data row has properties and methods. The sqlserver module converts each column into a property of the data row object.

The majority of the time, you will want to work with the data returned to PowerShell, not just display it to the screen. To do so, first assign the output of Invoke-SqlCmd to a variable.

$ data = Invoke-Sqlcmd -Query $ sql @sqlParams

If you want to see the contents of the variable, simply run just the variable name.

This will display the contents of the collection variable $ data, displaying each row object, and the properties for each row.

How to Create an Ubuntu PowerShell Development Environment – Part 3

You can also iterate over the $ data collection, here’s a simple example.

foreach($ rowObject in $ data)

{

“$ ($ rowObject.YouTubeName) is a favorite YouTuber!”

}

This sample produces the following output:

How to Create an Ubuntu PowerShell Development Environment – Part 3

In this code, I just display a formatted text string, but you could do anything you want to with it, such as writing to an output file.

Cleanup

When I was a kid, mom always taught me to put my toys away. There are many reasons why you would want to remove containers you are no longer using. Testing is one, and you may wish to write a script to spin up a new container, load it with data, then let the testers do their thing. When done, you may wish to stop the container or delete it altogether.

Stopping and Starting Containers

For a first step, use Docker to see what containers are currently running.

sudo docker container ls

The output shows that on the system only one container is running.

How to Create an Ubuntu PowerShell Development Environment – Part 3

Take the scenario of wanting to shut down the container, but not removing it. Perhaps you want to turn it off when the testers aren’t using it to save money and resources. To do this, simply use the stop command.

After issuing a stop, you should do another listing to ensure it is, in fact, stopped. You might think you could do another container ls, but note I said it lists currently running containers. If you want to see all containers, running or not, you must use a slightly different Docker command.

sudo docker stop arcanesql

sudo docker ps -a

The stop command will stop the container with the name passed in. The ps -a command will list all containers running or not.

How to Create an Ubuntu PowerShell Development Environment – Part 3

If you look at the STATUS column, on the very right side of the output, you’ll see the word Exited, along with how long in the past it exited. This is the indicator the container is stopped.

In this example, say it is the next morning. The testers are ready to get to work, so start the container back up.

sudo docker container start arcanesql

All that is needed is to issue the start command, specifying container, and provide the name of the container to start.

How to Create an Ubuntu PowerShell Development Environment – Part 3

As you can see, the status column now begins with Up and indicates the length of time this container has been running.

Deleting a Container

At some point, you will be done with a container. Perhaps testing is completed, or you want to recreate the container, resetting it for the next round of testing.

Removing a container is even easier than creating it. First, you’ll need to reissue the stop command, then follow it with the Docker command to remove (rm) the named container.

sudo docker stop arcanesql

sudo docker rm arcanesql

If you want to be conservative with you keystrokes, you can do this with a single command.

sudo docker rm –force arcanesql

The –force switch will make Docker stop the container if it is still running, then remove it.

You can verify it is gone by running or Docker listing command.

sudo docker ps -a

word image 39 How to Create an Ubuntu PowerShell Development Environment – Part 3

As you can see, nothing is returned. Of course, if you had other containers, they would be listed, but the arcanesql container would be gone.

Removing the Image

Removing the container does not remove the image the container was based on. Keeping the image can be useful for when you are ready to spin up a new container based on the image. Re-run the Docker listing command to see what images are on the system.

sudo docker image ls

The output shows the image downloaded earlier in this article.

word image 40 How to Create an Ubuntu PowerShell Development Environment – Part 3

As you can see, 1.3 GB is quite a bit of space to take up. In addition, you can see that the image was created 2 months ago. Perhaps a new one has come out, and you want to update to the latest—all valid reasons for removing the image.

To do so, use a similar pattern as the one for the container. You’ll again use rm, but specify it is an image to remove and specify the exact name of the image.

sudo docker image rm mcr.microsoft.com/mssql/server:2017-latest

When you do so, Docker will show us what it is deleting.

word image 41 How to Create an Ubuntu PowerShell Development Environment – Part 3

With that done, you can run another image listing using the image ls command to verify it is gone.

word image 42 How to Create an Ubuntu PowerShell Development Environment – Part 3

The image no longer appears. Of course, if you had other images you had downloaded, they would appear here, but the one for the latest version of SQL Server would be absent.

Conclusion

In this article, you saw how to use Docker, from within PowerShell Core, to download an image holding SQL Server 2017. You then created a container from that image.

For the next step, you installed the PowerShell SqlServer module, ran some queries to create a table and populate it with data. You then read the data back out of the database so you could work with it. Along the way, you learned the valuable concept of splatting.

Once finishing the work, you learned how to start and stop a container as well as remove it and the image on which it was based.

This article just scratched the service of what you can do when you combine Docker, SQL Server, and PowerShell Core. As you continue to learn, you’ll find even more ways to combine PowerShell Core, SQL Server and Docker.

Let’s block ads! (Why?)

SQL – Simple Talk

Heaps in SQL Server: Part 1 The Basics

May 23, 2020 BI News and Info

The series so far:

Heaps in SQL Server: Part 1 The Basics

This article is the beginning of a series of articles about Heaps in Microsoft SQL Server. Heaps are rejected by many database developers using Microsoft SQL Server. The concerns about Heaps are even fuelled by Microsoft itself by generally recommending the use of clustered indexes for every table. Globally renowned SQL Server experts also generally advise that tables in Microsoft SQL Server be provided with a clustered index.

Again, and again, I try to convince developers that a heap can even have advantages. I have discussed many pros and cons with these people and would now like to break a “PRO HEAP” lance. This article deals with the basics. Important system objects that play a major role in Heaps are only superficially presented in this article and described in detail in a follow up article.

The Basics of Heaps

Heaps are avoided in Microsoft SQL Server as the devil avoids holy water. One reason for the rejection is that many blog articles by renowned SQL Server Experts indicate that a table should use a clustered index. This article is the beginning of a series of articles that focuses on the broadest possible scope of application pro or con Heaps. The focus is on the heap to decide for yourself whether and when a heap has advantages or disadvantages compared to a clustered index. A sensible decision requires that you understand the way of working and the internal structures.

What are Heaps

Heaps are tables without a clustered index. Without an index, no sorting of the data is guaranteed. Data is stored in the table where there is space without a predetermined order. If the table is empty, data records are entered in the table in the order of the INSERT commands.

CREATE TABLE dbo.Customers

(

Id INT NOT NULL,

Name VARCHAR (200) NOT NULL,

Street VARCHAR (200) NOT NULL,

Code CHAR (3) NOT NULL,

ZIP VARCHAR (5) NOT NULL,

City VARCHAR (200) NOT NULL,

State VARCHAR (200) NOT NULL

)

INSERT INTO dbo.Customers(Id, Name, Street, Code, Zip, City, State)

VALUES(1,‘John Smith’,‘Times Square’,‘123’,‘10001’,

‘New York’,‘New York’);

The script creates a new table for storing customer data and inserts one row. Since neither an index nor a primary key is used with the CLUSTERED option, data will be stored “unsorted” in this table.

If a table does not have a clustered index, the heap can be seen in the system view [sys]. [Indexes] which will always have the [Index_Id] = 0.

– A Heap has always the index_id = 0

SELECT object_id,

name,

index_id,

type_desc

FROM sys.indexes

WHERE object_id = OBJECT_ID (N‘dbo.Customers’, N‘U’);

word image 20 Heaps in SQL Server: Part 1 The Basics

Figure 1: [index_id] = 0 or [index_id] = 1 is reserved for Heaps OR Clustered Indexes

The Structure of Heaps

Since Heaps are primitive data stores, no complex structures are required to manage Heaps. Heaps have one row in sys.partitions, with [index_id] = 0 for each partition used by the Heap. When a Heap has multiple partitions, each partition has a Heap structure that contains the data for that specific partition.

Depending on the data types in the Heap, each Heap structure will have one or more allocation units to store and manage the data for a specific partition. At a minimum, each Heap will have one IN_ROW_DATA allocation unit per partition.

word image 21 Heaps in SQL Server: Part 1 The Basics

Figure 2: Storage concept of Heaps / Clustered Indexes / Nonclustered Indexes

Index Allocation Map

Each table and index use IAM structures (Index Allocation Map) to manage the data pages. An IAM page contains information about blocks (extents) that are used by a table or index per allocation unit.

Data pages of a Heap do not store references to next or previous data pages (links). This is not necessary for Heaps because a Heap does not require data to be sorted according to defined criteria.

SELECT SIAU.type_desc,

SIAU.total_pages,

SIAU.used_pages,

SIAU.data_pages,

SIAU.first_iam_page,

sys.fn_PhysLocFormatter(SIAU.first_iam_page) AS iam_page

FROM sys.system_internals_allocation_units AS SIAU

INNER JOIN sys.partitions AS P

SIAU.container_id =

CASE WHEN SIAU.type IN (1, 3)

THEN P.hobt_id

ELSE P.partition_id

END

WHERE P.object_id = OBJECT_ID (N‘dbo.Customers’, N‘U’);

The column [first_iam_page] in the [sys].[system_internals_allocation_units] system view points to the first IAM page in the chain of IAM pages that manage the allocated data pages of a Heap in a specific partition. Don’t worry about the mystic hex code; it can easily be deciphered with the function sys.fn_PhysLocFormatter!

word image 22 Heaps in SQL Server: Part 1 The Basics

Figure 3: Information about page allocations and first IAM page

The above query returns information about the storage type, number of pages and the location of the first IAM-page which manages the data pages of the Heap. Microsoft SQL Server only needs the first IAM page because it holds a reference to the next IAM and so on.

To have a look into the secrets of an IAM Page, you can use DBCC PAGE but be careful about using undocumented functions in a production system.

– route the output of DBCC PAGE to the client

DBCC TRACEON (3604);

– Show the content of a data page

DBCC PAGE (0, 1, 188, 3);

word image 23 Heaps in SQL Server: Part 1 The Basics

Figure 4: A deeper look into the content of an IAM-page

The above depiction shows the stored information in the IAM page of our Heap. It says that pages 32,680 – 32,967 are allocated by the table [dbo].[Customers]. So now Microsoft SQL Server knows what data pages hold data when running a SELECT statement against the Heap.

Page Free Space (PFS)

The fill level of a data page can only be specified for data pages of a Heap. Unlike a clustered index, the data rows are not sorted and do not have to be entered in a sorted form. It is up to Microsoft SQL Server to decide on which data page a data record is saved.

However, to assess where a record can be saved, Microsoft SQL Server needs to know where there is enough space on allocated data pages to complete the transaction. This information is retrieved via the recorded fill level of the data page. The information is held in the PFS page.

The problem is that this degree of filling is not saved “exactly”. Rather, Microsoft SQL Server only uses “rough” percentages to indicate the degree of filling.

word image 24 Heaps in SQL Server: Part 1 The Basics

The next higher fill level is updated as soon as the state is exceeded. For example, a data page is ALWAYS 50% full as soon as the first data record is entered.

The current fill level of data pages of a heap can be determined with the – undocumented – system function [sys]. [dm_db_database_page_allocations].

The next example shows how the fill level changes when the state (bytes) is exceeded. To do this, a Heap is created that stores 2,004 bytes per data record.

DROP TABLE IF EXISTS dbo.demo_table;

CREATE TABLE dbo.demo_table

(

C1 INT NOT NULL IDENTITY (1, 1),

C2 CHAR(2000) NOT NULL DEFAULT (‘Test’)

);

INSERT INTO dbo.demo_table DEFAULT VALUES;

After the table has been created, insert one record into the table. Although the data page is filled with only 25%, Microsoft SQL Server records the filling status of the page with 50%.

– What pages have been allocated?

SELECT allocated_page_page_id,

previous_page_page_id,

next_page_page_id,

page_type_desc,

page_free_space_percent

FROM sys.dm_db_database_page_allocations

(

DB_ID(),

OBJECT_ID(N‘dbo.demo_table’, N‘U’),

NULL,

N‘DETAILED’

);

word image 25 Heaps in SQL Server: Part 1 The Basics

Figure 5: The page_free_space_percent

When you insert a second row the fill level won’t change since 2 * 2011 bytes = 4,022 bytes do not reach 50%. The fill level only needs to be updated for the third data record!

– Insert a second row into the demo table

INSERT INTO dbo.demo_table DEFAULT VALUES;

NOTE: Each data record has a record header that describes the structure of the data record. The structure is stored in 7 bytes. The physical length of a sample data record is therefore not 2,004 bytes but 2,011 bytes. To learn more about the anatomy of a record, read this article by Paul Randal.

Conclusion

This article described the internals of Heaps, but you may still be wondering if there are any advantages to using them. Here is a summary of the advantages and disadvantages which I’ll write about in future articles:

Advantages of Heaps

Using a heap can be more efficient than a table with a clustered index. In general, there are some use cases for Heaps like loading staging tables or storing protocol data into a Heap, since there is no need to pay attention to sorting when saving data. Data records are saved on the next possible data page on which there is sufficient space. Furthermore, the INSERT process does not require moving down the B-Tree of an index structure to the data page to save the record!

Disadvantages of Heaps

Heaps can have several disadvantages:

A Heap cannot scale if the database design is unsuitable because of PFS contention (will be handled in the next articles in detail!)

You cannot efficiently search for data in a Heap.

The time to search for data in a Heap increases linearly with the volume of data.

A Heap is unsuitable for frequent data updates because of the risk of forwarded records (will be handled in the next articles in detail)

A Heap is horrible for every database administrator when it comes to maintenance because a Heap requires an update of nonclustered indexes when the Heap is rebuilt.

Some of the “disadvantages” mentioned above can be eliminated or bypassed if you know how a heap “ticks” internally. I hope I can convince one or the other that a clustered index is not always the better choice. How to optimize the handling of Heaps will be described in future articles, so stay tuned!

Let’s block ads! (Why?)

SQL – Simple Talk

The Impact of Intense Disruption On Business Models: Strategic Alternatives (Part 2)

May 17, 2020 SAP

The Impact of Intense Disruption On Business Models: Strategic Alternatives (Part 2)

Nitin Singh

Part 2 of a three-part series

Once you understand the key parameters that impact your business model, it is time to dive deep into how to leverage existing capabilities to navigate through the new economic scenario. Additionally, if the capability does not exist or exists only partially, what should you do to resolve those gaps? Let’s analyze the typical business capabilities required to run a consumer products company like XYZ, using the capability model in the following figure.

Figure 3 1024x279 The Impact of Intense Disruption On Business Models: Strategic Alternatives (Part 2)

It is important to identify the key capabilities that should be augmented to handle this situation. It is also imperative to understand the business capabilities that are negatively impacted. Based on the typical challenges highlighted in the previous article, the following capability heatmap was drawn that highlights the key capabilities that were impacted.

Figure 4 1024x279 The Impact of Intense Disruption On Business Models: Strategic Alternatives (Part 2)

This provides a high-level view of which capabilities XYZ should augment. Different approaches could be leveraged, such as partnerships, build capacity, outsourcing, etc. For example, to run a business remotely, there might be a need to license or rent remote application tools for effective working.

Now, let’s look at which capabilities should be augmented to resolve issues of supply-chain disruption, as discussed in the previous blog. We realized that identifying alternate sources of supply and developing those suppliers quickly could be one such solution for XYZ. Now with the help of the capability model, let’s look at the related capabilities that XYZ needs to build to make the best use of alternate sources of supplies. Please refer to the previous figure, where you can see three key capabilities highlighted with red arrows.

Sourcing: Identifying alternate sources of supply requires a robust strategic sourcing process that helps in reaching out to alternate suppliers quickly and qualifying them according to product-quality standards. One of the sources for potential suppliers could be the B2B business networks that already have registered and qualified suppliers. In addition, the ability to integrate XYZ’s systems with those networks would help XYZ place requests for quotes electronically and make this process real-time.

Commodity management: Commodity or category management is also an essential capability to make sure XYZ can quickly reimagine its procurement strategy to leverage the discounts and/or tap into the best sources of supply. This includes analyzing the strategy for the short, mid, and long term. Some categories would show exponential growth, while other categories might see a dip in demand, and this is true for many of the large fast-moving consumer goods (FMCG) companies whose portfolio includes many different SKUs. A structured commodity strategy also includes price-negotiation guidelines and identifying subsequent procurement processes. So, this capability certainly is an important one to make sure XYZ can tap into alternate sources of supply for critical products.

Available to promise and allocations: Once XYZ has developed or augmented capabilities for sourcing and commodity management, it is essential to update its available-to-promise (ATP) logic by taking the supplier capacity into consideration to ensure an accurate order-delivery timeline. ATP logic should also be able to consider incoming shipments for the new supplier so that XYZ can promise its customers accordingly. If XYZ can connect to the business network of the newly onboarded supplier, it would make it much easier to get timely advanced ship notices, which can help make accurate predictions on promise dates to XYZ’s customers. In addition, XYZ can connect with retailers to identify key commodities that have rapidly shifting demand patterns (e.g., cleaning supplies, toilet paper, sanitizers, etc.).

In this post, we examined analyzing the impact of uncertain events by leveraging a capability heat map. In the next and final part, we will explore how to restructure your portfolio strategy to mitigate risks to your business from unforeseen events.

Join us in our exclusive webinar session on May 19 to get an overview of the SAP Readiness Check tool, which gives a first analysis of your existing SAP ERP application, highlighting key project activities.

About Nitin Singh

Nitin Singh works with SAP’s strategic customers as Principal Enterprise Architect to orchestrate the complex transformation programs. He also leads the Practice for Intelligent Enterprise Innovations at Business Transformation Services, SAP America. He is an expert in planning & executing complex digital transformations and has delivered more than 80 engagements with SAP strategic customers over the last 15 years. He speaks frequently at internal & external forums and is an author of a SAP press book.

Let’s block ads! (Why?)

Digitalist Magazine

COVID-19: Let us not forget hopeful news, part five

May 12, 2020 CRM News and Info

I took a week hiatus from writing LUNFHN (again with the totally awkward acronym), because honestly, it takes me a long period of time to find what I need to find and then to write it and link it. But there is no dearth of hopeful news. Just a dearth of writing. So, for your visceral, emotional, and intellectual pleasure, here is my fifth installment of hopeful news around COVID-19.

Treatments, Vaccines, and All…That…Jaaaaazz.

Featured stories

There is a stem cell treatment developed in Abu Dhabi that was tested on 73 patients and they ALL recovered. The Abu Dhabi Stem Cell Centre developed the treatment and said if the results continue to hold up it could be a “game changer.” My original source was the London Mirror but, thank god, I found it in several Abu Dhabi based news sources and Arab news bureaus and press so I am willing to vouch for the reality of the story.
Looking forward to Winter? The llama, not the season. Apparently, there is some promise in llama antibodies for staving off COVID-19, as they were able to stave off SARS and MERS. The research supports that. Here’s a paragraph from the NY Times on this: “Winter was simply the lucky llama chosen by researchers in Belgium, where she lives, to participate in a series of virus studies involving both SARS and MERS. Finding that her antibodies staved off those infections, the scientists posited that those same antibodies could also neutralize the new virus that causes Covid-19. They were right, and published their results Tuesday in the journal Cell.” And Winter is just so CUTE!!
Blood thinners are a promising treatment for the prevention of blood clots in COVID-19 patients. Turns out clots are one of the causes of COVID-19 deaths and the studies that have been released show a dramatic decline in deaths with patients treated with the blood thinners.

The Good the Tech Industry Does…

Salesforce seems to be in this every week. Much as I like Salesforce – it’s not because of that. Its effort in this crisis is not just laudable, it’s heroic, epic really. As of May 4, it was announcing Salesforce Work.com, a portal for literally everything you need to manage your workforce in any time of crisis. The reason I word it that way, is that Salesforce Care is, I have to presume, not going away when the pandemic does. It will be permanent. Among the offerings of expert advice, management technology tools and access to best practices, one stands out to me in particular. It has developed emergency response management tools with Accenture to help you handle everything from crisis intervention to business continuity. The full press release can be read here.

The Good that People(s) Do

In 1847 The Choctaw Nation, impoverished and suffering, donated $ 170.00 of their very limited funds to support the starving people of Ireland. That great deed was never forgotten. Now 173 years later, the Irish people are returning the favor to the Native American population of the U.S. and are major donors to a GoFundMe effort to support the Navajo and Hopi nations who are struggling with Covid-19. The statement from the Choctaw says it all: The Choctaw and Irish had become kindred spirits since the potato famine, the tribe said. “We hope the Irish, Navajo and Hopi peoples develop lasting friendships, as we have. Sharing our cultures makes the world grow smaller.”
Guy Fieri, noted chef and all around raconteur, has raised $ 20 million in relief for out of work restaurant employees. I find it amazing how much people — famous or otherwise — are doing for each other in the midst of this crisis. Amazing, but expected. The good in people, the empathetic nature of the species shows up when it has to.
The Reform Alliance, headed by Twitter magnate Jack Dorsey, Meek Mill and the inimitable Jay Z are sending 10 million masks and other PPEs to correctional institutions across the country. I say no more.

Useful Information

Sometimes it comes from strange places. Of all companies, Kroger came out with a playbook really called “Sharing What We Learned: A Blueprint for Businesses.” It is actually a really good retailer’s (and applicable other ways) guide to handling the store during the lockdowns.
Finally. Someone who likes working from home — and is not just accommodating it. This article in Fast Company by Doug Aamoth outlines some things you can do that will make it not just a burden to be shouldered but be something that you will actually enjoy. Even beyond the pandemic. And there will be a “beyond the pandemic.” Hang in there.

Best PowerPoint of the Week

Of course, this is the only PowerPoint of the entire pandemic…outside of the 10 million generated by the tech industry. This is once again a Ryan Reynolds tour de force. Takes PowerPoint to a new level – though the level is sideways. Or down…

[embedded content]

Best Corona Cover of…What day is this?

This week, we reach back to Ariel and Under the Sea. Why wouldn’t we? This one is SO well done.

[embedded content]

Best Professional Effort of the…More than a Week

I’m cheating. This wasn’t a specifically produced effort for SIP and COVID-19. This is the Prayer of St. Francis as it was sung in Come from Away. If you are unacquainted with Come from Away, it is a major hit Broadway musical about the response of the citizens of Gander, Newfoundland to closing of the U.S. airspace during 9-11. Seven thousand airline passengers were grounded in Gander and all of them, ALL OF THEM, were taken in and taken care of by the Gander citizens — 9000 total citizens in the town. There is a book about this and of course, the musical. This is the Prayer of St. Francis (one of the versions) sung and the beauty of the sentiment and song and the arrangement just should be listened to. By the way, not only am I married to a Newfoundlander, but the character Beulah (whose name you will hear in the beginning) is based on my mother-in-law’s cousin, who was a hero and named…Beulah IRL. Listen and do what I do each time. Cry and also recognize the beauty in the human spirit. I’m not religious but….

[embedded content]

Cheating again…

You’ve probably figured out that I love Broadway. That I miss Broadway — though have only walked down it and gone to theatres in its district. So once again, this was the incredibly well produced Broadway tribute to Moms on Mother’s Day done from homes everywhere as a fundraiser for the COVID-19 Emergency Assistance Fund. I can’t get over how the lighting is perfect, the sound outstanding and the production of choral voices flawless. By the way, when you get to the “Come from Away” segment and the “real Beulah Cooper” – that’s my mother in law’s cousin. She’s family.

[embedded content]

And once again…SGN with John Krasinki

Who am I kidding? This blows away all the competition. This guy has built a monument to how to handle a crisis with humor and empathy. This week he presides over graduation so that those who didn’t get to go to theirs can be part of one.

[embedded content]

I’m just gonna end this post with an SGN/JK trademark ending, something he says at the end of every show.

“Just remember, no matter how hard things get, there is always good in the world.”

You go, everyone.

____________________________________________________

Once again, remember the CRM Playaz Happy Hours on Wednesdays at 3:30pm EDT to 4:30pm EDT(ish). It really ends closer to five but it can’t be a happy hour if it ends in 90 minutes now can it? These have been great – completely booked (free to register) for the entirety of their existence. Next available: May 20. Here’s the link if you’re interested. https://www.eventbrite.com/e/crm-playaz-happy-hour-tickets-101830929050.

Let’s block ads! (Why?)

ZDNet | crm RSS

Data Virtualization Needs to be Part of your Data Integration Toolbox

May 10, 2020 TIBCO Spotfire

Reading Time: 3 minutes

Any craftsman worth their salt has a deep toolbox, knowing that the right specialty tool at hand makes all the difference when completing a high-quality job in a timely manner.

The same can be said for IT, and especially data engineers, responsible for providing data to business consumers. To perform their work, quickly and well, they need to have all the right tools in their data integration toolbox.

Right Tool, Right Job

A broad toolbox with the right combination of data integration capabilities can be extremely valuable when responding rapidly to data requests. It can mean the difference between frustrating delays or punctual satisfaction and better business results.

But there are a variety of data integration tools available today. And it is important to select the right tool if you want to get the job done successfully. To help data engineering leaders ensure their team’s toolbox has everything they need, there are 7 recognized styles for data delivery:

● Bulk/batch (ETL, ELT)

● Replication

● Messaging

● Data services orchestration

● Synchronization

● Data virtualization

● Streaming data integration

Contrary to what some may believe, it’s not about selecting just one of the above tools and assuming it will solve your data integration needs. Many companies will need a combination of data delivery styles to find success—a data integration toolbox that meets their various business goals. According to Gartner, “By 2021, more than 80 percent of organizations will use more than one data delivery style to execute their data integration use cases.”(1)

Why Include Data Virtualization in Your Toolbox

Of all those different data delivery styles, data virtualization is one of the most crucial ones and should be included in your toolbox. Data virtualization is a very useful tool that augments the existing data integration tools that you may already have. It provides a pragmatic integration approach that organizations are increasingly adopting to use along with their favorite ETL/ELT and replication tools.

In fact, according to Gartner’s 2018 Market Guide for Data Virtualization, “Through 2022, 60 percent of all organizations will implement data virtualization as one key delivery style in their data integration architecture…In 2011, only 11% of surveyed organizations reported that they were utilizing data virtualization in a focused set of use cases.” (2)

Cloud Data Sharing is Driving Data Virtualization Demand

But let’s face it, most people try to get by with as few tools as possible. So even when they know about power saws, they won’t buy one until they are faced with a “big job” that requires hundreds of cuts. Cloud data sharing is proving to be just that kind of “big job.”

Why is that? Well, while data and workloads are rapidly moving to the cloud, many legacy systems remain on-premises. This creates a number of new cloud data integration patterns including:

● Hybrid: Integrating on-premises and cloud data

● Multi-instance: Integrating multiple data sources within a single cloud provider

● Multi-cloud: Integrating data across multiple cloud providers

● All of the above: Integrating on-premises, multi-instance, and multi-cloud

With this “big job” of cloud data sharing, comes a need for the right tool to get the job done. Data engineering teams have found that data virtualization is the right integration tool across all these cloud integration patterns. Regardless of the on-premises and cloud topology, data virtualization provides complete, consistent, business-friendly data wherever it happens to live, without moving or replicating it. And even better, its virtualized, metadata-driven data views adapt quickly as your cloud topology changes.

To learn more about how data virtualization can help complete your data integration toolbox and improve your cloud data sharing, check out these educational data virtualization resources we’ve curated for you.

And stay tuned for my next blog on who can most benefit from data virtualization and how to get started!

(1) Gartner, Magic Quadrant for Data Integration Tools, Ehtisham Zaidi, Eric Thoo, Nick Heudecker, 1 August 2019
(2) Gartner Market Guide for Data Virtualization, Sharat Menon, Mark Beyer, Ehtisham Zaidi, Ankush Jain, November 16, 2018

Let’s block ads! (Why?)

The TIBCO Blog

The Impact Of Intense Disruption On Business Models And Strategic Alternatives (Part 1)

May 6, 2020 BI News and Info

When you are in business, you need to be always prepared for the worst. As the current major economic disruption and uncertainty shows, various uncertainties can cause major disruptions to the entire business.

When we look back at this era, I believe we will identify two distinct economic models: before the current disruption, when economies around the world were growing at a record pace, and after the disruption is over, when the economy gradually comes back to normal.

The pace of recovery will be different for different industries, as the crisis changes the way people consume products and services. This will be driven in part by the differentiation between essential and non-essential needs. Several research papers predict that there will be massive changes in consumer spending patterns, both in the short term as well as in the long term, depending upon the type of products.

For instance, there might be an increase in long-term demand for organic products, cleaning supplies, personalized healthcare, and remote services, and we might see more e-commerce and home-delivery patterns emerging from a variety of industries, wherever it makes sense. It is certain that there will be a push to offer redefined value propositions, such as grocery stores that offer certified hygienic products with premium delivery services.

It’s critical to analyze the impact of these disruptive events on your business model and determine the options to redefine the business strategy to address challenges posed by the unforeseen events.

The long-term changes in consumers’ spending habits will also impact corporations and the way they offer products and services. Careful consideration should be given to analyzing evolving market dynamics, and this is the right time to look at your business model to understand how resilient it is to withstanding ongoing economic disruption and the best ways to prepare your organization for it.

The impact will be different for different industries; for the travel industry, it might be a long time before economic activity can be restored, as people will be extra cautious when planning travel. Airlines, hotels, rental cars, taxis, and restaurants are expected to suffer more in the short to medium term due to a very significant dip in demand.

On the other hand, grocery and medicine distributors might see a higher demand for essential products and a dip in non-essential supplies. Some segments of the healthcare and wellness industries will see sustained increases in demand, such as the organic foods, personalized medicines, and preventive healthcare sectors. People have realized the importance of being safe and healthy; especially since healthy people without preexisting conditions appear to have better chances of surviving illness.

This business situation poses some unique challenges and trends across industries.

Challenges

Specific product categories see jumps in demand while others see slumps. For example, cleaning supplies and premium produce might see a jump in the short term, while luxury items might see a slump.

Consumer confidence will likely remain low, and maintaining a high level of customer satisfaction will be increasingly difficult due to external circumstances.

Distributors will face profit erosion from supply chain disruptions due to factors that are not in their control.

There will be capacity constraints on selected sectors and geographies.

Business process adoption trends

Logistics processes, such as stock transfers, drop-ships, and vendor consignment, might gain traction.

Alternate sourcing strategies will become key ingredients of the supply chain to avoid disruptions.

Business impact

Having examined the high-level business situation, let’s deep dive into one of the industries to understand the unique impacts of the current economic shock and how best to prepare your organization.

Consumer products companies are encountering an interesting mix of scenarios, as some subsectors and commodities are witnessing a rise in demand, while many others are seeing a fall in demand. For better illustration of the impact and approach for risk mitigation, let’s take an example of a reference consumer product company, XYZ, which is in fast-moving consumer goods, including food processing and consumable items.

Let’s first understand the key challenges XYZ is facing in the current situation:

Availability of raw materials and ingredients

Difficulty to predict demand for products

Variability in demand according to commodity type

Capacity constraints for essential supplies

The impact on XYZ’s business model

Now let’s look at how these challenges are negatively impacting XYZ’s business model. There could be a need to reassess the value proposition and redefine the category strategy. A business model canvas (as shown below) highlights some of the key business challenges across multiple dimensions.

Figure 1 1024x464 The Impact Of Intense Disruption On Business Models And Strategic Alternatives (Part 1)

Under the “Key Partners” and “Key Activities” headings, you can see that, given that the demand for some essential items might grow exponentially and create capacity issues, while demand for others might slow, there is likely to be demand-pattern variability, according to the commodity. Additionally, commodity demand is further skewed depending on geography, creating a challenge of uneven demand patterns for the very same commodity, which are driven by the intensity of the current disruption in different parts of the world or even in different states of the same country.

In this blog, I will focus on resolving the challenge highlighted above in red: Supply chain disruption due to delivery issues. Supply chain disruption for XYZ might be happening due to multiple factors such as closing borders, country-specific lockdowns, a supplier’s factory shutting down, transport providers cannot deliver raw materials due to the virus’ spread, a supplier’s supplier going out of business, etc.

A disruption in the supply chain impacts one key value proposition: omnichannel availability of the products, which in turn impacts customer segments, as goods are not delivered on time or, in many cases, not delivered at all! All of this results in poor customer experience and, in many cases, lost customers as they start looking for alternate products. This example demonstrates how one issue on the Key Partners’ dimension of XYZ’s business model has a cascading impact on many other dimensions in the business model.

Now let’s discuss how to fix this critical issue on the supply side.

The following figure explores some ways to resolve some of the business challenges. The business model plot above showed areas of concern in specific dimensions and how those challenges have cascading effects on XYZ’s value proposition and customer segments. Now we will focus on resolving the Supply chain disruption due to delivery issues factor.

As you can see below, Identifying alternate sources of supply leading to Faster supplier development could be one solution to the challenge that we identified in the leveraging business model canvas method.

Figure 2 1024x561 The Impact Of Intense Disruption On Business Models And Strategic Alternatives (Part 1)

This article presented an approach to understanding the impact of unforeseen events on your business model and how the impact cascades through and affects many dimensions. In Part 2 of this series, we will deep-dive into capability analysis to understand which capabilities are required to enable identifying alternate sources of supplies as well as developing new suppliers in an expedited manner.

Let’s block ads! (Why?)

Digitalist Magazine

Eliminate the Guesswork with Sales Forecasting: Part 2

May 1, 2020 Microsoft Dynamics CRM

Stop playing the guessing game!

Being able to predict the revenue of your organization is vital. An accurate revenue forecast allows for planning, focusing on the right aspects of your business, making informed strategic decisions, budgeting, and allocating resources appropriately, just to name a few. This in turn saves you time and money. One of the best new features included in the Dynamics 365 2020 Release Wave 1 is Sales Forecasting. It enables you make accurate predictions, eliminating the guesswork. Part 1 of this blog focused on the basics of sales forecasting, as well as configuring sales forecasts. In this second part, we will focus on viewing and analyzing sales forecasts.

Viewing a Sales Forecast

*Note when viewing a forecast, the first thing that needs to be done is to review prerequisites and security roles. In order to configure a sales forecast you must have either a system administrator or forecast manager role. The administrator sets who will have visibility to the forecast, but they are the only one who can make adjustments or changes. For more information on setting up viewing permissions view Microsoft’s document on viewing permissions. To learn more about how to configure a sales forecast, visit part 1 of this blog series or register for our Sales Forecasting webinar. During our webinar, we will discuss, not only how to build monthly, quarterly, or annual sales forecasts for your sales team, but also how to use forecasts with your Dynamics 365 Goals, and how to create reports.

Using Sales Forecast enables your organization to predict the amount of revenue your sales team will generate within a given timeframe. Sales Forecasts can play a significant role in your company’s success. By using a forecast:

Sellers are able to track their performance against targets and identify pipeline risks that might jeopardize their ability to hit the targets.
Managers are able to track individual sales performance against quotas, to proactively provide coaching.
Directors are able to use forecast trends to anticipate departmental sales and reallocate resources if necessary.
Organization leaders are able to use projected estimates to change product strategy or convey updated projections to investors.

After signing into the Sales Hub:

At the bottom of the site map, select Change area, and then select Sales.

SF2 1 157x300 Eliminate the Guesswork with Sales Forecasting: Part 2

2. Under Performance, select Forecasts.

3. On the grid page, do the following: The sales forecast will display all categories as designed by the administrator or forecast manager.

From the drop-down list, choose a forecast. (Forecasts that you have permissions for will appear here).
Select the forecast period you wish to view. Options in the drop-down list reflect the number of forecast periods defined in the forecast configuration. The current period is the default.

4. When setting up a forecast, the administrator can choose to customize the forecast or use out-of-the-box categories. The following is a list of out-of-the-box categories offered:

Users: Shows the hierarchy of users as defined by the Manager field. To learn more, see Forecasts and sales hierarchy.
Quota: A target amount set for a specific owner over a given time frame. The progress bar is calculated against this value.
Pipeline: Shows the aggregated (rolled-up) value of estimated revenue for all open opportunities that have the forecast category set as Pipeline (low confidence). This value can be adjusted during forecast.
Best case: Shows the aggregated (rolled-up) value of estimated revenue of all open opportunities that have the forecast category set as Best Case (medium confidence). This value can be adjusted during forecast.
Committed: Shows the aggregated (rolled-up) value of estimated revenue for all open opportunities that have the forecast category set as Committed (high confidence). This value can be adjusted during forecast.
Omitted: Shows the aggregated (rolled-up) value of excluded revenue for all open opportunities that have the forecast category set as Omitted.
Won: Shows the aggregated (rolled-up) value of revenue that’s generated for all won opportunities that have the forecast category set as Won.
Lost: Shows the aggregated (rolled-up) value of revenue that’s lost for all lost opportunities that have the forecast category set as Lost.

To learn more about viewing a sales forecast, we have included this video from Microsoft:

[embedded content]

In the forecast grid, you can perform the following important functions:

View and manage underlying opportunities – To understand which opportunities are affecting forecast values. Underlying opportunities can be viewed in the kanban view or in a table view.
Adjust values in a forecast – provides sales managers or sellers the ability to estimate the final amount that they expect for a forecast’s opportunities to bring in at the close of the forecast period.
Maintain up-to-date forecast data – Forecast data is automatically recalculated. You may also choose to manually update the data and refresh.

A forecast is a reflection of a seller’s pipeline.

Pipeline management is a big part of forecasting. To get the most accurate forecast, it is vital to not only know what is in your pipeline but also how to accurately adjust it. Data can either be viewed in a grid view or a Kanban view. The Kanban view is a new capability that allows sellers to quickly and easily move deals between forecast stages while also seeing how these changes impact their forecast.

nevAnalyze revenue outcome by using predictive forecasting

In order to utilize predictive forecasting, a Dynamics 365 Sales Insights license must be purchased or a trial started. Predictive forecasting helps sellers and managers to improve their forecast accuracy based on data. These AI-driven models look at a combination of data and the open sales pipeline to predict future revenue outcome. The screen below shows an example of a a screen with a prediction column.

To learn more, see about premium forecasting.

Additional Resources

How can we help?

Contact enCloud9 to discuss how we can improve your sales forecasting and tracking capabilities with Microsoft Dynamics 365. To see how Sales Forecasts can play a significant role in your company’s success, join our April webinar as we take a deep dive into one of the upcoming updates coming to Dynamics 365: Sales Forecasting. We’ll be discussing

How to build monthly quarterly or annual sales forecasts for your sales team
How to use forecasts with Dynamics 365 Goals
Creating reports

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

COVID-19: Let us not forget the hopeful news, part four

April 29, 2020 CRM News and Info

I know I’m supposed to write about CRM, customer engagement and customer experience and that’s what you expect from me. And far be it from me to fall below your expectations. But, if you know me, one of my major themes for the last two or so years around engagement and experience has been not just personalization, but humanization – which translates broadly and very roughly into empathy and the need to be empathetic as a business and as individuals. It also has been themed around communicating that empathy with your customers, your employees, your friends, your family, even absolute strangers. It should be part of your soul, and in a different way at the heart of whatever institutions you are involved with.

Thus, for the last now month, I’ve been doing the “Let us not forget hopeful news” a.k.a. the acronym-awkward LUNFHN (Lunfin?). The purpose was to separate the good, the warm, the hopeful news signals from the non-stop litany/noise of the sensationalism of the media premised on their head-scratching decision to double down the old saw “if it bleeds, it leads.” I have a degree in journalism and a major in English from Northwestern and honestly, I am ashamed of the way that the press makes sure that all the horrors are evident all the time and when you watch the broadcast media, you see them going into excruciating detail of the worst of the worst. For many it becomes suffocating and even traumatic as they see what they believe is a never-ending horror. To punctuate this, the media makes a point of interviewing grieving people who have lost a loved one to COVID-19 and make sure they ask questions that make the heartbroken person relive what they just saw and felt so that the media can push the point home. All in the name of realism and news. So, what you get if you watch the news is an overwhelming, suffocating feeling of both horror and uncertainty and at minimum a nasty headache.

I say otherwise.

If human history means anything, you find always that we manage to get past these crises and once we do, we’ve learned something that at best ends the crisis and prevents future similar ones, or at worst gets it under control in a way that allows us to continue on with our lives – even as changed as they may be. We are often better as a result of the worse. World War II was an uncertain horror of a different kind. But we ended up with a prosperity that led us to the generation of the greatest wealth in human history – and to the benefit of a larger part of the population than had previously been imagined, though of course, there were struggles along the way.

But what has been phenomenal as we approach May — the third full month of this pandemic — is that the human spirit is not only not lost, it is flourishing. The level of creativity, the transformation of norms and changes in memes and weathering storms (note the poetic turn there) has been just inspirational. It is amazing what people can do on their own, with their families and friends, or professional colleagues when physically unable to communicate but virtually in communication all the time. Also, its genuinely astonishing how many beautiful voices are out there singing to and for all of us – and themselves of course. Just as amazing is that we have the technological infrastructure to allow a massive increase in volume and velocity of digital communications that, while it might be stretching the system, isn’t breaking it – allowing us to continue to connect. And equally as impressive is the imagination of people and the creativity and humor they manage to bring to themselves, their families and the world in the midst of crisis. And despite all the dire warnings of the length of time it will take to arrive at a safe enough vaccine and to develop the treatment to help resolve the illness and to end this pandemic even if it doesn’t eliminate the virus, the level of global cooperation is unprecedented with scientists/medical professionals working together around the globe to come up with an answer. The results of ongoing and just starting, efforts? In record time, we’ve begun human and animal trials.

Hopefully, this is a source for you of some positive but real information, news and entertainment. I spent hours auditioning the videos to come up with the best of the best that I find each week. I spend hours and hours finding what can be random but always interesting and useful (I hope) posts and articles and videos on things that might do anything from keep your hygiene at human levels to entertain you – but all of this is to remind you of both the resiliency, creativity and general goodness that characterizes our species and that manifests even more powerfully during crises like the current one.

So, event though we are in a pandemic….enjoy.

Useful Information…Sorta

McKinsey charts a wise course for how to engage (or “connect”) with customers during a crisis like this. Not surprisingly they come to the same conclusion that most of us have come to. Empathy. Their way of putting it might be a bit more dispassionate – “lead with empathy” but the sentiment remains important. For the complete document – well worth reading – come here. You’ll have to register for a McKinsey account, but its free.
Okay, cutting your own hair as a useful thing in a business post, might not rise to the level of a McKinsey report – but then again… Don’t think this is important? Take a look at yourself in a mirror, dude(ss). It’s clearly important. Given that we still cannot go to barbers and that barbers are actually essential to the grooming of our coiffure, I scoured the web looking for the best guides for cutting your own hair (given that your significant other may refuse to do it). First, here’s what Bored Panda found happens when it goes wrong for 24 different people. If that isn’t scary enough to make you want to do it the right way, then let it grow out. But if you want to do it properly, here are guides on how to cut hair the right way when you are doing it yourself. First for women: Here’s what Good Housekeeping put out just as the crisis wound up. Now for men: Men’s Health magazine includes some recommended kits)…a lot simpler. At least I thought so. Let’s just say if I had a butcher do the haircutting for me I would have been better off. Oh, BTW, buying hair clippers is not so easy either. Most of the ones mentioned in the Men’s Health article are unavailable except for a very few. Mad rush to Amazon….commence.
Since you do have time, I’m thinking that you’re thinking “I wish I was a DJ.” Well, lucky you. The Library of Congress, the center of contemporary music, has a new tool out called “Citizen DJ” which lets you mix your own beats. For me I can mix beets (the red ones) and, thus make a salad, but this….? Nah. But hey, each to his own. I’d do this if I had any talent at all. I don’t.
This is an old school thing that might be of real interest to those of you who are a. baby boomers and b. die-hard sports fans who are really missing sports (I raise my own hand). Here is an article that appeared in ESPN re: playing the baseball replay games that might fill that hole in your various organs. If you are interested in playing your kids or your parents in the board games, here are two of the most popular (for 50 years) APBA. Stratomatic. Not to be outdone, if you are younger and a sports fanatic here are some PC-based versions of the same and similar things. APBA Baseball for Windows. Stratomatic Computer Baseball. The multi-sport (baseball, Football, Hockey, Basketball, Golf) Action PC Sports – arguably the best of the lot. It will help, though won’t complete you.
Zoom fatigue isn’t just a term invented by some bored SIPers, it’s something that we are now going to have to deal with because of its impact on people’s psyches. SAP’s Future of Customer Engagement and Experience’s Editor in Chief and all around Renaissance person Jenn Van De Zande speaks of not only the emerging issue that is concurrent with the continuous use of Zoom (and other engagement communications platforms) but some of the ways to disengage and deal with the fatigue.

The Good Things Non-Tech Companies Do

All State is returning the enormous sum of $ 600 million in premiums to their customers. These are their auto insurance customers. They are giving the customers a 15% rebate on their already paid insurance premiums due to the lower instance of auto claims due to the….obvious. Geico is doing the same, though, being a Geico customer, I personally have no evidence of this.
Verizon has been acting super responsibly during the crisis – in part by adding services and bandwidth for free to existing customers, like 15GB of wireless LTE (4G) hotspot data for free, but even more importantly by investing seriously in supporting the frontlines. They have donated more than $ 50 million to “nonprofits including No Kid Hungry, the American Red Cross, and the CDP COVID-19 Solidarity Response Fund for WHO. And in partnership with the New York Times, we’re giving 14 million high school students within the U.S. free digital access to NewYorkTimes.‌com.”

The Good Things Tech Companies Do

Once again, I’m mindful of how important it is when companies are supporting the supply chain in this time of crisis. Materials management — which involves the prioritization of goods and services being made available to the appropriate parties and the movement of goods when disruptions upset the supply chain among its many values — is the subject of an app that German-born tech company Celonis has developed for customers and non-customers alike. It’s called Celonis Snap. For their customers, it can be dropped into the system. For non-customers there is an online site where you can upload data and get the results you are seeking.
Oracle is now the technology infrastructure backbone for Zoom. That might not sound like “the good things” and might be seen by some as a business deal – Reuters for example, but it is much more than that. Read this blog post by Oracle correspondent Barb Darrow on the agreement between Oracle and Zoom that is public as of April 28. The key is the blog headline: “Zoom Taps Oracle to Keep Schools Teaching, Businesses Running, Friends and Family Connected.” With 300 million Zoom users and Zoom as a verb, Zoom has become vital to international communications, even though it was unintentional. Oracle’s backbone can support that scale and thus can –as best as can be anticipated — help secure the success of communications that are currently vital to the world.
Microsoft was asked by New York State to help build an app to support COVID-19 and antibody testing. A team of Microsoft, local, and state government engineers built it with Dynamics 365 and had it up and running in five days. Now it’s being used across 22 testing sites to register patients and schedule tests. Five days.

The New Normal?

stabakrobot1 COVID 19: Let us not forget the hopeful news, part four — Source: Thomas Torjusen

The Norwegian Professional Football League’s Stabaek team held a press conference announcing a new appointment for the club. In attendance: a club official, the new guy, a camera man, a sound man and the press…robots. Each of the press were not only represented by these cute little R2D2ish creatures but even had press credential badges and were able to ask questions via the robots and get answers. Went off without a hitch. (See above.). For more of this tune in to this CRM Playaz episode that Brent Leary and I did last week with Thomas Torjusen, Head of Media and Chief Digital Officer at Norsk Toppfotball — and a thoroughly knowledgeable, and charming man.
Preferred Hotels did a global travel survey to see what people are planning post-pandemic when it comes to travel. It has an old normal vibe to it which is somewhat surprising, but it still is something to see that people still want to get on planes and go places, with all of this. Kind of makes me happy.
Apparently the signage industry has a new, hot category, social distancing signage. There isn’t a whole lot to say about this one. Just click on the link and look at the signage.
Finally, Phil Wainwright of the esteemed diginomica writes a piece on how business will be changed forever post-Covid-19 “Six Ways Business Will be Changed Once Covid-19 Passes” that isn’t just the usual fluff and fantasy. None of what he says is a stretch – all of it is possible. He wrote it at the end of March. But its still timely since the crisis hasn’t ended, and, thus, its ahead of its time.

Best Corona Covers

These are some of the most creative things I’ve ever seen – virus or not – the level of parody, production and great singing voices are nonpareil.(I’ve inserted the link to the definition because I know you don’t want to bother to look it up but you’re not sure I’m using it right).

Broadway

Zach Timson seems to be a really talented parodist and his lyrics are exceptionally clever – leading to Corona cover medleys of Broadway tunes. And he has a decent Broadway-ish voice too! Here is his Broadway Corona Medley recorded 3/21/2020. The reason I mention the date- he makes a point of saying, updated info please wear masks.

[embedded content]

Pop/Rock

This comes from a very talented someone who calls himself “The Kiffness.” Not sure where that comes from, but the guy not only writes funny parodies but also does spot-on imitations of the artists singing the songs he parodies. This was my favorite though spoiler alert and warning, it’s a politicized one. It’s called “Crappy” parodying of course, Pharrell’s “Happy.”

[embedded content]

Best Original Not-Really-A-Corona-Cover

Once again, my favorite Corona Cover artiste (yes, artiste) Chris Mann, has an original from his album on what a date night looks like under quarantine. His wife is in this and she’s pretty good too. Called “Old Fashioned.”

[embedded content]

Best “We Are In This Together” Professional Zoom Music

Broadway wins again in this one. This is the official London Theatre Company Quarantine Zoom Production of the Mamma Mia theme song. The sheer fun they have singing it is worth it alone. Just great!!

[embedded content]

What Can I Say? John Krasinski SGN #4 The Prom

There’s no competition with this guy with his funny, sweet, empathetic, brilliant show and audience connection. So, from, now on he is hall of fame and will be featured in each of these LUNFHN posts I do. In this one, John realized that no one was going to the prom this year so he staged a prom with the Jonas Brothers among others and lots of shots of kids dressed to the prom eights (hey, the nines dressing comes when you are older) dancing to the music. Kind of touching to see these high-schoolers dressed up and smiling as they danced to an audience of millions on the internet – and music that, most likely, they wouldn’t have heard at their actual proms.

[embedded content]

CRM Playaz presents the Playaz Place Bar and Not Grill Happy Hour

If you are interested in joining the hit event The CRM Playaz Present: Playaz Place Bar and Not Grill Happy Hour any time in the next 38 weeks, here is a link to register. Warning: We are sold out (don’t worry its a free ticket) for April 29 and now May 6. There is some availability for the rest of the month of May but we are getting daily registrations. We do have other weeks available, so feel free to sign up for them. Or put yourself on the Waitlist for an already sold out Happy Hour. If you are interested, the Happy Hour is 3:30pm ET every Wednesday. Bring a glass of a drinkable liquid with you. You will be asked about it.

We will be announcing some super cool stuff throughout the Happy Hour (called, by some of the attendees, the Magic Hour), though for those of you who are willing to date themselves, I’m not a big fan of that, because if you do remember, Magic Johnson had a TV show called the Magic Hour that lasted all of six episodes and was the only thing that I think he ever failed at. Thus, the Happy Hour it stays. Come join us. All are welcome.

Coronavirus Updates

Let’s block ads! (Why?)

ZDNet | crm RSS

Monitoring the Power Platform: Canvas Driven Apps – Auditing and Activity Logs Part 1

April 25, 2020 Microsoft Dynamics CRM

Summary

Microsoft Power Apps Canvas Apps provide users the ability to quickly create and publish applications to their enterprise. With this ability comes concerns with governance to ensure users are allowed the appropriate permissions as well as identifying and highlighting specific apps across the organization. This article will explore promoting a Canvas App as a Featured or Hero app which can be used as communication devices during crisis, internal training, etc. From there the article will discuss other scenarios useful for monitoring Canvas Apps with the Power Apps Administration PowerShell cmdlets. To wrap up, the article will go into activity events taken within the Power Platform.

Featured and Hero Apps

Once your enterprise begins creating Power Apps, one thing users will begin to notice is abundance of apps in your Power Apps environment. The Power Apps Player has functionality to allow users to search for a specific app but knowing what to look for and who published it can be a challenge. By default Power Apps provides views which can help distinguish apps created or favorites by a user.

PowerApps.FeaturedAppView Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 1

For an enterprise, the ability to quickly create and promote an app is key driver for choosing Power Apps as a platform. In a real world example, consider the COVID-19 crisis. An enterprise may want to push out an app that allows users to check in, provide emergency contacts, or stay up to date on the latest news. An example of such as app is the Crisis Communication Template, developed by Microsoft. Luckily Power Apps provides a mechanism to set an app to be a featured app which will prominently display in a specific view allowing users to quickly find and open the app.

PowerApps.FeaturedAppAndHeroApp Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 1

As shown above, multiple featured apps can be added to a list available to all Power Apps users within the organization.

A Hero App, is an app that will be shown at the top of default list for Power Apps. This is also extended to other lists, even the Featured List. The Hero App will also be given the headline “FEATURED APP” to help distinguish it from other apps. As described in Pat Dunn’s article, Discoverability in the Enterprise, there can be only one Hero App for an organization. Also detailed in the article are the PowerShell cmdlets needed to create Featured and Hero Apps.

PowerApps.HeroApp Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 1

Using PowerShell to create Featured and Hero Apps

To set an app as a Featured or Hero app, two PowerShell cmdlets are required. Before using the cmdlets, the specific modules that contain them need to be downloaded for use. The article, PowerShell support for Power Apps, has step by step instructions of how to get and work with the modules.

Install the Modules

To download the modules as an administrator, use the following command in PowerShell:

Install-Module -Name Microsoft.PowerApps.Administration.PowerShell 

If you don’t have admin rights, use the following PowerShell command:
Save-Module -Name Microsoft.PowerApps.Administration.PowerShell -Path 
Import-Module -Name Microsoft.PowerApps.Administration.PowerShell

Be mindful of the -Path argument if working with automation such as Azure DevOps Pipelines.
Connect to Power Apps
 
To connect to your Power Apps tenant, run the following command in PowerShell:
Add-PowerAppsAccount

That command will prompt you for credentials. Using the -Username and -Password arguments, a script can run passive authentication without user input.
Get Environment and Canvas App Identifiers
 
Once connected, you’ll need the Canvas App identifier to set as the Featured or Hero App. A quick way for a Power App Maker or Admin to find the values needed are to navigate to the app itself and get the identifiers from the URL. The below URL is from my app I want to set as a Featured App:
https://make.powerapps.com/environments/{Environment ID}/apps/{Canvas App ID}/
This approach works well today if the URL continues to show the Canvas App Identifier. Another approach is to use PowerShell to get the App Id. The Environment Id can be found using the Get-AdminPowerAppEnvironment cmdlet (EnvironmentName) while the App Id can be found in the Get-AdminPowerApp cmdlet (AppName). Combining those values we can now use the Featured and Hero commands in PowerShell.
Set Canvas App as Featured or Hero App
 
To mark an app as a Featured App use the following PowerShell cmdlet:
Set-AdminPowerAppAsFeatured -AppName {AppName}

To mark an app as a Hero App use the following PowerShell cmdlet:
Set-AdminPowerAppAsHero -AppName {AppName}

When the commands are run, a response is sent back with a success HTTP code and headers including a request identifier. To confirm the Power App is Featured or Hero, a user can navigate to the Power Apps Player to view. 
As an administrator, I’ll want tighter control and visibility into these actions as well as other lifecycle events for Power Apps. Capturing when apps are created and destroyed, updated and restored are integral to better understanding usage patterns and ensuring governance is applied. To find out more, refer to Part 2 where we will begin to search the Unified Audit Log for activity and audit logs.
Other Useful Power Apps Administration PowerShell Tasks
 
The PowerShell module for Power Apps Administration tasks is an extremely useful way to view and take action on environments, apps, flows, connectors and permissions. The PowerShell Support for Power Apps page contains a list of common operations an administrator would find useful for governing an organization’s use of the Power Platform and its environments. Some commands I’d like to call out are:




Command
Description




Get-AdminDlpPolicy
Displays all of the Data Loss Prevention Policies


Remove-AllowedConsentPlans

Can be used to remove the ability for uses to create trial licenses. 
NOTE: It is important to note that when using Remove-AllowedConsentPlans all existing plans of the specified type will be removed from all users in the tenant and will not be recoverable.



Set-AdminPowerAppOwner
Changes the owner of a Power App. This is useful for reassigning if someone leave the organization.


Set-AdminFlowOwnerRole
Changes the owner of a Power Automate Flow. Again, useful for reassignment.


Recover-AdminPowerAppEnvironment
Recover a Power App Environment




The “Get” cmdlets can all be used for reporting and logging of the current state of an environment. The below command from the examples on the Power Apps PowerShell page shows how to use the OutputFilePath argument to write to a text file.
Get-AdminPowerAppsUserDetails -OutputFilePath '.\adminUserDetails.txt' –UserPrincipalName 'admin@bappartners.onmicrosoft.com'

Consider the use of these commands with techniques discussed in Part 2 of Canvas Apps – Auditing and Activity Logs for additional logging capabilities.
Activity Events for Canvas Apps
 
What events are audited?
 
The events audited by the Office 365 Security and Compliance Center allow for administrators to search across multiple services offered including Power Apps. These logs are collected at the SDK layer, meaning one action can actually trigger multiple event logs in the log store. Events collected include app lifecycle actions such as when an app is created, edited or deleted. User actions include launching an app or modifying permissions.
Common Audit Properties
 
Each audit record includes common properties that can be used to help search or even correlate events together. Fields such as precise time stamps, the specific Canvas App, the operation performed and Identity of the record. 




Field name
Description




Date
Date and time of when the log was generated in UTC


App Name
Unique Identifier of the Power Apps Canvas App


Id
Unique GUID for every row logged


Result Status
Status of the row logged. Success in most cases.


Organization Id
Unique identifier of the organization from which the log was generated.


CreationTime
Date and time of when the log was generated in UTC


Operation
Name of operation


UserKey
Unique Identifier of the User in Azure AD


UserType
The audit type (Admin, Regular, System)


Additional Info
Additional information if any (e.g. the environment name)




The Identity value is key as it can help us better understand a chain of events. Consider the events that take place when promoting an app as a Featured App. Marking an app as featured creates three events:
MarkPowerAppAsFeatured
PublishPowerApp
UpdatePowerApp
The same goes with promoting a Hero app as well as other events such as restoring a Canvas App from a previous version.

In Part 2, we will cover these events in detail to show how they are connected to paint a broader picture.
List of Events
 
The table below shows the events captured for Power Apps including life cycle events of app, user modifications and when a user launched a specific Canvas App. For an up to date reference check out the Activity logging for Power Apps Audit Schema document.




Event
Description




Created app
When the app gets created for the first time by a maker


Launched app
When the app gets launched


Marked app as Featured
Every time the app is marked as Featured


Restored app version
The version of the app when restored


Edited app
Any updates made to the app by the maker


Published app
When the app is published and is now made available to others in the environment


Edited app permission
Every time a user’s permissions to the app is changed


Deleted app
When the app is deleted


Marked app as Hero
Every time the app is marked as Hero


Deleted app permission
Every time a user’s permissions to the app is removed





Life Cycle Events
 
Life cycle events include when a maker creates an app, edits and publishes an app. Other events include restoring an app to a prior version as well as the previously mentioned promoting to a featured or hero app.
Launched Event
 
The Launched event is a highly asked for event that captured the precise time that a user logins into an app. This audited event allows admins to see the user’s Azure identifier and which app they logged into. Not shown is the user’s session identifier but this can be found within the specific Canvas App run which will be discussed later.
Edited App Permission Event
 
The Edited App Permission event is of interest as it shows when and who shared an app. The permission level (CanEdit or CanView) given, the owner who shared and when they shared the app is audited.

Resources can be shared and specifically for the Common Data Service connector, security roles can be assigned for use with the connector, which will also assign the security role to the user within the environment.

In the image above, I gave the user the “Custom Sys Admins” security role for the Common Data Service connector. Navigating to the user roles within the Model Driven App I can see the user now has the same security role. Consider the implications before adding a security role to a Canvas App user. For further reference, check out the Security Model for Common Data Service.

Next Steps
 
In this article we have covered getting started with the Power App Administration PowerShell module and cmdlets for Featured and Hero App promotion. Continue exploring the capabilities of this module as it pertains to your Power Apps Canvas App administrative operations. 
We have also covered the what activities are currently captured for Power Apps Canvas Apps. Discussed in Part 2 are techniques to view the audit logs within the Office 365 Security and Compliance Portal as well as automated techniques. 
This article is designed to supplement the article on Power App Analytics, which provides more of an all up view of Canvas App usage. Combining these two documents, an administrator can now track analytic metrics as well as the events that define those metrics.
If you are interested in learning more about specialized guidance and training for monitoring or other areas of the Power Platform, which includes a monitoring workshop, please contact your Technical Account Manager or Microsoft representative for further details. 
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, please go here to submit a new issue.
Index
 
Monitoring the Power Platform: Introduction and Index
Let’s block ads! (Why?)
Dynamics 365 Customer Engagement in the Field

Command	Description
Get-AdminDlpPolicy	Displays all of the Data Loss Prevention Policies
Remove-AllowedConsentPlans	Can be used to remove the ability for uses to create trial licenses. NOTE: It is important to note that when using Remove-AllowedConsentPlans all existing plans of the specified type will be removed from all users in the tenant and will not be recoverable.
Set-AdminPowerAppOwner	Changes the owner of a Power App. This is useful for reassigning if someone leave the organization.
Set-AdminFlowOwnerRole	Changes the owner of a Power Automate Flow. Again, useful for reassignment.
Recover-AdminPowerAppEnvironment	Recover a Power App Environment

Field name	Description
Date	Date and time of when the log was generated in UTC
App Name	Unique Identifier of the Power Apps Canvas App
Id	Unique GUID for every row logged
Result Status	Status of the row logged. Success in most cases.
Organization Id	Unique identifier of the organization from which the log was generated.
CreationTime	Date and time of when the log was generated in UTC
Operation	Name of operation
UserKey	Unique Identifier of the User in Azure AD
UserType	The audit type (Admin, Regular, System)
Additional Info	Additional information if any (e.g. the environment name)

Event	Description
Created app	When the app gets created for the first time by a maker
Launched app	When the app gets launched
Marked app as Featured	Every time the app is marked as Featured
Restored app version	The version of the app when restored
Edited app	Any updates made to the app by the maker
Published app	When the app is published and is now made available to others in the environment
Edited app permission	Every time a user’s permissions to the app is changed
Deleted app	When the app is deleted
Marked app as Hero	Every time the app is marked as Hero
Deleted app permission	Every time a user’s permissions to the app is removed

Monitoring the Power Platform: Canvas Driven Apps – Auditing and Activity Logs Part 2

April 25, 2020 Microsoft Dynamics CRM

Summary

This article will build off of Canvas Apps – Auditing and Activity Part 1 to examine how to search and store audit and activity logs for Canvas Apps. This includes how to turn on auditing, how to use the Office 365 Compliance Portal, the Unified Audit Log PowerShell command and the Office 365 Management Activity API. We will conclude with thoughts on monitoring tools such as Azure Sentinel, and storage tools such as Cosmos DB and Azure Blob Storage.

Searching the Unified Audit Log

Searching the Unified Audit Log can be performed both manually and with automation. Manually there is a portal called the Office 365 Security and Compliance Center Portal that provides a centralized area for auditing all Office 365 services including the Power Platform. If automation is desired, the Unified Audit Log offers both a PowerShell module and an API which can be subscribed to. This section covers all three of these in detail.

The Office 365 Security and Compliance Center Portal

Accessing the Office 365 Audit Reports

To begin using the Office 365 Unified Audit Log, your organization will need to have at minimum a specific Office 365 or Microsoft 365 license. Currently the minimum license is the E3 license. The type of license will impact the retention of the logs, E3 is for 90 days while the E5 license retains for a year by default. To turn on, click the “Turn on auditing” button. If an error message occurs, most likely its due to licensing.

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

Users will need to be an Office 365 Global Administrator or be a member of one or more Security and Compliance Center role groups. To provide a user access to the Security and Compliance Center, use the Office 365 Admin Center and open the Admin center for Compliance. Alternatively, you can directly access the permissions page from here. The permission needed is “View-Only Audit Logs” or “Audit Logs” which allows for viewing and exporting of audit reports.

Navigating the Office 365 Compliance Center Portal

The Office 365 Security and Compliance Center, found at https://protection.office.com, can be used by security analysts to manually search for specific events using a portal user interface.

4162.pastedimage1587649976014v8 Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

As shown in the image above, a single or multiple activities performed on a Canvas App can be chosen for analysis. To begin our search, run an audit log search by specifying the activities, the time range and optional search parameters such as users or site. Once the search is complete, review the results in the portal. The results are maxed out at the most 5000 newest events and are incremented 150 records at a time.

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

Below is an image showing a drill down into the Launched app audit record.

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

Here is an image showing where I edited the App permissions to allow Everyone in my organization access to view:

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

The Object Id within Azure Active Directory:

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

PowerShell

In PowerShell the Search-UnifiedAuditLog, part of the Exchange Online PowerShell V2 module, is used for Office 365 services including Power Apps Canvas and Model Driven Apps. For Canvas Apps there exist a record type called “PowerAppsApp” that will scope to only logs from Canvas Apps. Using the events listed in the Power Apps logging documentation, here are the PowerShell Operation equivalents to each captured event:

Event	PowerShell Operation
Created app	CreatePowerApp
Launched app	LaunchPowerApp
Marked app as Featured	MarkPowerAppAsFeatured
Restored app version	PromotePowerAppVersion
Edited app	UpdatePowerApp
Published app	PublishPowerApp
Edited app permission	PowerAppPermissionEdited
Deleted app	DeletePowerApp
Marked app as Hero	MarkPowerAppAsHero
Deleted app permission	PowerAppPermissionDeleted

To use the Search-UnifiedAuditLog, connect using the Connect-ExchangeOnline cmdlet. This is a replacement for using the New-PSSession, however currently creating this session will also work.

Import-Module ExchangeOnlineManagement  

$  UserCredential = Get-Credential
Connect-ExchangeOnline -Credential $  UserCredential

NOTE: Please refer to the section “Important Notes on the Exchange Online Module” for important authentication considerations.

The below PowerShell command uses the Search-UnifiedAuditLog to search for any activities related to Canvas Apps. To specify this, use the -RecordType argument with the value “PowerAppsApp“.

$  endDate = Get-Date
$  startDate = $  endDate.AddDays(-7) #Search last 7 days
Search-UnifiedAuditLog -StartDate $  startDate -EndDate $  endDate -RecordType PowerAppsApp

Monitoring the Power Platform: Canvas Driven Apps Auditing and Activity Logs Part 2

As the image above shows, this will bring back all activities related to Canvas Apps. Depending on what type of audit record you’re looking for, the search can be filtered using the Operation argument.

Example of searching for Canvas App creation events:

Search-UnifiedAuditLog -StartDate $  startDate -EndDate $  endDate -RecordType PowerAppsApp -Operation "CreatePowerApp"

Example of searching for Canvas App launch events:
Search-UnifiedAuditLog -StartDate $  startDate -EndDate $  endDate -RecordType PowerAppsApp -Operation "LaunchPowerApp"

Each operation can also be filtered down to the specific user using the -UserIds argument. Consider the below script for filtering launch events for a specific user:
Search-UnifiedAuditLog -UserIds "user@tenant.onmicrosoft.com" -StartDate $  startDate -EndDate $  endDate -RecordType "PowerAppsApp" -Operations "LaunchPowerApp"

Each record returned includes the data of the audit, the user and the data collected within the AuditData property. The app and environment properties are available within this property which can be extracted and stored for contextual information to a log data store. For help working with the AuditData property, consider using with the “ConvertFrom-Json” cmdlet.
$  AuditData = ConvertFrom-Json -InputObject $  _.AuditData

Azure Automation allows for the use of running PowerShell runbooks in Azure. Here is an example image of the output from running the Search-UnifiedAuditLog in Azure Automation:

One of the benefits of Azure Automation is the ability to schedule the execution of our script. The image below shows the recurrence based on a schedule created.

Another benefit is the native integration with Azure Log Analytics. This integration will come in handy further in this article.

Kusto Query:
AzureDiagnostics  
| where StreamType_s == "Output" 
| project TimeGenerated, ResultDescription
Important Notes on the Exchange Online Module
 
NOTE: I want to point out verbiage that exists on the Search-UnifiedAuditLog page referring to programmatically downloading data from the audit log:
If you want to programmatically download data from the Office 365 Audit Log, we recommend that you use the Office 365 Management Activity API instead of using the Search-UnifiedAuditLog cmdlet in a PowerShell script. The Office 365 Management Activity API is a REST web service that you can use to develop operations, security, and compliance monitoring solutions for your organization. For more information, see Office 365 Management Activity API reference.
The above is important to point out that the current Exchange Online modules rely on Basic authentication. This is a major concern as it requires either a user to interactively login or to supply credentials as a username and password stored somewhere like Azure Key Vault.
Office 365 Management Activity API
 
Another way to automate delivery of audit data is by use of the Office 365 Management Activity API. To begin using the API, an App Registration needs to be created in Azure Active Directory. The App Registration will need to have permissions to the Office 365 Management APIs, scoped to the ActivityFeed.Read permission. Once created, the App Registration can be used to get an access token for working with the API subscriptions and blobs. The below image shows the authentication flow and a single request to the API.

Now that the authentication mechanism is in place, we will have to tell the Office 365 Management API which activities we are interested in. This, in my opinion, is where I hope to see the API achieve filter parity with the PowerShell or Portal for the Unified Audit Log. For Power Apps Canvas Apps, there is no way to filter to the specific workload like what is available in the Portal or through PowerShell. This means we have to subscribe to a general bucket of events called Audit.General.
To get the authorization token and create the subscription I used Postman. I’ve included my sample Postman collection here which shows how to get an authorization token, to start a subscription, as well as poll for notifications.
As stated in the documentation, a subscription needs to be created for Audit.General. Each tenant you intend to monitor will need its own subscription. Once created, the subscription can be used to poll for events or as a webhook to have notification delivered when events are ready. NOTE: Subscriptions can take up to 12 hours before the content blobs are available.
For the webhook I followed a really great article by Amreek Singh that covers how to setup a subscription and webhook to deliver to Cosmos DB for storing and analysis. Included in his article are Power Automate flow examples that can be used to learn more. The example does not include the authorization token and I’m including this as an addition to his flows.

When receiving a notification, an array of content blobs will be delivered. Here’s an example of a content blob notification:
{
    "contentUri": "https://manage.office.com/api/v1.0/1557f771-4c8e-4dbd-8b80-dd00a88e833e/activity/feed/audit/<identifier>$  audit_general$  Audit_General$  na0045",
    "contentId": "<identifier>$  audit_general$  Audit_General$  na0045",
    "contentType": "Audit.General",
    "contentCreated": "2020-04-20T21:43:05.262Z",
    "contentExpiration": "2020-04-27T21:40:03.973Z"
}
From what I’ve seen, each blob contains around 10-15 minutes worth of audits but this could vary depending on the activity captured. Below is an image of the frequency of delivery to the webhook to my flow.

Managing, Archiving and Retaining Events
 
Utilizing Azure Log Analytics or Azure Application Insights offers native integration with additional monitoring tools to help detect anomalies. Tools such as Azure Sentinel, offering a Security Information Event Management (SIEM) and Security Orchestration Automated Response (SOAR), or Azure Monitoring, offering analysis detection solutions, are perfect for this.

As stated above, based on the license type, audit logs are retained from 90 days to one year. However, business requirements may dictate a long term solution that could cover multiple years of audit activity. Regardless which mechanism to extract data, be it from the Portal or through PowerShell or the API, archiving these audits is a key requirement for most organizations. Each of these tools provide us capabilities to integrate these logs into a centralized data store such as Azure Blob Storage or Cosmos DB for long term retention.

This topic has a considerable amount of impact in your organization’s strategy. A future article will cover this topic in more detail, and when published, I’ll link from here.
Advanced Audit in Microsoft 365
 
A recent addition to the compliance tooling within Office 365 is the Advanced Audit feature. Currently I do not see anything specific to the Power Platform but this may change in the future. The key call out I see from the documentation is the ability to retain audit logs from one year and the high bandwidth accessibility for the Office 365 Management Activity API.
The other call out, is the throttling which is capped at 2,000 requests per minute.
Next Steps
 
In this article we have covered the Unified Audit Logs and what activities are currently captured for Power Apps Canvas Apps. Discussed were techniques to view the audit logs within the Office 365 Security and Compliance Portal as well as automated techniques using subscriptions and webhooks as well as automating PowerShell using Azure Automation. Consider the combination of using the Unified Audit Log to notify of events happening and the Power App Administration cmdlets to apply security and enrich audits from the audit log.
This article is designed to supplement the article on Power App Analytics, which provides more of an all up view of Canvas App usage. Combining these two documents, an administrator can now track analytic metrics as well as the events that define those metrics.
If you are interested in learning more about specialized guidance and training for monitoring or other areas of the Power Platform, which includes a monitoring workshop, please contact your Technical Account Manager or Microsoft representative for further details. 
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, please go here to submit a new issue.
Index
 
Monitoring the Power Platform: Introduction and Index
Let’s block ads! (Why?)
Dynamics 365 Customer Engagement in the Field

Tag Archives: Part

Assumptions

The Platform

The Demo

The Beginning

Pulling Your Image

Running the Container

Installing the SQL Server Module

Running Your First Query

Splatting

Creating Tables

Loading Data

Reading Data

Cleanup

Stopping and Starting Containers

Deleting a Container

Removing the Image

Conclusion

The Basics of Heaps

What are Heaps

The Structure of Heaps

Index Allocation Map

Page Free Space (PFS)

Conclusion

Advantages of Heaps

Disadvantages of Heaps

The Impact of Intense Disruption On Business Models: Strategic Alternatives (Part 2)

Sharelines

About Nitin Singh

Treatments, Vaccines, and All…That…Jaaaaazz.

Featured stories

The Good the Tech Industry Does…

The Good that People(s) Do

Useful Information

Best PowerPoint of the Week

Best Corona Cover of…What day is this?

Best Professional Effort of the…More than a Week

Cheating again…

And once again…SGN with John Krasinki

Right Tool, Right Job

Why Include Data Virtualization in Your Toolbox

Cloud Data Sharing is Driving Data Virtualization Demand

Challenges

Business process adoption trends

Business impact

The impact on XYZ’s business model

Stop playing the guessing game!

Viewing a Sales Forecast

nevAnalyze revenue outcome by using predictive forecasting

Additional Resources

How can we help?

Useful Information…Sorta

The Good Things Non-Tech Companies Do

The Good Things Tech Companies Do

The New Normal?

Best Corona Covers

Broadway

Pop/Rock

Best Original Not-Really-A-Corona-Cover

Best “We Are In This Together” Professional Zoom Music

What Can I Say? John Krasinski SGN #4 The Prom

CRM Playaz presents the Playaz Place Bar and Not Grill Happy Hour

Coronavirus Updates

Summary

Featured and Hero Apps

Using PowerShell to create Featured and Hero Apps

Install the Modules

Connect to Power Apps

Get Environment and Canvas App Identifiers

Set Canvas App as Featured or Hero App

Other Useful Power Apps Administration PowerShell Tasks

Activity Events for Canvas Apps

What events are audited?

Common Audit Properties

List of Events

Life Cycle Events

Launched Event

Edited App Permission Event

Next Steps

Index