Working with templates in Microsoft Lists

by Contributed | Jun 15, 2022 | Technology

This article is contributed. See the original author and article here.

You can save a lot of time using templates when creating new Microsoft Lists. List templates, both ready-made ones and your own custom templates, help keep your information tracking consistent – being able to reuse templates to uniformly create a common set of columns, formatting, views, rules, and more – all within one click – saving you from having to always configure from scratch.

Join Chris Kent and me (Mark Kashman) as we share templating insights and walk you through numerous demos in this new video below, “The broad world of templates when using Microsoft Lists” – watch it all or use its six main YouTube chapters to jump to the section that most speaks to you. And aligned to the video, I highlight everything you see and hear in six sections below, along with more explanation and links to learn more about each aspect of list templates. 

Take a scroll and learn more about working with templates in Microsoft Lists.

Start from blank to learn what can be applied and created in a template

The best way to understand what list templates do, or are available for you to design into your own custom templates, is to learn the parts and pieces of what templates can create for you when you use them:

• Columns | When you create a column for a list, you choose a column type that indicates the type of data that you want to store in the column, such as numbers only, formatted text, hyperlink, a choice column – templates support deploying any/all column types.


• Views | You can create a view to save different sort, filter, and grouping selections you’ve made from the column headers or filters pane. The columns you’ve shown or hidden, and the column widths will also be saved with the view.


• Formatting | You can use column, row, or view formatting to customize how lists are displayed. Think of this as color-coding specific fields/column or based on certain conditions. There is a lot you can do to configure and customize how your list data appears – and the real value is not having to do the same formatting over and over, just bake it into a list template and get on with the importance of tracking your information, the way you like to.


• Rules | You can create rules to automate actions such as sending someone a notification when data changes in the list. You’ll choose a condition that triggers the rule and the action that the rule will take. This, too, now includes the ability to include Power Automate flows as a part of what can get created and delivered by a list template.

Starting with a blank template is a great way to learn how to use Microsoft Lists, and to then understand what you can design into a template for the next list you create.

Learn more about getting started with Microsoft Lists – adding columns, creating views, adding rules, and more. I also created a video a little bit ago titled, “Microsoft Lists: The value of the Blank template”, that goes deeper into the beautiful abyss of the blank’ness of Lists around a specific scenario: A shareable list of resources :).

Ready-made templates

Microsoft’s built-in, ready-made templates help you create lists using pre-configured layouts that include columns, color formatting, and data structure. You can customize each list to fit your situation by changing the formatting to highlight important data, configuring forms to see more at a glance, and setting reminders to keep you and your team up to date on what’s happening. Our hope is that these templates will save you time and cover many of the common scenarios for using Microsoft Lists.

Choose from numerous ready-made templates available in Microsoft Lists as you click “New list.”

Learn more about ready-made templates for Microsoft Lists in Microsoft 365.

Create a list from another list

When you need a consistent and reliable approach to creating similar lists, you can create one from an existing list – especially ones you’ve tweaked just the way you like ‘em. When you do this, the entire list structure is copied, including views, formatting, and columns. However, the actual content of the original list isn’t copied.

Use the structure and formatting of another list to create a new list with the same schema. Above shows picking the list you wish to replicate in look and feel.

Learn more how to create a new list based on the columns in another list.

Design and deploy your own custom list templates

Sometimes you gotta roll up your sleeves and design what you need specific to your business. And with that, to be able to add it as a default template ‘from your organization’ that will be available to your peers and employees to use in your tenant – to save time and to organize and align with your preferred information tracking patterns.

Once you design the list characteristics to your preference, you then extract, package, and deploy the new using PowerShell.

Don’t miss Chris’ demo in the above video – using a great FAQs scenario – he goes into all the best practices for extracting the value from a list to create a custom, deployable list template.

Learn more about creating custom list templates. Note: You’ll need to be a global admin or SharePoint admin in Microsoft 365 and leverage the SharePoint Online Management Shell (built on top of PowerShell).

Microsoft 365 connected templates

One of the best things about SharePoint and Teams is how separately they provide world class collaboration, and when you combine them together that boundary disappears. With our newest Microsoft 365 connected templates, we are bringing both into one seamless creation flow – no matter where you start from.

Microsoft 365 templates combine Teams templates and SharePoint site templates – along with other app templates – so you more quickly, and consistently, start to your preference.

When we create this team using the template, the project management channels and apps, and the connected SharePoint template gets applied automatically. Critically, the pages, lists, and power platform integrations are pinned right here in Teams, and best of all these pages and lists are fully editable right in Teams.

This capability is not yet in production. You can track progress on “Microsoft 365 connected templates” on the Microsoft 365 public roadmap, ID: 82158. #ComingSoon

Lists MSA Preview – new consumer and small business-focused templates

Microsoft Lists – MSA Preview is a lightweight version of the Microsoft Lists app designed for small business and individual use in conjunction with your Microsoft account (MSA). It’s also a place where the team has deployed a few new list templates suitable for individuals and small businesses.

