2. Creation of MonetDB Database

General architecture

MonetDB database is presented with MonetDB daemon (service). This daemon can control several servers. Each server is defined inside of one directory that we create. Common name for such directories is “dbfarm”. Beside configuration files for that server, all of the databases of that server will also be placed inside of subfolders of dbfarm directory.

Server creation

When we want to create new server, first thing is to create folder for that server.

-> monetdbd create /home/fffovde/DBfarm1

Inside of that folder a new file with the name “.meroviginian_properties” will be created. This file will have properties for our database.

The Merovingian dynasty was the ruling family of the Franks from the mid-5th century until 751. This dynasty ruled the Netherlands, the country from which MonetDB originates. MonetDB is using this term for some of its internal files and commands.

If we take a look inside of this file, we will find only one property:

-> cat .merovingian_properties
# DO NOT EDIT THIS FILE - use monetdb(1) and monetdbd(1) to set properties
# This file is used by monetdbd
control=false

All other properties are using default values. We can read those default values by command:

> monetdbd get all /home/fffovde/DBfarm1
property
hostname
dbfarm
status
mserver
logfile
pidfile
loglevel
sockdir
listenaddr
port
exittimeout
forward
discovery
discoveryttl
control
passphrase
snapshotdir
snapshotcompression
mapisock
controlsock
value
FffOvdeKomp
/home/fffovde/DBfarm1
no monetdbd is serving this dbfarm
unknown (monetdbd not running)
/home/fffovde/DBfarml/merovingian.log
/home/fffovde/DBfarml/merovingian.pid
information
/tmp
localhost
/50000
60
proxy
true
600
no
<unset>
<unset>
.tar.lz4
/tmp/.s.monetdb.50000
/tmp/.s.merovingian.50000

***********************************************

Monetdb daemon will use default port 50000. It is possible to have several Monetdb daemons. Their port numbers could collide. If we have several Monetdb daemons, then we should immediately change default port number to some unused port number. 

We can set another port number by running this command:

> monetdbd set port=12345 /home/fffovde/DBfarm1

***********************************************

If our monetdbd service is already running, we should stop it. We are doing this in order to release port 50000.

> systemctl stop monetdbd
> systemctl disable monetdbd

Now we can start our server. Our new server will use default port 50000.

> monetdbd start /home/fffovde/DBfarm1

If we now look inside of our DBfarm1 directory, we will now see all of this files.

File “.merovingian_lock” is empty. This file probably just signalized that there is a server inside of directory dbfarm.

File “merovingian.pid” has the number 2436. This is the number of monetdbd process. If we use command “sudo netstat -tulnp” to show us all listening ports, we will see the name monetdbd beside process 2436, and this process will listen the port 50000.

We can also read content of log file. There we will see how our action succeeded.

After this step we no longer have to use command monetdbd, we can just use monetdb (without d).

Database creation

We will create new database in this way. This database is created in “maintenance mode” because no one can access it before we can properly configure it. This command will create a new folder with the name “voc” inside of our DBfarm1 directory.

monetdb create voc

Now, we can start our database, so that only members of monetdb group can access it.

monetdb start voc                  

We can check status of our database with this command (50000 is port number):

monetdb -p50000 status   

Last step would be to make this database available to all of users:

monetdb release voc   

Making queries

mclient” is application used by users to send queries to databases. We have to provide name of a user with “-u” switch, and name of a database with “-d” switch. Everyone that are inside of “monetdb” group can use “monetdb” username. We will be asked for password, and default password is “monetdb“. At the bottom we can notice “sql>” prompt. This is where we can type our queries.

mclient -u monetdb -d voc   

**************************************************

If we have used “set port” command to set some other port for our server, then we have to supply that alternative port number to mclient:

mclient -p50007 -u monetdb -d voc

**************************************************

Now we can type our first query. Don’t forget the semicolon. We can exit “sql>” prompt with the command “quit“.

SELECT 'columnValue' as columnName;    

How to Stop or Lock Our Server?

We can stop our server with stop command:

Monetdbd stop  ~/DBfarm1     

After we do this, we can check our “merovingian.log” file. Inside of it, we will see all of the databases of that server to be shut down.

cat merovingian.log     

If we just want to make our database unavailable then we use “lock” command. This would put our database under maintenance mod.

monetdb lock voc         

We already know that we can exit maintenance mode with “monetdb release voc“.

1. Install MonetDB Database on Ubuntu Linux

Getting the Codename of our Ubuntu Version

First, we need to know the code name of our Ubuntu version. We can find that by reading from the file “os-release”. From this file we can read only the line that has words “VERSION_CODENAME” inside of it.

> cat /etc/os-release | grep VERSION_CODENAME

Our Ubuntu codename is “focal”. It is also possible to use command:

> lsb_release -cs

We can see from the command line above that our user account is “user“. “computer” is the name of our system.

Adding a Repository Where MonetDB is Stored

Next, in folder “/etc/apt/sources.list.d” we will create a file with the name “monetdb.list”.

> cd /etc/apt/sources.list.d    #jump to that folder
> sudo touch monetdb.list       #create new file, you will be asked to provide password

Inside of this file we have to place this text. These are addresses to MonetdDB repository.
deb https://dev.monetdb.org/downloads/deb/ focal monetdb
deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb
We can add this text by inserting these two lines in our terminal:

sudo sh -c 'echo "deb https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'
sudo sh -c 'echo "deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'

Now our file looks like this:

Installing GPG key

Then, we would execute this command. This command will read GPG key file from the internet, and it will place that file in location /etc/apt/trusted.gpg.d/monetdb.gpg. GPG key is a file which will be used to verify MonetDB packages before installing them.

sudo wget --output-document=/etc/apt/trusted.gpg.d/monetdb.gpg https://dev.monetdb.org/downloads/MonetDB-GPG-KEY.gpg

This monetd.gpg file is binary file. We can read its content with the command:

sudo apt-key finger

This command will read values of all the GPG keys in our Ubuntu. One of those keys will be for MonetDB:

If result of this command is equal to “8289 A5F5 75C4 9F50 22F8 EE20 F654 63E2 DF0E 54F3” for MonetDB, then that means that we have installed the correct key.

MonetDB Installation

Now we can install MonetDB. First, we will update our list of available software with command:

sudo apt update

Then we can install MonetDB server and client:

sudo apt install monetdb5-sql monetdb-client

Next step is to enable “MonetDB” service. This service will run each time we boot our computer.

sudo systemctl enable monetdbd

But, if we want our service to run immediately, we don’t have to wait for the next boot. We can start our service with:

sudo systemctl start monetdbd

Now that we started our service, let’s check its status. We type:

systemctl status monetdbd

We can see that our process is enabled and is running.

Adding an User in MonetDB

Next step is to add users, who are allowed to run a database server, to user group monetdb. user” is the name of the users account.

sudo adduser user monetdb

To activate this change, we have to log out. If we are using console, we just have to type “exit” and then we can log in again. If we are using GUI, then we can just click on “Log Out” button somewhere in our desktop environment.

We can now  type “mserver5” in terminal (or console), and we will get a proof that our server is installed.

We can also type “monetdb status“.

Branding icons of Excel, Word, PowerPoint files

Embedded Custom Icons in Excel File

Sometimes, when we download an Excel file from the Internet, we get icons that are actually previews of the contents of the Excel file. We can get icons like this if we enable thumbnails for Office files. We will see below how to achieve this.

But even better, we can replace those preview icons with our brand icons. So, we get something like the icons below for our Excel files, but also for our Word and PowerPoint files. When we send such files to someone else, that person will receive files with our custom icons. We cannot remove the small images in the corner (1), they are automatically placed by Office, but the rest of the icon is free to customize.

How to Enable Thumbnails for Office Files

When we save files from Excel, Word or PowerPoint for the first time, there is a check box that will produce the saved files with a preview icon. We just need to check that checkbox before saving our file. Programs will remember our setting so the next time we save some other file, this checkbox will be checked. We have to do this separately for Excel, Word and PowerPoint.

It is also possible to enable this checkbox if we go to File > Info (1) > Properties > Advanced Properties (2). In the new dialog, we would have to go to the Summary tab (3) and there we have to check (4) “Save thumbnails for all Excel documents”. “Save Thumbnail” and “Save Thumbnail for All Excel Documents” are the same checkbox and they are always synchronized.

