Tech News - Databases

There’s been a lot of conversations happening on twitter over the last couple of days due to the fact that Docker is deprecated in Kubernetes v1.20. If you want to know more about the reason why I highly recommend checking out this twitter thread. I’ve recently built a Raspberry Pi Kubernetes cluster so I thought I’d run through updating them in-place to use containerd as the container runtime instead of Docker. DISCLAIMER – You’d never do this for a production cluster. For those clusters, you’d simply get rid of the existing nodes and bring new ones in on a rolling basis. This blog is just me mucking about with my Raspberry Pi cluster to see if the update can be done in-place without having to rebuild the nodes (as I really didn’t want to have to do that). So the first thing to do is drain the node (my node is called k8s-node-1) that is to be updated and cordon it:- kubectl drain k8s-node-1 --ignore-daemonsets Then ssh onto the node and stop the kubelet: – systemctl stop kubelet Then remove Docker: – apt-get remove docker.io Remove old dependencies: – apt-get autoremove Now unmask the existing containerd service (containerd is used by Docker so that’s why it’s already there): – systemctl unmask containerd Install the dependencies required:- apt-get install unzip make golang-go libseccomp2 libseccomp-dev btrfs-progs libbtrfs-dev OK, now we’re following the instructions to install containerd from source detailed here. I installed from source as I tried to use apt-get to install (as detailed here on the Kubernetes docs) but it wouldn’t work for me. No idea why, didn’t spend to much time looking and tbh, I haven’t installed anything from source before so this was kinda fun (once it worked). Anyway, doing everything as root, grab the containerd source: – go get -d github.com/containerd/containerd Now grab protoc and install: – wget -c https://github.com/google/protobuf/releases/download/v3.11.4/protoc-3.11.4-linux-x86_64.zip sudo unzip protoc-3.11.4-linux-x86_64.zip -d /usr/local Get the runc code: – go get -d github.com/opencontainers/runc Navigate to the downloaded package (check your $GOPATH variable) mine was set to ~/go so cd into it and use make to build and install: – cd ~/go/src/github.com/opencontainers/runc make make install Now we’re going to do the same thing with containerd itself: – cd ~/go/src/github.com/containerd/containerd make make install Cool. Now copy the containerd.service file to systemd to create the containerd service: – cp containerd.service /etc/systemd/system/ chmod 644 /etc/systemd/system/containerd.service And start containerd: – systemctl daemon-reload systemctl start containerd systemctl enable containerd Let’s confirm containerd is up and running: – systemctl status containerd Awesome! Nearly done, now we need to update the kubelet to use containerd as it defaults to docker. We can do this by running: – sed -i 's/3.2/3.2 --container-runtime=remote --container-runtime-endpoint=unix:///run/containerd/containerd.sock/g' /var/lib/kubelet/kubeadm-flags.env The flags for the kubelet are detailed here I’m using sed to append the flags to the cluster but if that doesn’t work, edit manually with vim:- vim /var/lib/kubelet/kubeadm-flags.env And the following flags need to be added: – –container-runtime=remote –container-runtime-endpoint=unix:///run/containerd/containerd.sock OK, now that’s done we can start the kubelet: – systemctl start kubelet And confirm that it’s working:- systemctl status kubelet N.B. – Scroll to the right and we can see the new flags Finally, uncordon the node. So back on the local machine:- kubectl uncordon k8s-node-1 Run through that for all the worker nodes in the cluster. I did the control node as well following these instructions (didn’t drain/cordon it) and it worked a charm! kubectl get nodes -o wide Thanks for reading! The post Updating my Kubernetes Raspberry Pi Cluster to containerd appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to join a friend doing their hobby and find out why they love it. Joining someone else isn’t really a good idea or very possible during this time. Colorado is slightly locked down, so it’s not necessarily legal, and likely not a good idea to join someone else. However, my daughter picked up some supplies and started knitting recently. I decided to sit with her a bit and see how the new hobby is progressing. It’s something I’ve been lightly interested in, and it looks somewhat zen to sit and allow your hands to move along, building something while you sit quietly. I remember reading about Rosey Grier picking up the hobby years ago. I have done some minor paracord crafts, usually making some bag pulls for the kids I coach. This was similar, and while I don’t need another hobby now, I enjoyed watching her work. The post Daily Coping 3 Dec 2020 appeared first on SQLServerCentral.

I am revisiting old T-SQL Tuesday invitations from the very beginning of the project. On May 3, 2010, Michael Coles invited us to write about how we use LOB data, so now you know what this week’s post is about. Let’s go over some definitions and complain a little, because that’s how cranky data professionals-> Continue reading T-SQL Tuesday Retrospective #006: What about blob? The post T-SQL Tuesday Retrospective #006: What about blob? appeared first on Born SQL. The post T-SQL Tuesday Retrospective #006: What about blob? appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to set aside regular time to pursue an activity you love. For me, I don’t know I love a lot of things, but I do enjoy guitar and I’ve worked on it a lot this year. The last month, I’ve let it go a bit, but I’m getting back to it. I try to remember to bring it downstairs to my office, and I’ll take some 5-10 minute breaks and play. I’ve also started to put together a little time on Sat am to work through a course, and build some new skills. The post Daily Coping 1 Dec 2020 appeared first on SQLServerCentral.

