h1

PowerQuery using parameters

Oktober 27, 2016

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“

 

Advertisements
h1

XAML: Format decimals

Oktober 7, 2016

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

h1

Cmd: Search for dlls

Oktober 4, 2016

dir /B c:\prod x.dll

h1

Disruptor-Net after 2 weeks

August 24, 2016

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.

h1

FSharp Data access via SqlDataProvider

August 11, 2016

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()

h1

Disruptor-Net: First experience

August 5, 2016

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.

h1

Typical F# for reconciliations

August 2, 2016

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])