Such preview icons will only be visible on the desktop or within a Windows Explorer window. Within the Windows Explorer window, the View selected should be “Large Icons” or some similar option (1). If we still can’t see our preview icon, we should also check inside View > Options > Change folder and search options (2). That will open a new window, where in the View tab we have the option “Always show icons, never thumbnails” (3). We should make sure to disable that option.

Insert a New Icon Manually from Scratch

The file formats XLSX, DOCX, PPTX are actually ZIP files. We can use some program that can extract such archives to get the inside of our Office files. In Figure (1) we can see how to use the popular 7-Zip program to extract our archive. For some other programs, you will first need to change the Excel file extension from XLSX to ZIP, and then use that other program to decompress. 7-Zip doesn’t need that step, it will happily extract the XLSX file directly.

As we can see below, we would get at least three folders and one XML file from one Excel file. There may be some other files inside, but for our project we are only interested in the folders (1), (2) and the XML file (3).

Inside the “docProps” folder we will place our icon. The icon must be in WMF file format for Excel, JPEG file format for PowerPoint, EMF file format for Word. I use a size of 64×64 pixels. The icon names should be “thumbnail.vmf”, “thumbnail.jpeg”, “thumbnail.emf”.

In XML file “[Content_Types].xml”, before </Types>, we need to add red text from bellow. For PPTX files, text is almost the same, we just use jpeg format so the text should be “<Default Extension=”jpeg” ContentType=”image/jpeg”/>”. For DOCX files we use “<Default Extension=”emf” ContentType=”image/x-emf”/>”.

…heetml.styles+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Default Extension="wmf" ContentType="image/x-wmf"/></Types>

Inside the “_rels” folder there is “.rels” XML file. Inside it we do something similar. Before </Relationships>, we need to add red text from bellow.

…nxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId999" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/thumbnail" Target="docProps/thumbnail.wmf"/></Relationships>

For Powerpoint files, the only difference for “.rels” file is that we use “thumbnail.jpeg” instead of wmf.

<Relationship Id="rId999" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/thumbnail" Target="docProps/thumbnail.jpeg"/>

For word we use EMF.

<Relationship Id="rId999" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/thumbnail" Target="docProps/thumbnail.emf"/>

The final step is to zip all the insides of our Excel file back into the ZIP file (1). After that we just change the extension of that ZIP file to XLSX and our custom icon is applied (2).

What if We Already Have a File with a Thumbnail?

In that case, the procedure above is almost the same, but the only modification would be to replace existing WMF (or JPEG or EMF) image with our own. If the office file already has a thumbnail, then there is no need to modify XML files, we just replace the image.

Such Custom Icons are Fragile

If the user opens our file, changes some content, and then he clicks “Save”, our custom icon will be lost. There are two scenarios here:

1. If the user has “Save Thumbnails for All Excel Documents” option turned on (1), then our custom icon will change to a preview thumbnail (2).

2. If “Save Thumbnails for All Excel Documents” option is turned off (1), clicking on “Save” will revert our custom icon to the standard Excel icon (2).

Changing Default Template

Is it possible to change our default template so that every new Excel file has our custom icon?

Well, that is not possible. You can create a new file from a template, but when you save that file, your custom icon will be removed, so it is not possible to inherit custom icon from the default templates.

How to Automatically Change Office File Icons to Custom Icons

If you have a lot of Office files and want to change their icons to custom icons, then you can use my VBA project which is available for download at the bottom of this blog post. This project will work both on files without preview icon, and on files that have a preview icon. The VBA project will work on all files that have four-character extensions where the first three characters are XLS*, PPT* or DOC*. This means that this VBA project will also change the icons of XLSM and similar files, too.

First download “Icons Customization” folder (1). You can place this folder anywhere and you can rename it. Inside it there is a subfolder “Icons1” (2). Within that subfolder you can find WMF, EMF and JPEG files (3). I also uploaded original SVG files there. I transformed those SVG files into PNG files, and then those PNG files into WMF and EMF files. I couldn’t get the correct WMF and EMF files directly from SVG files. The “Icons2” folder is the same as folder “Icons1”. You can have up to 20 such folders with different icons sets. These “Icons” subfolders shouldn’t be renamed.