Introduction In my last blog post, I have addressed an issue with DAX Median function consuming a lot of memory. To refresh the result, below is the performance summary of those implementations. More on that topic you can find in this previous article. Performance for hour attribute (24 values) Duration (s) Memory Consumed (GB) Native median function 71.00 8.00 Custom implementation 1 6.30 0.20 Many2Many median implementation 2.20 0.02 Performance for location attribute (422 values) Duration (s) Memory Consumed (GB) Native median function 81.00 8.00 Custom implementation 1 107.00 2.50 Many2Many median implementation 41.10 0.08 It seems we have solved the issue with memory but still, the duration of this query when used with locations is not user-friendly. Today we will focus on the performance part. Tuning 3 – Improve User Experience I did not find how to improve the performance with some significant change of DAX or model. As such, I was thinking if we can somehow use aggregations for the median. MEASURESenzors[Calc_MedTempMap] =VAR _mep = [MedianPositionEven]VAR _mepOdd = [MedianPositionOdd]VAR _TempMedianTable = ADDCOLUMNS( values(TemperatureMapping[temperature]), “MMIN”,  [RowCount]– [TemperatureMappingRowCount] +1 , “MMAX”, [RowCount])        VAR _T_MedianVals =FILTER( _TempMedianTable ,( _mep >= [MMIN]&& _mep <= [MMAX])||( _mepOdd >= [MMIN]&& _mepOdd <= [MMAX]) )RETURNAVERAGEX( _T_MedianVals, [temperature]) The part highlighted is still the critical one having the biggest impact on performance because formula engine needs to do the following: – Iterate through all values we have on visual (for example location) – For each item take a list of temperatures – For each temperature get a cumulative count (sum of all counts of lower temperatures) Although we made faster a less expensive cumulative count, we are doing too many loops in the formula engine evaluating similar values again and again. What about to pre-calculate “_TempMedianTable” table so we don’t have to change the algorithm but just pick up cumulative counts as a materialized column? This is how the new model would look like: We can do the aggregation in the source system or we can do it even in Power BI, because we have less memory consumption. There are two helper tables: LocMedAgg – for analyses through a location. HourMedianAgg – for analyses by hour. Now we need to create an hour and location-specific measures and then one combined measure which will switch among them according to the current selection of attributes made by the user. This is DAX expression for LocMedAgg table: MEASURESenzors[LocMedAgg] =FILTER(SUMMARIZECOLUMNS(Senzors[location],TemperatureMapping[temperature],“TcountEndCount”, [RowCount],“TCountStart”,[RowCount] – [TemperatureMappingRowCount] + 1,“Cnt”, [TemperatureMappingRowCount]),— due to m2n relation we would have empty members we do not need and therefore let’s filter themNOT(ISBLANK([TemperatureMappingRowCount]))) New definition for hour Median measure is: —————————————————————— MEASURESenzors[HM_MedianPositionEven] =ROUNDUP(([HM_CountRows] / 2), 0) —————————————————————— MEASURESenzors[HM_MedianPositionOdd] =VAR _cnt = [HM_CountRows]RETURNROUNDUP(( _cnt / 2), 0) + ISEVEN( _cnt ) —————————————————————— MEASURESenzors[HM_Med] =VAR _mpe = [HM_MedianPositionEven]VAR _mpeOdd = [HM_MedianPositionOdd]VAR _T_MedianVals =FILTER( HourMedianAgg,VAR _max = HourMedianAgg[TcountEndCount]VAR _min = HourMedianAgg[TCountStart]RETURN( _mpe >= _min&& _mpe <= _max )||( _mpeOdd >= _min&& _mpeOdd <= _max ))RETURNAVERAGEX( _T_MedianVals, [temperature]) However, when we bring it into the visualization, we see the following issue: We are missing the total value. But that actually is no issue for us as we need to bring a context into the final calculation anyway, so we will compute the total value in a different branch of the final switch. We create the aggregated median measures for location the same way as for hour, and then we put it all together in the final median calculation that switches among different median helpers. For simplification, I wrapped the logic for each branch into a new measure, so the final calculation is simple: MEASURESenzors[CombinedMedian] =SWITCH(1 = 1,[UseHourMedian], [HM_Med_NoAgg],[UseLocationMedian], [LM_Med_NoAgg],[IsDateFiltered], [Orig_Med],[Calc_MedTempMap]) The switch above do this: If an hour and nothing else is selected use hour aggregation median calculation If location and nothing else is selected use location aggregation median If date attribute is selected use native median In all other cases use M2M median calculation Below is one of the switching measures: MEASURESenzors[IsDateFiltered] =— as I let engine to generate hierarchy for me I need to have this filter a bit complex to identify if any level of data is filteredISFILTERED(Senzors[Date].[Date])||ISFILTERED(Senzors[Date].[Day])||ISFILTERED(Senzors[Date].[Month])||ISFILTERED(Senzors[Date].[MonthNo])||ISFILTERED(Senzors[Date].[Quarter])||ISFILTERED(Senzors[Date].[QuarterNo])||ISFILTERED(Senzors[Date].[Year]) MEASURESenzors[UseHourMedian] = ISFILTERED(‘Hour'[Hour])&&NOT(ISFILTERED(Location[Location])) &&NOT([IsDateFiltered]) And that’s it! Now we have solution where you get median under one second for major dimensions. You can download sample pbx from here. The post Power BI – Hungry Median – Aggregations appeared first on SQLServerCentral.

You can register now, but on Dec 3, I have a webinar with Microsoft on database devops and Azure. This is a joint effort with Redgate, so I’ll be showing some of our tools. In the webinar, which I recorded last week, I walk through the idea of Database DevOps in general, showing how we evolve our database development to incorporate automation, use Azure DevOps, and deploy code to an Azure database. Register today, and I’ll be ready for questions on Dec 3. The post Database DevOps Deployment to Azure–Webinar appeared first on SQLServerCentral.

Server-Level Roles SQL Server security is like a box of chocolates. Wait, it is more like an onion – with all of the layers that get to be peeled back. One of the more important layers, in my opinion, is the layer dealing with Roles. I have written about the various types of roles on several occasions. Whether it be Fixed Server Role memberships, Fixed Server Role permissions, or Database Roles permissions (among several options), you can presume that I deem the topic to be of importance. Within the “Roles” layer of the SQL Server security onion, there are multiple additional layers (as alluded to just a moment ago) such as Database Roles and Server Roles. Focusing on Server Roles, did you know there are different types of Server Roles? These types are “fixed roles” and “custom roles.” Today, I want to focus on the custom type of role. Custom Server Roles Starting with SQL Server 2014, we were given a new “feature” to help us minimize our security administration efforts. The new “feature” is that which allows a data professional to create a “Server Role” in SQL Server and to grant specific permissions to that role. I wrote about how to take advantage of this in the 2014 recipes book I helped to author, but never got around to creating an article here on how to do it. In this article, I will take you through a quick example of how to take advantage of these custom roles. First let’s create a login principal. This principal is a “login” so will be created at the server level. Notice that I perform an existence check for the principal before trying to create it. We wouldn’t want to run into an ugly error, right? Also, when you use this script in your environment, be sure to change the DEFAULT_DATABASE to one that exists in your environment. While [] is an actual database in my environment, it is highly unlikely it exists in yours! USE [master]; GO IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'Gargouille' ) BEGIN CREATE LOGIN [Gargouille] WITH PASSWORD = N'SuperDuperLongComplexandHardtoRememberPasswordlikePassw0rd1!' , DEFAULT_DATABASE = [] , CHECK_EXPIRATION = OFF , CHECK_POLICY = OFF; END; Next, we want to go ahead and create a custom server level role. Once created, we will grant specific permissions to that role. --check for the server role IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'SpyRead' AND type_desc = 'SERVER_ROLE' ) BEGIN CREATE SERVER ROLE [SpyRead] AUTHORIZATION [securityadmin]; GRANT CONNECT ANY DATABASE TO [SpyRead]; GRANT SELECT ALL USER SECURABLES TO [SpyRead]; END; As you can see, there is nothing terrifyingly complex about this so far. The statements should be pretty familiar to the data professional and they are fairly similar to routine tasks performed every day. Note in this second script that after I check for the existence of the role, I simply use “CREATE SERVER ROLE” to create the role, then I add permissions explicitly to that role. Now, I will add the login “Gargouille” to the Server Role “SpyRead”. In addition to adding the login principal to the role principal, I will validate permissions before and after – permissions for Gargouille that is. EXECUTE AS LOGIN = 'Gargouille' GO USE []; GO SELECT * FROM fn_my_permissions(NULL, 'DATABASE') fn; REVERT USE master; GO IF NOT EXISTS ( SELECT mem.name AS MemberName FROM sys.server_role_members rm INNER JOIN sys.server_principals sp ON rm.role_principal_id = sp.principal_id LEFT OUTER JOIN sys.server_principals mem ON rm.member_principal_id = mem.principal_id WHERE sp.name = 'SpyRead' AND sp.type_desc = 'SERVER_ROLE' AND mem.name = 'Gargouille' ) BEGIN ALTER SERVER ROLE [SpyRead] ADD MEMBER [Gargouille]; END; EXECUTE AS LOGIN = 'Gargouille' GO USE []; GO SELECT * FROM fn_my_permissions(NULL, 'DATABASE') fn; REVERT We have a few more things happening in  this code snippet. Let’s take a closer look and break it down a little bit. The first section tries to execute some statements as “Gargouille”. When this attempt is made, there is an error that is produced – which is good because it validates the principal does not have permission to connect to the requested database. The next statement of  interest adds the “Gargouille” principal to the SpyRead Server role. After the principal is added to the custom server role, I attempt to impersonate the “Gargouille” principal again and connect to the database and run a permissions check. These are the results from that last query. Lastly, I run a check to validate that Gargouille is indeed a member of the server role “SpyRead” – which it is. From these results we can see the power of the customer server role. In this case, I had a user that “needed” to access every database on the server. Instead of granting permissions on each database one by one, I granted the “Connect” (and a couple of other permissions to be discussed in the follow-up article) to the server role and then added Gargouille to that role. This reduced my administration time requirement quite a bit – more if there are hundreds of databases on the server. In the follow-up article, I will show how this will help to make it easier to grant a user the ability to view schema definitions as well as read from every database with one fell swoop. Stay tuned! Wrapping it Up In this article, I have shown how to use the power of custom server roles to help reduce your administration time. The custom security role is like using a security group to grant a bunch of people the same sets of permissions. When you use a security group to manage multiple people, it makes administration very much like you have offloaded the job to somebody else to do because it becomes that easy! Now it is your turn, take what you have learned in this article and see how you could apply it within your environment to help you be a rockstar data professional. Feel free to explore some of the other Back to Basics posts I have written. Are you interested in more articles showing what and how to audit? I recommend reading through some of my auditing articles. Feeling like you need to explore more about the security within SQL Server, check out this library of articles here. Related Posts: SQL Server User Already Exists - Back to Basics January 24, 2018 Quick Permissions Audit January 14, 2019 When Too Much is Not a Good Thing December 13, 2019 Easy Permissions Audit January 21, 2019 Cannot Use the Special Principal - Back to Basics November 7, 2018 The post Server-Level Roles - Back to Basics first appeared on SQL RNNR. The post Server-Level Roles – Back to Basics appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to connect with someone from a different generation. I used to always view a different generation as an older one. Now as I age, with more life behind me than in front, I tend to think about younger generations more. We did some speed networking recently at work, as a way to connect and meet other people. I ended up with people I know in two of the three sessions, but in the third, I ran into a former intern that was hired back during the pandemic. It was a neat 10 minute conversation, hearing some of his experiences on how things are different, his experience and impressions of working with his team inside the company. I enjoyed it, and it brought me back to my first job after college, and how different the experience was, both in life and at work. I don’t know if he enjoyed it as I did, but I really appreciated the opportunity and perspective. I hope I get to see him again, and also get to do more speed networking. The post Daily Coping 25 Nov 2020 appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to broaden your perspective: read a different paper, magazine, or site. Many of us tend to get caught in bubbles of information. Google/Bing do this, bringing us certain results. We have a set group of people on social media, and often we read the same sites for news, information, fun, etc. The US election took place recently, and it was a contentious one. Like many, I was entranced with the process, the outcome, and the way the event unfolded. I have some sites for news, and a subscription, but I decided to try something different since I was hoping for other views. In particular, I gave CNN a try at CNN.com. I haven’t been thrilled with their television program for years, as I think they struggle to find new and interesting things to day and still fill 24 hours. However, I saw some ratings about how people around the world view the site, and it’s fairly neutral. I also grabbed a new book, Dirt, after hearing someone talk about it on NPR. It’s a book by a writer that moves to France to learn French cooking. A little out of my area, and I struggled to get into this book, but eventually it turned a corner, and I enjoyed hearing about how the writer blends cooking, chefs, ingredients, and life in a foreign country. The post Daily Coping 27 Nov 2020 appeared first on SQLServerCentral.

