PowerQuery using parameters

For our business we use PowerQuery/PowerBI to analyse data from databases in Postgres. Overall this is in general a very nice way to link databases with Excel.

Unfortunately the language for the formulas is a bit clumsy. The documentation can be found here. It is also annoying that the query source gets changed after compilation in Excel.

This post is mainly intented to keep track of how to add a parameter to a power query.

My personal raw version looks like this:

let
Source = (daysBack)=>PostgreSQL.Database(„host“, „user“, [Query=“select * from analysis where as_of_date > CURRENT_DATE – INTERVAL ‚ “ & Text.From(daysBack)& “ day'“])
in
Source

Excel translates this then into

let

Source = (daysBack)=>PostgreSQL.Database(„host“, „user“, [Query=“select * from analysis where as_of_date > CURRENT_DATE – INTERVAL ‚ “ & Text.From(daysBack)& “ day'“]),
#“Invoked FunctionSource“ = Source(3)
in
#“Invoked FunctionSource“

 

XAML: Format decimals

Binding=“{Binding BindTarget,StringFormat={}{#.00}}“

Cmd: Search for dlls

dir /B c:\prod x.dll

Disruptor-Net after 2 weeks

I have been using the disruptor now in my main libs. Each day the RingBuffers are used 300-400 Mio times per day without any issues.

I realized that there multiple disruptor implementations on Nuget.

I personally use this implementation. The implementation is based on the latest Java API and thus it required a small change as the IEventHandler interface changed its name from OnNext to OnEvent.

FSharp Data access via SqlDataProvider

I am personally using Postgres as my database engine and I usually use a DB first approach.

On the C# side I have worked with Dapper so far – especially as I typically require speed.

On the F# I have started to use the SqlDataProvider and it is the typical delightful type provider experience. It adds a lot of productivity if you can use Intellisense when coding.

The code typical looks like this:

[<Literal>]
let connectionString = „Server=xxx;Port=5432;User Id=postgres;Password=quepasa;Database=db;“

type sql = SqlDataProvider< ConnectionString=connectionString, DatabaseVendor=Common.DatabaseProviderTypes.POSTGRESQL, IndividualsAmount=1000, UseOptionTypes=true >

Actual insert code looks like this

let ctx = sql.GetDataContext()
let rowDb = ctx.Public.Tablexxx.Create()
rowDb.Column1 <- „xxx“
ctx.SubmitUpdates()

Disruptor-Net: First experience

Yesterday I have taken the Disruptor-Net  into production at a first criticial part of one of my applications. A great general overview of the pattern can be found in Martin Fowler’s blog.

The general plan for me is to get rid of BlockingCollection and use this instead. Fortunately I usually use the ConsumingEnumerable and thus the migration is easy and requires most of the work on the objects which are pushed to the queue to support update operations for object reuse.

I took this example as a guideline. Unfortunately the number of C# examples is limited. This is rather simple, but it feels a bit unelegant and clumpsy as it is very Java-ish, so I might look again at getting it more idiomatic and with less noise in the code. A BlockingCollection requires one class and this implementation requires three – I hope to get it down to two again to get rid of the EventHandler class and use delegates instead.

Performance is always about seeing for believing and it did look good. Using efficient collection is a great and rather safe way to improve the performance and latency.

Typical F# for reconciliations

One typical task for a typical enterprise developer is to compare/reconcile data from two sources. Typically one source is your own database and the second source is a structured data file, usually in csv or xml format.

These programs are a time consuming and boring task. The complexity is usually coming from the bad documentation of the data. The big advantage of using the FSharp. Data Provider is that one saves a lot of typing to generate clean types for a concise code.

The purpose of this entry is to show a skeleton F# program for this task.

type Key =

{ Account : string

      Id : string }

type Value =

    { Volume : int }

Later in the Main program the main part looks like this:

    let key2ValueOne =

        data.Rows

        |> Seq.filter (fun row -> filter1 row)

        |> Seq.map (fun row ->

               { Account = row.ACCOUNT_ID

                 Id = row.Id }, { Volume = row.QUANTITY })

        |> Map.ofSeq

    let valueSeq = accounts |> Seq.map (fun account -> getValues os account)

    let key2ValueTwo =

       valueSeq

        |> Seq.map Map.toSeq

        |> Seq.concat

        |> Map.ofSeq

    let onlyOne =

       key2ValueOne

        |> Map.toSeq

        |> Seq.map fst

        |> Seq.filter (fun key -> not (key2ValueTwo.ContainsKey(key)))

    let onlyTwo =

        key2ValueTwo

        |> Map.toSeq

        |> Seq.map fst

        |> Seq.filter (fun key -> not (key2ValueOne.ContainsKey(key)))

    let differences =

        key2ValueOne

        |> Map.toSeq

        |> Seq.map fst

        |> Seq.filter (fun key -> key2ValueTwo.ContainsKey(key))

        |> Seq.filter (fun key -> key2ValueOne.[key] <>key2ValueTwo.[key])

Serilog

Serilog has been upgraded to a new version. The main thing is that it has now split into several packages, which helps to reduce the number updates, but also is a breaking change. Thus my minimum example for Serilog in F# has changed.

Nuget packages:

install-package Serilog

install-package Serilog.Sinks.RollingFile

Add the beginning of a module

module Logger =
let log = (new LoggerConfiguration()).WriteTo.RollingFile(„MyProgram-{Date}.log“).CreateLogger()

Actual logging code might look like this:

Logger.log.Information(„Start“)

Parallels

I have bought a new Mac Air laptop mainly for home and training purposes. The laptop has an I5 processor, 8 GB of Ram and 128 GB Hard disk (maybe not so smart, even though I plane to use a 128 SD card for non-critical or cloud-based data).

As the Windows 10 update cutoff was looming and with a spare Win 8 license in the cellar, I had a decision to make if I install Windows via Bootcamp or via Parallels. I plan to use Windows solely for Visual Studio.

I have finally decided to use Parallels as I hope that the performance penalty will be ok to bear and the advantages especially around disk space usage/flexibility will pay off. I got the first glimpse of the advantage when realizing that instead of the planned 10 GB for VS I might need to have 30 GB with Xamarin.

One great article which I found was 5 Ways to save disk space on Mac OS. I will add from time to time more content to update on my experience.

 

Evaluating MS Bond – Part1

I am currently investigating alternatives to Protobuf for serialization and deserialization.
I want to test especially the newcomers Aeron.NET and MS Bond.

This article gives  a quick first summary when starting to test MS Bond.

MS Bond works with a two-step process for serialization and is thus based on schema files.

The schema files get translated via a tool named gbc in actual C# classes (Bond is crossplatform and supports C#, C++ and Python).

The actual development steps are:

• Istall the Nuget packag Bond.CSharp
• Create the Schema file
• Add ..\packages\Bond.CSharp.4.2.1\tools\gbc c# Price.Bond as an pre-build event

My first draft of a file looks like this:

namespace BondTestsCSharp
struct PriceTick
{
0: int32 ContractTag;
1: int32 PriceType;
2: double Price;
3: int32 Volume;
4: int64 TimeStamp;

}

I will look into some more steps regarding Custom times – especially to wrap TimeStamps.

Bond can be found at .MS Bond