Files “Excel, Powerpoint, Word” (4) are the files that will get a new icon. You can place more Office files here and all of them will be modified. Note that files for Excel and Powerpoint  (5) are regular files, but the Word file (6) has a preview icon.

Now, open the “CHANGE ICONS” file (7). Choose from drop down menu (8) which icon set you want to use. Then run the “subCustomizeIcons” macro (9). When the project is completed, you will get a message (10). If you have many files and if they are big in size, then this procedure will take longer. Every file must be zipped and unzipped, and this takes time.

All original files will now be prefixed with “_OLD” (1). The new files will have original names (2). If we switch to “Large icons” view, we will see that our original files are unchanged (3), but our new files have branded icons (4).

From here you can dowload VBA project:

SUMMARIZECOLUMNS function in DAX

Our Model

We will use this simple model to explain SUMMARIZECOLUMNS function. On the left side we have ProductCategory > ProductSubcategory > Product. On the right side we have “Calendar” table. “Sales”, the fact table is in the middle.

Grouping Columns

In its simplest form, this function just groups values from several columns. If the columns do not belong to the same table, the result will be a cross join of their values.

EVALUATE
SUMMARIZECOLUMNS(   
     ProductCategory[ProductCategory]
   , Calendar[Year]
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]

But if the columns are from the same table, then only distinct rows of those columns will be returned.

EVALUATE
SUMMARIZECOLUMNS(
     Product[Manufacturer]
   , Product[BrandName]
)
ORDER BY Product[Manufacturer]
               , Product[BrandName]

If the first table above has 24 rows, and the second has 4 rows, then the formula below will give us a table with 4 * 24 = 96 rows. The formula below combines all four columns.

EVALUATE
SUMMARIZECOLUMNS( ProductCategory[ProductCategory], Calendar[Year]
                                    , Product[Manufacturer]                    , Product[BrandName] )
ORDER BY                      ProductCategory[ProductCategory], Calendar[Year]
                                    , Product[Manufacturer]                    , Product[BrandName]

Filter Table Argument

We don’t need to combine all the values from the columns, we can apply filters on them. Any table mentioned after grouping columns will be considered as a filter.


this is filter table created with TREATAS function.
This is why we only have two categories in our table.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , Calendar[Year]
   , Product[Manufacturer]
   , Product[BrandName]
   , TREATAS( { “Audio”, “Cameras and camcorders” }
        , ProductCategory[ProductCategory] )
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]
              , Product[Manufacturer]
              , Product[BrandName]

It is possible to use several filter tables. Each table will filter the columns that belong to it.



We now have two filter tables created with
TREATAS function. One will apply a filter to the “ProductCategory” column,
and the other  to the “Year” column.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , Calendar[Year]
   , Product[Manufacturer]
   , Product[BrandName]
   , TREATAS( { “Audio”, “Cameras and camcorders” }
        , ProductCategory[ProductCategory] )
   , TREATAS( { 2011, 2012 }, Calendar[Year]   )
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]
              , Product[Manufacturer]
              , Product[BrandName]

The grouping column must be part of the filter table, for the filter to apply.

Aggregations

Now we can add some aggregations. Aggregations are defined by the the name of the new column, and then we add some expression that returns a scalar value.

The SUMMARIZECOLUMNS function was born for this. This is the fastest and easiest function to group columns from several tables and then add some aggregated values from a fact table. A lot of work can be done with just one function.


*In the source data, we only have Sale for this two product categories.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , ProductSubCategory[ProductSubCategory]
   , “SalesQuantity”, SUM( Sales[SalesQuantity] )
   , “SalesAmount”, SUM( Sales[SalesAmount] )  
)
ORDER BY ProductCategory[ProductCategory]
               , ProductSubcategory[ProductSubcategory]

NONVISUAL

Filter Table argument can do two things. It can affect the number of rows, and it can also affect the measurements. Let’s make one measure:
TotalSalesAmount:=SUM( Sales[SalesAmount] )