In this month’s Power BI Digest Matt and I will again guide you through some of the latest and greatest Power BI updates this month. In our November 2020 edition we highlighted the following features: New Field and Model View (Preview) Filters Pane – Apply all filters button Data Lasso now available in maps Visual Zoom Slider Anomaly Detection (Preview) The post Power BI Monthly Digest – November 2020 appeared first on SQLServerCentral.

I will be speaking at two upcoming PASS SQLSaturday’s. These are free events that you can attend virtually: Azure Synapse Analytics: A Data Lakehouse 12/5/20, 1:10pm EST, PASS SQL Saturday Atlanta BI (info) Azure Synapse Analytics is Azure SQL Data Warehouse evolved: a limitless analytics service that brings together enterprise data warehousing and Big Data analytics into a single service. It gives you the freedom to query data on your terms, using either serverless on-demand or provisioned resources, at scale. Azure Synapse brings these two worlds together with a unified experience to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs. In this presentation, I’ll talk about the new products and features that make up Azure Synapse Analytics and how it fits in a modern data warehouse, as well as provide demonstrations. (register) (full schedule) How to build your career 12/12/20, 4:30pm EST, PASS SQL Saturday Minnesota, (info) (slides) In three years I went from a complete unknown to a popular blogger, speaker at PASS Summit, a SQL Server MVP, and then joined Microsoft.  Along the way I saw my yearly income triple.  Is it because I know some secret?  Is it because I am a genius?  No!  It is just about laying out your career path, setting goals, and doing the work. I’ll cover tips I learned over my career on everything from interviewing to building your personal brand.  I’ll discuss perm positions, consulting, contracting, working for Microsoft or partners, hot fields, in-demand skills, social media, networking, presenting, blogging, salary negotiating, dealing with recruiters, certifications, speaking at major conferences, resume tips, and keys to a high-paying career. Your first step to enhancing your career will be to attend this session! Let me be your career coach! (register) (full schedule) The post PASS SQLSaturday's I will be speaking at first appeared on James Serra's Blog. The post PASS SQLSaturday’s I will be speaking at appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to tell someone how you’re grateful for them in your life. It’s Thanksgiving, a day traditionally in the US where we give some thanks. Lots of people do this all month, picking something each day. My family does this at dinner, each of us giving a few things we’ve been thankful for this year. However, this is something you ought to do regularly with those in your life for whom you are grateful. Partners, kids, parents, boss, employee, etc. Remember to appreciate things, and people, in your life. I do try to let some people know that I an grateful, but not enough. I’d hope I remember to do this monthly, if not more often. There are a lot of people I am grateful for. In this case, for today, I’m remembering to tell me wife how grateful I am to have her in my life, for all the help, support, love, and joy she brings. The post Daily Coping 26 Nov 2020 appeared first on SQLServerCentral.

