John Liu Blog

Every drop counts

Power BI hierarchy

In Power BI, you can create new hierarchy in Model View. There are simply one-level hierarchy, such as category and subcategory for product. A more complex one is the multi-level parent-child hierarchy, such as orgnisation management chart which has multiple levels. For the complex parent-child hierarchy, Power BI does not flatten the hierarchy by default and this needs to be done manually using PATH() AND PATHITEM() functions. //create a new column, Path //The PATH() function returns a string contains a delimited list of IDs, starting with the top/root of a hierarchy and ending with the specified ID.

Posted by John Liu Thursday, February 23, 2023

Configure application auto start after reboot

To configure application auto run after current user login: First login in as the user Ctrl + R to start run, type in “sheel:startup” (which is folder like C:\Users\john\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup) Create a shortcut to the application required with appropriate parameter Note: you may need to redo the above each time following application reinstall/upgrade

Posted by John Liu Saturday, January 28, 2023

SQL Server Session Settings

Following SQL Server session settings impact on if an existing plan in cache can be used or not. Only if the session settings are identical to the settings for the cached plan, the plan can then be reused; otherwise, it will be recompiled. Different setting may impact performance at execution. ANSI_DEFAULTS ANSI_NULL_DFLT_ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT For stored procedure, the ANSI_NULLS and QUOTED_IDENTIFIER settings will be used are the settings used when the stored procedure is created and runtime settings are irrelevant.

Posted by John Liu Tuesday, January 24, 2023

Power BI notes

Number formatting “#,0.00” two decimal with thousand separator “#,0” no decimal with thousand separator “#,#” no decimal with thousand separator “P0” no decimal point “P2” 2 decimal points Workout the first day of the week based on sepecified day =DateAdd(DateInterval.Day ,1 ,DateAdd(DateInterval.Day ,DatePart(DateInterval.WeekDay ,iif(IsNothing(Parameters!ReportDate.Value),Today(),Parameters!ReportDate.Value) ,FirstDayOfWeek.Sunday ) * -1 ,iif(IsNothing(Parameters!ReportDate.Value),Today(),Parameters!ReportDate.Value) ) )

Posted by John Liu Friday, December 23, 2022

SQL Server Python integration

SQL Server Machine Learning Services config files are located under C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXP_2019\MSSQL\Binn pythonlauncher.config rlauncher.config When receive following error If the service worked before but not now, check the LaunchPad service has been restarted following restart of SQL Instance. If restart SQL Instance followed by restart LaunchPad service didn’t help, check LaunchPad service account has FULL CONTROL permission on the working folder configured in the above config file From SQL2019, the working folder in the config file is a shortcut link to C:\Program Files\Microsoft SQL Server\MSSQL15.

Posted by John Liu Friday, December 23, 2022