As you can see below, we just summed the two subcategories with the measure TotalSalesAmount ( 339.112.125 ). The measure is influenced by the filter table argument. We can notice that if we display all the data, then the total amount of sales would be 416.455.001.

EVALUATE
SUMMARIZECOLUMNS(
     ProductSubcategory[ProductSubcategory]
   , TREATAS( { “Recording Pen”, “Televisions” }
        , ProductSubcategory[ProductSubcategory] )
   , “SalesQuantity”, [TotalSalesAmount]
   , “SalesAmount”, CALCULATE( [TotalSalesAmount], ALLSELECTED( Sales )  )
)
ORDER BY  ProductSubcategory[ProductSubcategory]

We can remove the filter table influence on the measure by wrapping it in a NONVISUAL function. This time our TotalSalesAmount is exactly 416.455.001. The image below is from PBID, as this NONVISUAL function was not introduced into Excel.

EVALUATE
SUMMARIZECOLUMNS(
     ProductSubcategory[ProductSubcategory]
   , NONVISUAL( TREATAS( { “Recording Pen”, “Televisions” }
        , ProductSubcategory[ProductSubcategory] ) )
   , “SalesQuantity”, [TotalSalesAmount]
   , “SalesAmount”, CALCULATE( [TotalSalesAmount], ALLSELECTED( Sales )  )
)
ORDER BY  ProductSubcategory[ProductSubcategory]

IGNORE

When we apply aggregations, rows where all measures are blank, will be excluded from the result.
We use VALUES function because there is no row context in the SUMMARIZECOLUMNS function.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , “SalesQuantity”, IF( VALUESProductCategory[ProductCategory] ) = “TV and Video”
                                   , BLANK(), SUM( Sales[SalesQuantity] ) )
   , “SalesAmount”, IF( VALUES ProductCategory[ProductCategory] ) = “TV and Video”
                                  , BLANK(), SUM( Sales[SalesAmount] ) ) )
ORDER BY ProductCategory[ProductCategory]

We can use the IGNORE function to treat some measures as they were blank. If some of the measures values are blank, and others are IGNORED, then such rows will not be part of a result.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , “SalesQuantity”, IF( VALUESProductCategory[ProductCategory] ) = “TV and Video”
                                   , BLANK(), SUM( Sales[SalesQuantity] ) )
   , “SalesAmount”, IGNORE( SUM( Sales[SalesAmount] ) )
ORDER BY ProductCategory[ProductCategory]

Using IGNORE on all the rows will not hide all data. Contrary, it will display all of the rows.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , “SalesQuantity”, IGNORE( SUM( Sales[SalesQuantity] ) )
   , “SalesAmount”, IGNORE( SUM( Sales[SalesAmount] ) )
ORDER BY ProductCategory[ProductCategory]

ROLLUPADDISSUBTOTAL

ROLLUPADDISSUBTOTAL basics

SUMMARIZECOLUMNS can have subtotals and grandtotal calculated using the ROLLUPADDISSUBTOTAL helper function. This function accepts at least two arguments. First is the column used for grouping. For the items in this column, we would get subtotals. The second argument is the name of the new column which will say TRUE or FALSE depending on whether that row is a detail row or a subtotal row.

In the image bellow we can see three new rows with subtotals. There is, also, one more column that shows whether the row is a subtotal for a particular column ( TRUE or FALSE ).

EVALUATE
SUMMARIZECOLUMNS(
      Calendar[Year]
   ,  ROLLUPADDISSUBTOTAL( ProductCategory[ProductCategory], “CategorySubtotal?” )
   , “SalesAmount”, [TotalSalesAmount]
)
ORDER BY  Calendar[Year] ASC
                , ProductSubcategory[ProductSubcategory] DESC

This happens if we wrap each grouping column with ROLLAPADDISSUBTOTAL. We get all possible subtotals.

EVALUATE
SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( Calendar[Year], “YearSubtotal?” )
   ,  ROLLUPADDISSUBTOTAL( ProductCategory[ProductCategory], “CategorySubtotal?” )
   , “SalesAmount”, [TotalSalesAmount]
)
ORDER BY  Calendar[Year] ASC
                , ProductCategory[ProductCategory] DESC