This week we are going to launch a blog series geared towards folks that are new to PowerShell. The growing popularity of automation is seeing people getting started with the PowerShell scripting language. The Start-Transcript is a built-in command that allows you to quickly build a log of actions being taken by your script. There are other ways to build logs, but for beginners using the commands that are available to you is pretty easy. Getting Started Using the command is easy to get started and incorporate into your process. The example below will show you how to use the command and see the output that is captured. Start-Transcript -Path "C:LoggingDemo_Logging.log" Get-Process Stop-Transcript   Here you can see in the log that was created, it captures run time information and then starts tracking commands that are executed. With this simple example, you can see how beneficial this option can be for tracking and troubleshooting execution issues. Additional Features There are a few parameters that come along with this command that allow you to make your logging more scalable. NoClobber = This parameter allows the command to not overwrite the log file if you are using the same name Append = This parameter will add anything new to the log file that is executed each time your script runs References Start-Transcript on Microsoft Docs     The post [PowerShell Newbie] Using Start-Transcript appeared first on GarryBargsley.com. The post [PowerShell Newbie] Using Start-Transcript appeared first on SQLServerCentral.

Many conferences have moved online this year due to the pandemic, and many attendees are expecting captions on videos (both live and recorded) to help them understand the content. Captions can help people who are hard of hearing, but they also help people who are trying to watch presentations in noisy environments and those who lack good audio setups as they are watching sessions. Conferences arguably should have been providing live captions for the in-person events they previously held. But since captions are finally becoming a wider a topic of concern, I want to discuss how captions work and what to look for when choosing how to caption content for an online conference. There was a lot of information that I wanted to share about captions, and I wanted it to be available in one place. If you don’t have the time or desire to read this post, there is a summary at the bottom. Note: I’m not a professional accessibility specialist. I am a former conference organizer and current speaker who has spent many hours learning about accessibility and looking into options for captioning. I’m writing about captions here to share what I’ve learned with other conference organizers and speakers. Closed Captions, Open Captions, and Subtitles Closed captions provide the option to turn captions on or off while watching a video. They are usually shown at the bottom of the video. Here’s an example of one of my videos on YouTube with closed captions turned on. YouTube video with closed captions turned on. The CC button at the bottom has a red line under it to indicate the captions are on. The placement of the captions may vary based upon the service used and the dimensions of the screen. For instance, if I play this video full screen on my wide screen monitor, the captions cover some of the content instead of being shown below. Open captions are always displayed with the video – there is no option to turn them off. The experience with open captions is somewhat like watching a subtitled foreign film. But despite captions often being referred to colloquially as subtitles, there is a difference between the two. Captions are made for those who are hard of hearing or have auditory processing issues. Captions should include any essential non-speech sound in the video as well as speaker differentiation if there are multiple speakers. Subtitles are made for viewers who can hear and just need the dialogue provided in text form. For online conferences, I would say that closed captions are preferred, so viewers can choose whether or not to show the captions. How Closed Captions Get Created Captions can either be created as a sort of timed transcript that gets added to a pre-recorded video, or they can be done in real time. Live captioning is sometimes called communication access real-time translation (CART). If you are captioning a pre-recorded video, the captions get created as a companion file to your video. There are several formats for caption files, but the most common I have seen are .SRT (SubRip Subtitle), .VTT (Web Video Text Tracks). These are known as simple closed caption formats because they are human readable – showing a timestamp or sequence number and the caption in plain text format with a blank line between each caption. Who Does the Captions There are multiple options for creating captions. The first thing to understand is that captioning is a valuable service and it costs money and/or time. In general, there are 3 broad options for creating captions on pre-recorded video: Authors or conference organizers manually create a caption file Presentation software creates a caption file using AI A third-party service creates a caption file with human transcription, AI, or a combination of both Manually creating a caption file Some video editing applications allow authors to create caption files. For example, Camtasia provides a way to manually add captions or to upload a transcript and sync it to your video. Alternatively, there is a VTT Creator that lets you upload your video, write your captions with the video shown so you get the timing right, and then output your .VTT file. Another approach is to use text-to-speech software to create a transcript of everything said during the presentation and then edit that transcript into a caption file. Services like YouTube offer auto-captioning, so if it’s an option to upload as a private video to get the caption file from there, that is a good start. But you will need to go back through and edit the captions to ensure accuracy with either of these approaches. Vimeo also offers automatic captioning, but the results will also need to be reviewed and edited for accuracy. These are valid approaches when you don’t have other options, but they can be very time consuming and the quality may vary. This might be ok for one short video, but is probably not ideal for a conference. If you are going to make presenters responsible for their own captions, you need to provide them with plenty of time to create the captions and suggest low-cost ways to auto-generate captions. I’ve seen estimates that it can take up to 5 hours for an inexperienced person to create captions for one hour of content. Please be aware of the time commitment you are requesting of your presenters if you put this responsibility on them. Captions in Your Presentation Software Depending on the platform you use, your presentation software might provide AI-driven live captioning services. This is also known as Automatic Speech Recognition (ASR). For example, Teams offers a live caption service. As of today (November 2020), my understanding is that Zoom, GoToMeeting, and GoToWebinar do not offer built-in live caption services. Zoom allows you to let someone type captions or integrate with a 3rd party caption service. Zoom and GoToMeeting/GoToWebinar do offer transcriptions of meeting audio after the fact using an AI service. PowerPoint also offers live captioning via its subtitles feature. My friend Echo made a video and blog post to show the effectiveness of PowerPoint subtitles, which you can view here. There are a couple of things to note before using this PowerPoint feature: It only works while PowerPoint is in presentation mode. If you have demos or need to refer to a document or website, you will lose captions when you open the document or web browser. If you are recording a session, your subtitles will be open subtitles embedded into your video. Viewers will not be able to turn them off. The captions will only capture the audio of the presenter who is running the PowerPoint. Other speakers will not have their voice recorded and will not be included in the captions. Google Slides also offers live captions. The same limitations noted for PowerPoint apply to Google Slides as well. Third-Party Caption Services There are many companies that provide captioning services for both recorded and live sessions. This can be a good route to go to ensure consistency and quality. But all services are not created equal – quality will vary. For recorded sessions, you send them video files and they give you back caption files (.VTT, .SRT, or another caption file format). They generally charge you per minute of content. Some companies offer only AI-generated captions. Others offer AI- or human-generated captions, or AI-generated captions with human review. Humans transcribing your content tends to cost more than AI, but it also tends to have a higher accuracy. But I have seen some impressively accurate AI captions. Captions on recorded content are often less expensive than live captions (CART). Below are a few companies I have come across that offer caption services. This is NOT an endorsement. I’m listing them so you can see examples of their offerings and pricing. Most of them offer volume discount or custom pricing. Otter.ai – offers AI-generated captions for both recorded and live content, bulk import/export, team vocabulary 3PlayMedia – offers AI-generated and human-reviewed captions for recorded content, AI-generated captions for live content. (Their standard pricing is hidden behind a form, but it’s currently $0.60 per minute of live auto-captioning and $2.50 per minute of closed captions for recorded video.) Rev – offers captions for both recorded and live content, shared glossaries and speaker names to improve accuracy. The Described and Captioned Media Program maintains a list of captioning service vendors for your reference. If you have used a caption service for a conference and want to share your opinion to help others, feel free to leave a comment on this post. Questions for Conference Organizers to Ask When Choosing a Captioning Vendor For recorded or live video: What is your pricing model/cost? Do you offer bulk discounts or customized pricing? Where/how will captions be shown in my conference platform? (If it will overlay video content, you need to notify speakers to adjust content to make room for it. But try to avoid this issue where possible.) Is there an accuracy guarantee for the captions? How is accuracy measured? Can I provide a list of names and a glossary of technical terms to help improve the caption accuracy? Does the captioning service support multiple speakers? Does it label speakers’ dialogue to attribute it to the right person? Does the captioning service conform to DCMP or WCAG captioning standards? (Helps ensure quality and usability) How does the captioning service keep my files and information secure (platform security, NDAs, etc.)? What languages does the captioning service support? (Important if your sessions are not all in English) For recorded video: Does my conference platform support closed captions? (If it doesn’t, then open captions encoded into the video will be required.) What file type should captions be delivered in to be added to the conference platform? What is the required lead time for the captioning service to deliver the caption files? How do I get videos to the caption service? For captions on live sessions: Does the live caption service integrate with my conference/webinar platform? How do I get support if something goes wrong? Is there an SLA? What is the expected delay from the time a word is spoken to when it appears to viewers? Further Captioning Advice for Conference Organizers Budget constraints are real, especially if you are a small conference run by volunteers that doesn’t make a profit. Low quality captions can be distracting, but no captions means you have made a decision to exclude people who need captions. Do some research on pricing from various vendors, and ask what discounts are available. You can also consider offering a special sponsorship package where a sponsor can be noted as providing captions for the conference. If you are running a large conference, this should be a line item in your budget. Good captions cost money, but that isn’t an excuse to go without them. If your conference includes both live and recorded sessions, you can find a vendor that does both. You’ll just want to check prices to make sure they work for you. If your budget means you have to go with ASR, make sure to allow time to review and edit closed captions on recorded video. Try to get a sample of the captions from your selected vendor to ensure quality beforehand. If possible for recorded videos, allow speakers to preview the captions to ensure quality. Some of them won’t, but some will. And it’s likely a few errors will have slipped through that can be caught and corrected by the speakers or the organizer team. This is especially important for deeply technical or complex topics. Make sure you have plenty of lead time for recorded videos. If a speaker is a few days late delivering a video, make sure their video can still be captioned and confirm if there is an extra fee. Final Thoughts and Recap If you’d like more information about captions, 3PlayMedia has an Ultimate Guide to Closed Captioning with tons of good info. Feel free to share any tips or tricks you have for captioning conference sessions in the comments. I’ve summarized the info in this post below for quick reference. Terms to Know Closed captions: captions that can be turned on and off by the viewer Open captions: captions that are embedded into the video and cannot be turned off CART: communication access real-time translation, a technical term for live captioning ASR: automatic speech recognition, use of artificial intelligence technology to generate captions .SRT and .VTT: common closed caption file formats Choosing a Captioning Solution for Your Conference (Click to enlarge) This diagram represents general trends and common decision points when choosing a captioning solution. Your specific situation may vary from what is shown here Summary of Caption Solutions Manual creation of caption files for recorded sessionsCost: NoneTime/Effort: HighPros: • Doesn’t require a third-party integration• Supports closed captions• Works no matter what application is shown on the screen• Works not matter what application is used to record and edit videoCons:• Accuracy will vary widely• Manual syntax errors can cause the file to be unusable Upload to YouTube, Vimeo or another service that offers free captionsCost: None to LowTime/Effort: MediumPros:• Supports closed captions• Works no matter what application is shown on the screen• Works no matter what application is used to record and edit videoCons:• Not available for live sessions• Requires editing of captions to achieve acceptable accuracy• Requires an account with the service and (at least temporary) permission to upload the video• Accuracy will vary widely Auto-generated captions in presentation software (e.g., PowerPoint, Google Slides)Cost: LowTime/Effort: LowPros: • Works for live and recorded sessions• No third-party integrations requiredCons:• Requires that all presenters use presentation software with this feature• Must be enabled by the presenter• Won’t work when speaker is showing another application• Often offers only open captions• Accuracy may vary• Often only captures one speaker ASR (AI-generated) captions from captioning serviceCost: MediumTime/Effort: LowPros: • Works for live and recorded sessions• Supports closed captions• Works no matter what application is shown on the screen• Works not matter what application is used to record and edit videoCons: • Accuracy may vary• Requires planning to meet lead times for recorded sessions• Poor viewer experience if delay is too large during live sessions Human-generated or human-reviewed captions from a captioning serviceCost: HighTime/Effort: LowPros: • Ensures the highest quality with the lowest effort from conference organizers and speakers• Works for live and recorded sessions• Works no matter what application is shown on the screen• Works not matter what application is used to record and edit videoCons: • Requires planning to meet lead times for recorded sessions• Poor viewer experience if delay is too large during live sessions I hope you find this exploration of options for captions in online conference content helpful. Let me know in the comments if you have anything to add to this post to help other conference organizers. The post Captioning Options for Your Online Conference appeared first on SQLServerCentral.