Two new templates only in the Lists – MSA Preview (as of June 2022):

• Gift ideas | Easily organize your gift planning for your family & friends in one list and make gift buying and giving a breeze.


• Expense tracker | Record all your personal and/or business expenses in one place.
  
  

When using lists.live.com (Microsoft Lists – MSA Preview), you’ll see new templates in the updated create list experience; the two new ones we cover in the video are ton he bottom-right.

Expect more templates to be released, both for consumer and commercial use.

Learn more the Microsoft Lists – MSA Preview and try it today – go to lists.live.com: sign up, sign in and let us know what you think.

Final thoughts…

We hope you’ve learned more about the value of using and creating list templates – bringing to your own productivity and the time saving, organizational benefits they bring to your business. To learn more about Lists throughout the year, please visit our Microsoft Lists resource center for blogs, demos, help content, videos, podcasts, and more. Plus, jump into the value of Formatting samples in our PnP GitHub repository.

Thanks, @Mark Kashman & @Chris Kent 

Building IoT apps using Postgres, Citus, and Azure

by Contributed | Jun 14, 2022 | Technology

This article is contributed. See the original author and article here.

If you work on an app that processes time-series data coming from devices & provides real-time rich insights regarding them, you may have come across below scalability requirements for your app:

• Support high throughput and low latency while ingesting billions of metrics from thousands to millions of IoT devices. 


• Support sub-second response times and high concurrency (100s of users) while powering real-time dashboards.

There’s a common perception that only NoSQL databases can provide that kind of scale. Citus on Azure breaks these barriers by enabling you to build large scale IoT apps within the PostgreSQL ecosystem.

• Due to Postgres, your IoT app gets a rich expressive SQL interface supporting updates, deletes, filters, joins, aggregations, extensions, and so on, along with data consistency & reliability guarantees.


• Due to the superpower of distributing tables across multiple servers and ability to scale-out seamlessly, your app gets the desired performance & scalability as it evolves. You can start small with single node & scale to multiple nodes seamlessly.

This blog will present an end-to-end reference architecture for building a scalable IoT app. Hyperscale (Citus) (a.k.a. Citus on Azure) within the Postgres managed service on azure is the database of choice. The blog also covers certain best practices & considerations you can keep in mind while building your IoT app.

Reference Architecture for IOT workloads on Azure 

Okay, let’s get started. First let us go through a high level walk-through of the various tools and services that come together in creating a reference architecture that enables you to build a scalable IoT app.

The main components of this reference architecture include:

1. Connecting your devices to the cloud with Azure IoT Hub


2. Real-time processing & ingestion of device data with Azure Databricks


3. Store and query time-series IoT data using Citus on Azure


4. Interactive visualizations on IoT data using Power BI and Grafana

Figure 1: A reference architecture to build scalable IoT apps using Postgres, Citus and Azure. This architecture depicts an end-to-end flow of how IoT data from devices enters the cloud via Azure IoT Hub and gets processed in Databricks before getting ingested into Postgres with Citus on Azure. Postgres with Citus is the scalable relational database which is used for storing device data and serving real-time dashboards via Grafana or Power BI (along with Azure Functions). This post focuses on how all these pieces come together to enable you to build large scale IoT apps within the Postgres ecosystem.

Connecting your devices to the cloud with Azure IoT Hub

Azure IoT Hub is a managed service in Azure which acts as a central message hub for bi-directional communication between your IoT application and the devices. You can use Azure IoT Hub to build IoT solutions with reliable and secure communications between  your IoT devices and a cloud-hosted solution backend. You can connect virtually any device to IoT Hub.

We’ve created a GitHub repo that lets you generate thousands of devices that are registered with IoT hub and send messages at scale. This repo enables you to simulate device data at scale and test IoT Hub.  You can also plug scripts inside this repo as a part of your CI/CD pipelines while building your IoT app.

Real-time processing & ingestion of device data with Azure Databricks

Azure Databricks can be used for processing & ingesting device data coming from Azure IoT Hub. Azure Databricks is a fault-tolerant stream processing engine with Kafka compatibility for continuous processing. You can use Spark Structured Streaming for both real-time ingestion and micro-batch processing of data.

The following code snippets show Databricks fetching device data from IoT Hub, processing it & ingesting it into Hyperscale (Citus) on Azure.

• Following Scala code snippet will listen to the stream coming from EventHub (or IoT hub) devices topic and create a spark DataFrame for further transformations. Make a note to import the library kafkashaded.org.apache.kafka.common.security.plain.PlainLoginModule. This module is installed by default on Databricks clusters.

import org.apache.spark.sql.functions._