It is possible to place all the grouping columns together in a singe ROLLUPADDISSUBTOTAL function. In that case, we would get hierarchical subtotals, from left to right ( like in Excel pivot table ).

EVALUATE
SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( Calendar[Year], “YearSubtotal?”
                                               , ProductCategory[ProductCategory], “CategorySubtotal?” )

   , “SalesAmount”, [TotalSalesAmount]
)
ORDER BY  Calendar[Year] DESC, ProductCategory[ProductCategory] DESC

ROLLUPADDISSUBTOTAL filters

We can create a single filter table. We can place this filter table in the ROLLAPADDISSUBTOTAL function and that way we can filter our subtotals and grand total. We can place this argument at the beginning, and at the end of ROLLUPADDISSUBTOTAL function. At the start it would only filter the value of grand total. At the end, it would filter only column items.

In this example below we place it in both places. This would filter both the grand total and the items. The images are from PBID because this argument is not introduced into Excel.

VAR RollupFilter = TREATAS( { “Recording pen”}; OnlyNeeded[ProductSubcategory] )
VAR Result  = SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( RollupFilter
        ; OnlyNeeded[ProductSubcategory]
        ; “SubcategorySubtotal?”
        ; RollupFilter )
   ; “SalesAmount”; [TotalSalesAmount]
)
RETURN Result

This would be the results if we placed this argument only at the beginning, or only at the end of the ROLLUPADDISSUBTOTAL function.

At the beginning, it influence only grand total.
At the end, it influence only subtotals.

ROLLUPGROUP

By placing some columns in a ROLLUPGROUP, we will observe them together. That is why their individual subtotals will not appear in our table. If we want to exclude some subtotals, we use ROLLUPGROUP.

In this example we don’t have subtotals for ProductCategory and SubCategory. They are excluded because we placed this two columns in the ROLLUPGROUP function.

EVALUATE
SUMMARIZECOLUMNS(     
     ROLLUPADDISSUBTOTAL(
           Calendar[Year]
         , “YearSubtotal?”
         , ROLLUPGROUP( ProductCategory[ProductCategory]
        
                           , ProductSubcategory[ProductSubcategory] )
         , “(Sub)CategorySubtotal?”
     )
     , “SalesAmount”
     , [TotalSalesAmount] 
)
ORDER BY  Calendar[Year] DESC
                , ProductCategory[ProductCategory] DESC
                , ProductSubcategory[ProductSubcategory] DESC

Sample file can be downloaded from here:

Remember and Reset the Cursor Position

What Problem Are We Solving?

When making video tutorials, it’s easiest to make short videos, but sometimes we need more time to explain a topic. Even then, we can break our entire tutorial into smaller videos. Then we have a problem how to connect those small videos into a whole. This problem can be solved by placing a slide between each smaller video. If slides are not a suitable solution then we have to find a way to seamlessly connect our smaller videos. This leads us to our problem, how to record cursor position in the previous video, so that we can start recording new video with the cursor at the same position.

Idea is to save location of our cursor in TXT file, when some shortcut is pressed (1). This is something we have to do at the end of recording. Before we start recording the next video, using another shortcut, we would return the cursor to the last position (2). Note that the TXT file always retains the last 20 recorded positions.

I will show you a solution that doesn’t use third-party software and can be use on any computer.

Recording the Last Position of the Cursor

We can record the last cursor position with a Powershell script. This script will read the current cursor position and then it will write that cursor position into TXT file.

Add-Type -AssemblyName System.Windows.Forms

$p = [System.Windows.Forms.Cursor]::Position 
$X = $p.X 
$Y = $p.Y

Add-Content -Path "C:\Users\Sima\Desktop\Resursi\Kursor pozicija\Previous cursor positions.txt" -Value ( $X.ToString() + "`r`n" + $Y.ToString() )

Add-Type -AssemblyName PresentationCore,PresentationFramework
$ButtonType = [System.Windows.MessageBoxButton]::OK
$MessageboxTitle = "Remember cursor position."
$Messageboxbody = "Position $X, $Y is remembered."
$MessageIcon = [System.Windows.MessageBoxImage]::Information
[System.Windows.MessageBox]::Show($Messageboxbody,$MessageboxTitle,$ButtonType,$messageicon)