Introduction Median is a useful statistical function, which first time appeared in SSAS 2016 and in Power BI around that year as well. There are several articles on how to implement the median function in DAX from the time before the native DAX function was introduced. With one client we recently faced an issue when using the implicit median function in Power BI. Size of the dataset was roughly 30mio records. I would say nothing challenging for Power BI or DAX itself. However, the behavior of the median function was not convincing at all. Let’s look at the setup: I created a median dataset based on free data from weather sensors in one city (a link to download at the end of the blog) which has similar data characteristics as our report with the original issue. We have the following attributes: date, hour, location (just numeric ID of location which is fine for our test) and we are monitoring the temperature. We have 35mio records -> 944 unique records for temperature, 422 unique locations, and 24 hours of course. Now we make a simple report – we would like to see the median for temperature per hour despite date or location. Measure: MEASURESenzors[Orig_Med] =MEDIAN(Senzors[temperature]) The following result took 71 seconds to complete on the dataset in PB desktop. And took almost 8GB of memory:: Memory profile during the DAX query: If you try to publish this report to Power BI service, you will get the following message: I was just WOW! But what can I tune on such a simple query and such a simple measure? Tunning 1 – Rewrite Median? I was a bit disappointed about the median function. When we used date for filtering, the performance of the query was ok. But when we used a larger dataset it was not performing at all. I do know nothing about the inner implementation of the median function in DAX but based on memory consumption it seems like if there would be column materialization on the background and sorting when searching for the median. Here’s a bit of theory about median and a bit of fact about columnar storage so we can discover how we can take advantage of the data combination/granularity we have in the model. Below are two median samples for a couple of numbers – when the count of the numbers is Even and when is Odd. More median theory on Wikipedia. The rules for calculating median are the same, even when numbers in the set are repeating (non-unique). Here are the steps of the potential algorithm: Sort existing values. Find the median position(s). Take a value or two and make average to get median. Let’s look at this from the perspective of column store where we have just a couple of values with hundreds of repeats. As we know the count is very fast for column store and that could be our advantage as we have a small number of unique values repeated many times. Following is an example of data where we can visualize the way how we can take advantage of the fact described above. Temperature Count Cumulative Count Cumulative Count Start 12 500 500 1 13 500 1000 501 18 500 1500 1001 20 501 2001 1501 Total Count 2001     Position of median Odd 1001     Position of median Even 1001     In this case, we just need to go through 4 values and find in which interval our position of median belongs. In the worst-case scenario, we will hit between two values like on the following picture (we changed the last count from 501 to 500): Temperature Count Cumulative Count Cumulative Count Start 12 500 500 1 13 500 1000 501 18 500 1500 1001 20 500 2000 1501 Total Count 2000     Position of median Odd 1000     Position of median Even 1001     How to implement this in DAX: First helper measures are count and cumulative count for temperature: MEASURESenzors[RowCount] =COUNTROWS( Senzors ) MEASURESenzors[TemperatureRowCountCumul] =VAR _curentTemp = MAX ( ‘Senzors'[temperature] ) RETURN CALCULATE (        COUNTROWS ( Senzors ), Senzors[temperature] <= _curentTemp) Second and third measures give us a position of the median for given context: MEASURESenzors[MedianPositionEven] =ROUNDUP((COUNTROWS( Senzors ) / 2), 0) MEASURESenzors[MedianPositionOdd] =VAR _cnt =COUNTROWS( Senzors )RETURNROUNDUP(( _cnt / 2), 0)— this is a trick where boolean is auto-casted to int (0 or 1) + ISEVEN( _cnt ) The fourth measure – Calculated median – does what we described in the tables above. Iterate through temperature values and find rows that contain median positions and make average on that row(s). MEASURESenzors[Calc_Med] =— get two possible position of medianVAR _mpe = [MedianPositionEven]VAR _mpeOdd = [MedianPositionOdd]— Make Temperature table in current context with Positions where value starts and finishesVAR _TempMedianTable =ADDCOLUMNS(VALUES(Senzors[temperature]),“MMIN”,[TemperatureRowCountCumul] – [RowCount] + 1,“MMAX”, [TemperatureRowCountCumul])— Filter table to keep only values which contains Median positions in itVAR _T_MedianVals =FILTER( _TempMedianTable,(_mpe >= [MMIN]&& _mpe <= [MMAX])||(_mpeOdd >= [MMIN]&& _mpeOdd <= [MMAX]))— return average of filtered dataset (one or two rows)RETURNAVERAGEX( _T_MedianVals, [temperature]) Maximum number of rows which goes to the final average is 2. Let us see the performance of such measure: Performance for Hour (24 values) Duration (s) Memory Consumed (GB) Native median function 71 8 Custom implementation 6.3 0.2 Sounds reasonable and promising! But not so fast – when the number of values by which we group the data grow, the duration grows as well. Here are some statistics when removing hour (24 values) and bringing location (400+ values) into the table. Performance for location (422 values) Duration (s) Memory Consumed (GB) Native Median Function 81 8 Custom Implementation 107 2.5 Look at the memory consumption profile of calculated median for location below: That is not so good anymore! Our custom implementation is a bit slower for location and despite the fact it is consuming a lot less memory, this will not work in Power BI service as well. This means that we solved just a part of the puzzle – our implementation is working fine only when we have a small number of values that we are grouping by. So, what are the remaining questions to make this report working in PBI service? How to improve the overall duration of the query? How to decrease memory consumption? Tuning 2 – Reduce Memory Consumption We start with the memory consumption part. First, we need to identify which part of the formula is eating so much memory. Actually, it is the same one that has the most performance impact on the query. It’s this formula for the cumulative count, which is evaluated for each row of location multiplied by each value of temperature: MEASURESenzors[TemperatureRowCountCumul] =VAR _curentTemp = MAX ( ‘Senzors'[temperature] ) RETURN CALCULATE (        COUNTROWS ( Senzors ), Senzors[temperature] <= _curentTemp) Is there a different way to get a cumulative count without using CALCULATE? Maybe a more transparent way for the PB engine? Yes, there is! We can remodel the temperature column and define the cumulative sorted approach as a many-to-many relationship towards the sensors. Sample content of temperature tables would look like this: I believe that the picture above is self-describing. As a result of this model, when you use the temperature attribute from the TemperatureMapping table, you have: – Cumulative behavior of RowCount. – Relation calculated in advance. For this new model version, we define measures as below: RowCount measure we have already, but with temperature from Mapping table, it will give us CumulativeCount in fact. MEASURESenzors[RowCount] =COUNTROWS( Senzors ) We must create a new measure which will give us a normal count for the mapping table to be able to calculate the starting position of the temperature value: MEASURESenzors[TemperatureMappingRowCount] =CALCULATE([RowCount],FILTER( TemperatureMapping,TemperatureMapping[LowerTemperature] = TemperatureMapping[temperature])) New median definition: MEASURESenzors[Calc_MedTempMap] =VAR _mpe = [MedianPositionEven]VAR _mpeOdd = [MedianPositionOdd]VAR _TempMedianTable =ADDCOLUMNS(VALUES(TemperatureMapping[temperature]),“MMIN”,[RowCount] – [TemperatureMappingRowCount] + 1,“MMAX”, [RowCount])VAR _T_MedianVals =FILTER( _TempMedianTable,(_mpe >= [MMIN]&& _mpe <= [MMAX])||(_mpeOdd >= [MMIN]&& _mpeOdd <= [MMAX]))RETURNAVERAGEX( _T_MedianVals, [temperature]) Alright, let’s check the performance – the memory consumption is now just in MBs! Performance Many2Many Median Duration (s) Memory Consumed (GB) Used with Hours 2.2 0,02 Used with location 41.1 0,08 I think we can be happy about it and the memory puzzle seems to be solved. You can download a sample PBI file (I decreased data to only one month of the data, but you can download the whole dataset). Below is the statistics summary for now: Performance for Hour (24 values) Duration (s) Memory Consumed (GB) Native median function 71.00 8.00 Custom implementation 6.30 0.20 Many2Many median 2.20 0.02 Performance for Location (422 values) Duration (s) Memory Consumed (GB) Native median function 81.00 8.00 Custom implementation 1 107.00 2.50 Many2Many median 41.10 0.08 I’ll stop this blog here, as it is too long already. Next week, I’ll bring the second part in regards to how to improve performance, so the user has a better experience while using this report. The post Power BI – Hungry Median appeared first on SQLServerCentral.