val EventHubs_ConnectionString = "Endpoint=sb://***.servicebus.windows.net/;SharedAccessKeyName=twitter;SharedAccessKey= <shared_access_key>";
val constring = ""$ConnectionString"";
val splchar = """;
val EH_SASL = "kafkashaded.org.apache.kafka.common.security.plain.PlainLoginModule required username=" + "" + constring + "" + " password=" + splchar + EventHubs_ConnectionString + splchar + ";"; 
 
val df = spark
      .readStream
      .format("kafka") .option("subscribe", "<your topic>")
      .option("kafka.bootstrap.servers", "<iot_hub_name>.servicebus.windows.net:9093")
      .option("kafka.sasl.mechanism", "PLAIN")
      .option("kafka.security.protocol", "SASL_SSL")
      .option("kafka.sasl.jaas.config", EH_SASL)
      .option("kafka.request.timeout.ms", "60000")
      .option("kafka.session.timeout.ms", "30000")
      .option("kafka.group.id", "$Default")
      .option("failOnDataLoss", "true")
      .load() //Dataframe is loaded untill here

• Following Scala code snippet will write transformed Spark DataFrame to Hyperscale (Citus)

//Create your postgresql configurations
def postgresqlSinkOptions: Map[String, String] = Map(
    "dbtable" -> "public.devicedata", // table
    "user" -> "citus", // Database username
    "password" -> "<database password>", // Password
    "driver" -> "org.postgresql.Driver",
    "url" -> "jdbc:postgresql://<citus_server_group_name>.postgres.database.azure.com:5432/postgres",
    "sslmode" -> "require"
  )

//Writing device data to Citus/Postgres
df.writeStream
      .foreachBatch { (batchdf: DataFrame, _: Long) =>
        batchdf.write
          .format("jdbc")
          .options(postgresqlSinkOptions)
          .mode("Append")
          .save()
      }
      .start()
      .awaitTermination()

Using the Postgres COPY command for super-fast data ingestion

The above code triggers the Postgres INSERT command for ingesting data in batches. With batch INSERT, you can expect throughputs of a few 100s of thousand rows ingested per second. However if you wanted larger throughputs, you can use the Postgres COPY command.

COPY command lets you micro-batch rows periodically (can be as low as every 30 seconds) and ingest data in near real-time. With COPY, some of our customers have seen throughputs up to a few millions of rows ingested per second. However the throughput depends on the data-model (row width) & hardware configuration.

NOTE: Databricks’ JDBC based Postgres driver natively doesn’t support the COPY command. So we wrote a simple scala wrapper that extends the COPY command functionality to Databricks.

Store and query time-series IoT data using Citus on Azure

Citus is PostgreSQL extended with the superpower of distributed tables. This superpower enables you to build highly scalable relational apps. You can start building apps on a single node server group, the same way you would with PostgreSQL. As your app’s scalability and performance requirements grow, you can seamlessly scale to multiple nodes by transparently distributing your tables.

Below are a few best practices you can follow while building your app with Hyperscale (Citus):

• Distribution column: To distribute your tables, you’d need to a pick a column (a.k.a shard key) that decides how data is distributed across nodes. Shard key is the central piece and adds natural dimension to your data. In IoT applications, most of the times the distribution column is the identifier of your devices (say device_id). More on distributed data-modeling can be found in this link.


• Partitioning time-series data: As device data is all time series and has a time dimension, partition your tables based on time. Hyperscale (Citus) provides time series capabilities to create and manage partitions.


• JSONB to store device data: You can use columns with JSONB datatype to store and index semi-structured data. Device telemetry data is typically not structured, every device type has its own distinctive metrics.


• PostGIS for geospatial analysis: If your IoT app requires geospatial features, you can use the PostGIS extension, which Hyperscale (Citus) supports natively.


• Rollups (aka pre-aggregates) for fast analytics: You can use rollups to pre-aggregate raw data and avoid repetitive computations for queries. You can rollup raw data into minutely, hourly OR daily aggregates based on zoom-levels (based on time) in the dashboard. 

Interactive visualizations on IoT data using Power BI and Grafana

Power BI with real-time streaming lets you stream data and update dashboards in real time. Any visual or dashboard created in Power BI can display and update real-time data and visuals. As shown in the above reference architecture, you can use serverless Azure functions to periodically query device data in Hyperscale (Citus) and publish that to Power BI for real-time visualizations.

Grafana can be used for building time series dashboards or displaying Event Log (Operational Log) metrics based on time scale. By default, Grafana comes with PostgreSQL integration. For managed Grafana on the cloud you can use Azure Managed Grafana service, which recently went preview.

Get started with Postgres and Citus on Azure to build your own IoT app

To get a hands-on experience of Citus within 5 mins, you can walk through this Azure quickstart. As Citus is fully open source, you can easily download and test Citus on your local machine. Hope you found the blog useful, sharing a few informative links related to building time series apps with Citus.

• Use case guide on building time-series apps with Citus


• How to scale Postgres for time-series data with Citus?


• When to use Hyperscale (Citus)?


• Efficiently distributing Postgres with Citus – How to choose the right shard key?