That TXT file will keep the last 20 positions (1) so we don’t have to worry about overwriting the cursor position we saved earlier. In the end we will get a message box (2) with the position of our cursor expressed in pixels.

Resetting of the Cursor Position

Again, we’ll use Powershell to reset the cursor position. First we will read the last two numbers from our TXT file. Next, we’ll set the cursor position to the new location. Finally, we need to make sure that there are only the last 20 positions saved in our TXT file. We will achieve this by measuring how many lines there are in our file, and if that number is greater than 20 then we will overwrite our file with only the last 20 positions.

$file = "C:\Users\Sima\Desktop\Resursi\Kursor pozicija\Previous cursor positions.txt"
$file_data = Get-Content -tail 2 $file

Add-Type -AssemblyName System.Windows.Forms

$p = [System.Windows.Forms.Cursor]::Position
$p.X = $file_data[0]
$p.Y = $file_data[1]
[System.Windows.Forms.Cursor]::Position = $p 

$content = Get-Content $file
$numberOfLines = $content.Length
if ( $numberOfLines -gt 20 ) 
{
  $content[($numberOfLines-20)..$numberOfLines]|Out-File $file -Force
}

Embedding Powershell Scripts into VBS Scripts

Running Powershell PS1 scripts is restricted due to security. Instead of calling our scripts directly, we’ll wrap them in VBS scripts. Our code for the first and second scripts will now look like this. At the beginning and end, we need to create and destroy the shell object. We use that object to run our scripts using powershell.exe. The quotes and double quotes in the original Powershell scripts have been modified so that the script can be embedded inside a VBS script.

RECORD:
Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -Command ""Add-Type -AssemblyName System.Windows.Forms;$p = [System.Windows.Forms.Cursor]::Position;$X = $p.X;$Y = $p.Y;Add-Content -Path 'C:\Users\Sima\Desktop\Pamcenje kursor pozicije\Previous cursor positions.txt' -Value ( $X.ToString() + """"""`r`n"""""" + $Y.ToString()  );Add-Type -AssemblyName PresentationCore,PresentationFramework;$ButtonType = [System.Windows.MessageBoxButton]::OK;$MessageboxTitle = 'Remember cursor position.';$Messageboxbody = """"""Position $X, $Y is remembered."""""";$MessageIcon = [System.Windows.MessageBoxImage]::Information;[System.Windows.MessageBox]::Show($Messageboxbody,$MessageboxTitle,$ButtonType,$messageicon)"" ", 0
Set WshShell = Nothing

RESET:
Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -Command ""$file = 'C:\Users\Sima\Desktop\Pamcenje kursor pozicije\Previous cursor positions.txt'; $file_data = Get-Content -tail 2 $file; Add-Type -AssemblyName System.Windows.Forms;$p = [System.Windows.Forms.Cursor]::Position; $p.X = $file_data[0]; $p.Y = $file_data[1]; [System.Windows.Forms.Cursor]::Position = $p; $content = Get-Content $file; $numberOfLines = $content.Length; if ( $numberOfLines -gt 20 ) { $content[($numberOfLines-20)..$numberOfLines]|Out-File $file -Force }"" ", 0
Set WshShell = Nothing

Notice that WshShell.Run has a second argument. That argument is zero. This is used to prevent opening of a terminal window while scripts are executing.

Calling our Scripts

We need to call our scripts with keyboard shortcuts. We have to use shortcuts because we can’t use the mouse for that. Our cursor must be stationary.

We can create global shortcuts by creating two shortcut files that target our VBS scripts (1). These two shortcut files must be placed on the desktop, otherwise the keyboard shortcuts will not work.

Next we need to go to the properties of our shortcut files and there on the “Shortcut” tab we can set the keys (2) that will be used to call our VBS scripts.

Sample files can be downloaded below. Remember to change fullpath of “Previous cursor positions.txt” file inside of each VBS script. Also, change the target of each shortcut file. Change fullpath of powershell executive file, too.