Sometimes I wonder why I had created a Tech category separate from an IT category. The two of them are interrelated closely as my recent Wordpress blog post on my so-called Data Kraken had demonstrated.
I call myself the Theoretical Department of our engineering consultancy because I am mainly in charge of software development, simulations, and data analysis – related to measurement data for our heat pump system (and those of our clients).
But there is one big difference between what I call 'IT-only projects' (like my PKI-related services) or engineering projects that also involve software: 'IT' is my tag for providing software-related consulting or software engineering related to somebody else's IT system – a system whose requirements are defined by somebody else. My engineering software is built according to my own requirements. My 'Tech' projects, IT-centered as they may seem, are not primarily about IT: They are about systems using, storing, and transferring energy. IT is just a tool I use to get the job done.
All things I had ever done as an IT professional turn out to be useful, and I am learning something new nearly every day – when thinking about 'energy'. Heating systems today are part of what is called Internet of Things – so IT security is also an important aspect to consider. In 2015 I used this website to finally transition to .NET (… finally, from ASP ?), and as a spin-off I also re-developed the numerical simulations for our heat pump system in .NET – representing every component as on object. 2014 I migrated our initially only Excel-based data analysis to SQL Server, and I have improved my 'Data Kraken framework' since then, adding visualization by automated Excel plots etc.
I still work for some select 'IT-only' clients - and it seems my 'IT articles' here just constitute a series of updates about the exact extent to which I still do PKI. If the occasional data analysis question comes up, any SQL, Excel, or .NET skills might come in handy in my IT projects - like querying a certification authority's database, or using a semi-automated Excel sheet to create a Certificate Policy Statement, following the RFC. But I don't advertise myself as a SQL etc. expert; I rather think I returned to where I came from, many years ago:
When I worked as an IT consultant, I had been asked over and over: How does a physicist end up in IT? There are very different reasons: The obvious one is that as a physicist you might have picked some programming experience. I had indeed contributed to the (mess of patchy 'local-community-developed') software for automating the measurement of electrical resistance of superconducting thin films many years ago, but this was not the main reason. I was an experimental physicist so I can't claim that my work was immensely mathematical or computational (and my job as 'implemented applied cryptography' via Public Key Infrastructures was not either). The main analogy is that IT systems of sufficient complexity are as unpredictable as an experimental setup governed by lots of parameters, some of which you have not identified yet – as was the manufacturing of thin films by laser ablation. I was simply patient, perseverant, and good at troubleshooting by navigating a hyperspace of options what might have gone wrong.
This might be either boring or frustrating for non-geeks. But I believe the grunt work of maintaining and fixing software is rewarding if this is an auxiliary task, done to support the 'actual' system of interest. Mine are heat pump systems, power meters, photovoltaic generators and the like. I want to understand and optimize them and so I am willing to learn new programming languages and spend hours on troubleshooting bugs with software vendors' updates. Just as back then I learn the bare minimum of Turbo Pascal to develop software for low temperature measurements.
In 2017 I am going to focus on maintaining (and bug fixing ?) Data Kraken und ich will work on making usage and 'visualization' of the numerical simulation more and more similar to Data Kraken.
Currently, Data Kraken has the following main features:
- Documentation of the sensors and log files for different loggers (Heat pump / UVR16x2, smart meter, PV…) in an Access database - a small proto-kraken per installed system.
- Documentation of changes to sensors and log files, such as: Shuffled columns in files, modified naming conventions for files, new or replaced sensors. For example, the formerly manual reading off of the surface level of water in the water/ice tank has been replaced with an automated measurement in 2016. So the input value for calculating ice volume moved to a column in a different log file, and was measured in different time intervals.
- A Powershell script grabs all log files from their source locations, and changes date formats, decimal commas and line breaks. (I found this to be more performant than manipulating every line later after the import to SQL Server).
- The Powershell script then creates an updated set of SQL scripts – one set of scripts and one SQL database for each installation / each client. For example, the CREATE TABLE or ALTER TABLE commands are created based on the Access documentation of measured values and their change log.
- SQL scripts create or add SQL Server database fields, import only the files containing data points not imported yet, and import their data to a staging table. Each SQL database can thus always be re-created from scratch – from CSV log files and the meta documentation (Access).
- Error values are modified or deleted from the staging table, as defined before in the Access database (and such in a SQL script): For example vendor-defined error values for not connected sensors (as 9999) are set to NULL or whole rows of values are deleted if the system was e.g. subject to maintenance according to other system's documentation.
- Finally, the most important script is run: The one that does the actual calculation of e.g. average brine temperature, energy harvested by PV panels or the solar / air collector by day, or daily performance factors of the heat pump. The script needs several levels of SQL views – all of which are re-created by the script.
- Microsoft Excel is used as a front-end to show values from tables with calculation results. One Excel-formula only simple table allows for browsing through values, and picking daily, monthly, yearly, or seasonal numbers.
- Excel plots are automated with respect to the fields (columns) and to start and end date. Existing plots can be copied (also from other workbook), then documented in a table. The documentation table can then be modified and is used as input. Color and line widths are still tweaked manually.
Weird as this setup sounds, it allowed me to develop and change the solution just in the right way – installation by installation, e.g. by testing the changes to log files after the control unit's firmware for one specific installation first.
... and first post published to the new site, live and public now :-)
For a short time, the old sites are still available in parallel to the new site.
Looking back, I mainly struggled with:
- My flat-file database - accessing content and all meta information stored in text files, using standards SQL queries.
- Redirect strategy: Existing loads of redirects, temporary ones, permanent 301 ones, nice URLs without physical files...
- Migration of the actual content, uniting what was separated in different sources - asp files, RSS feed, CSV file databases
See also my latest blog post. Which also contains the expected meta-musings on The Web.
Lest we not forget - these were the old sites:
In the past weeks since the last update I've added the following features:
- XML sitemap including English and German posts - URLs and last changed date.
- Make yearly archive URLs 'hackable', thus using just /[lang]/[yyyy] as archive URL.
- Population of meta tags, using also open graph tags.
- Adding 'breadcrumb' / 'where am I' information by highlighting the item just clicked in the menu and side bars: Current category, current post, current tag.
- Assign an optional image to a post via related attributes: Image source, image size or full image tage (for embedding Wikimedia images plus copyright information). If an image should be displayed, but no source is given, add a standard image.
- Display the image automatically on the bottom of the post and use it in the open graph image tag, to be used as a preview image. Calculate height and size from the image's physical size and intended width.
- Create thumbnails of these images, to be shown in the list of posts in the category pages.
- Store all global configuration settings such as tagline in a config file that uses the same [name:] [value] parsing logic as content files.
- Migrate all existing posts on the sites e-stangl.at, radices.net, and subversiv.at, and keep track of where the content came from. (One former .asp page contained one or more 'posts').
- Use one default.aspx for all applications, differences depend on the app name. Example: Don't show post archive for the business page, but show latest posts from Wordpress blog feed instead.
- Clean old content: Replace relative references (../) by absolute ones, replace CSS classes in tags. Move meta infos from content to new file attributes.
Web Server Settings and DNS
- Tested the IIS URL rewrite module with a key map, to be created from Excel documentation. In case of issues with rewriting: Fall back to redirecting in a main ASP file.
- Configure new host names and subdomains in DNS as primary URLs of the new applications. Add new host names for testing to reflect the already existing redirects plus the migration redirects plus the future standard redirects.
- Modify the existing main default.asp, global.asa, and main asp script creating all pages to work with the new redirects (some duplicate code in asp and .net could not be avoided)
- Host name determines application name: One main host name for each (of the 3-4) application. I will use a subdomain of subversiv.at as my new primary host.
- Check if the application has been migrated, as per config parameters. If not the existing redirect logic and existing asp code kicks in - which sends the user to a subfolder depending on host name. This is for historical reasons as I had only one virtual web host in the old times, so e.g. e-stangl.at/ redirected to e-stangl.at/e/
- If the app was migrated, redirect all attempts to use a 'secondary' host to the new one. So e.g. accessing e-stangl.at will be recognized as calling the elkement app and redirect to my new primary name.
- Configuring the application as 'migrated' does not yet redirect any attempt to access one of the old articles. I will have to turn on my rewrite map or code for that.
- Complete all features for all applications before taking 'elkement'
- Feed parser for punktwissen,
- 'image database' for z-village (using small posts with images effectively as entries in a table of images), add an option to show the large version of the image inline.
- Maybe: Ordering of posts in category by changed date, not by created date.
- Limit number of posts on main page and on tag's pages, number = global parameter.
- Replace internal relative URLs to pages in the same virtual directory by absolute ones.
- Maybe: Replace parent path (../) URLs in old code, to turn Parent Path in the ASP config off as soon as possible.
- Migrate all content from side panes, header, and footer. Add images used before to new posts, re-use descriptions from old image database (TXT).
- Take elkement live and test redirects and preview images (social networks).
- If OK: Take the other apps live.
- Fix bugs
- Turn on redirects for old ASP pages.
- Watch results in web master tools.
- Inform Google about new URLs (Web Master Tools)
I've built the underlying 'flat-file database' (Details in this post), and my not yet public site has these features now:
- Menu bar from pages.
- Show all postings on home page
- Recent posts and archive in left bar.
- Tag cloud in right bar, tags created by grouping all posts' meta data.
- 'Tag page': Show all posts tagged with a specific tag.
- Indicate category of current posting by highlighting category in the menu.
- Highlight currently clicked article in archive.
- Menu page contains custom text plus automatically created list of all postings in this category.
- Automatic creation of RSS feed.
- CSS stylesheet and responsive design.
- 'Nice' URLs - ASP.NET Routing.
Currently I am painstakingly migrating snippets of content to new counterparts / articles / text files.
For testing I am using a layout similar to my Wordpress.com's blog design now: