Monday, March 9, 2020

A useful DAX formula for calculating new work won from project variation

Below calculate newly won project work sourced from project variation, it checks a few 


1. restrict the calculations to projects exists from previous month;
2. the formula can also calculate accumulated nww spanned multipel month

NWW Var :=
    VALUES ( DIM_Time[Calendar Month] ),
    SUMX (
        VALUES ( fact_result[project_code] ),
        IF (
            CALCULATE ( COUNTROWS ( fact_result ) ) > 0
                && CALCULATE (
                    COUNTROWS ( fact_result ),
                    PARALLELPERIOD ( DIM_Time[Date], -1MONTH )
                ) > 0,
            CALCULATE ( SUM ( fact_result[EAC_Fees] ) )
                CALCULATE (
                    SUM ( fact_result[EAC_Fees] ),
                    PARALLELPERIOD ( DIM_Time[Date], -1MONTH )

similary you can also derive the formula for calcualting new work won from new projects...

Wednesday, March 4, 2020

No module named PIL - when running tensorflow with databrick

Google didnt' tell me the answer directly... I have to install pillow onto the cluster and then the problem goes away.......

Tuesday, October 8, 2019

Clustered Stacked bar chart with line in secondary axis in Power BI

it looks like there is no native support for this kind of complex chart, initially I tried ggplot2 in R visual however I couldnt' get the ideal solution. finally it appears that this can be done much easier using python pandas + matplotlib.

sample code as below:

df = pd.DataFrame(dict(Subsidy=[3, 3, 3],
                       Bonus=[1, 1, 1],
                       Expense=[2, 2, 2],
                       Salary=[1, 2, 1],

ax = df[['Subsidy', 'Bonus']], position=1,
                            width=.2, ylim=[0, 8], color=['orange', 'red'])
df['Margin'].plot(secondary_y=True, color='k', marker='o')
df[['Expense', 'Salary']],stacked=True, position=0,
                            width=.2, ylim=[0, 8], color=['blue', 'green'])

Monday, September 23, 2019

SQL upgrade from 2012 to 2016 hangs

during the final stage of the upgrade on the sqlenginedbstartconfigaction_upgrade_configrc_cpu64  screen, the installation process appears to stuck, no error message no progreess so far.

however by looking into ERROLOG, it shows the sql process is busy upgrading databases etc, leave the process running for about 30 minutes and it finishes successfully.

it is bit confusing though since there is absolutely nothing else indicatign the progress is not stuck... even the errorlog only get updates after a few minutes.

Wednesday, June 5, 2019

Containerize legacy applications (Epicor E4SE) - take 3

After I figured out the MSDTC issue, all seemed well until I got hit by the MSMQ problem, even though I have diverted the queue to be on a remote machine, E4SE refused to work until it is satisfied that MSMQ is installed on the container, it turned out that MSMQ support in Container is only possible after build 1803 which isn't supported under Windows Server 2016.

Since I havne't got a 2019 server box on hand, I opted to use my windows 10 machine and pulled the 1803 image and rebuilt everything, good news is after all these the MSMQ issue is also gone.
will do more testing later but hopefully there should be no more major issues.

Tuesday, June 4, 2019

Containerize legacy applications (Epicor E4SE) - take 2

In my previous post, I almost thought everything is working fine until I got hit by this infamous MSDTC problem, I came across a few posts online and although they are quite inspiring and some of them have got it working, they are not quite the same situation as I got.

here is a summary of what people have got working for MSDTC:
> both app and sql are containerised
> under AWS, using ELB and port mapping
> under Azure, using CNI 

what I want to achieve:

> Containerized web app
> SQL Server running on VM
>  the web app will need to enable windows authentication under the domain, sql server is running under domain also

For me, my window server 2016 container host is running under vmware, initially I want to use the transparent network to make things easier however enabling promiscuous mode on the vmware environment doesn't seem to be an option so  this is out, at the end I ended up with running container just in NAT network and expose custom port to host.

It turns out that the above mentioned AWS scenario is the closest to mine, what I ended up with is to use KEMP load balancer in the place of ELB, 

what is really important:

> fix MSDTC port on the container and expose to host.
> expose RPC port 135 to host using customer port number
> expose port 80 to host via custom port number.

the KEMP load balancer will then map the ordinary port number to the custom port number on the container host, we also need to create the hostname DNS to point to the KEMP load balancer IP.

the above scenario only involves one container instance, if we need run multiple replica then we will most likely need to multiple the setup but I assume it is all straight forward and no drama here.

and finally MSDTC is working fine and hopefully the PoC is a success