My MSSQL database is corrupt and I can not get to my predictive models. Now what? Stellar to the rescue.

Small project database, with old backup, ended up in corrupt state. This happened.  A corrupted database; all-in-all 150MB.  I was super annoyed. Let alone, I had some R and Python code there and some – yes 🙂 – predictive models, I forgot to have automated backup created. Data stored in database should not be a problem, but the table with last stored model was the information / row, I needed

So the problem with serialized models stored in a table is, that it needs to be fully compatible and correctly restored to the last bit in order for R / Python to be read correctly. So missing one bit, and the model is literally useless.

My models are stored in T-SQL Table:

SELECT  
        [model_name]
       ,[model]
       ,[accuracy]
       ,[model_date]
       ,[version_Model]
       ,[Approved_by]
FROM [SQLR].[dbo].[Predictive_models]

Once I calmed my self down, I started with my options. Worse case scenario, I can restore two-months old backup, but what are my other options?

Using the Microsoft built-in Database Console Command

DBCC CHECKDB

utility.  The utility comes with multiple repair options to resolve corruption errors in MS SQL database.  But, yeah,  not that it is a super challenging but I started to repair with option:

DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS

and it ended with data loss. Hurray for the better restore.  But still was not successful enough due to the data loss.

Digging further, I used  PowerShell Restore-SqlDatabase cmdlet for restoring SQL Server database. But, was left empty-handed, since this works only with when you have a restorable backup.

Digging for further information, I googled up and came to this blogpost, by Stellar. Next thing you know, I am downloading the software, and looking back now, I was the best choice.

sql-box

The benefits using Stellar software repair for MS SQL Server:

  • Comes with a simple to use GUI which makes the repair process straightforward.
  • Helps repair SQL database quickly in three simple steps: 1. Select database file. 2. Repair database file. 3. Preview and Save repaired file.
  • Recovers all major database components of SQL server including tables, views, indexes, stored procedures, etc.
  • Resolves corruption errors in databases running on MS SQL Server 2019, 2017, 2016, and older versions

Additional Benefits:

  • Repairs SQL tables with PAGE and ROW compression.
  • Allows saving the repaired file in MS SQL, HTML, XLS, and CSV formats
  • Provides Windows and SQL authentication options to connect to the server
  • Supports Standard Compression Scheme for Unicode (SCSU) for SQL Server 2008 R2.

(based on the info/sales text from their website)

Step-by-step to repair corrupt .mdf file with Stellar Repair for MS SQL Server

After downloading, stop the MS SQL Server service and copy database (mdf. file) to different location.

1. The Instruction window is displayed. Click OK.

1-Software Instruction

2.  Select Database window appears. Click Browse to select .mdf file you want to repair.

2-Main Interface

Great feature is also, the ability to recover deleted database records; in this case select ‘Include Deleted Records’ checkbox (and make sure that you have the appropriate database recovery model selected).

After selecting the desired file, click repair to engage the process. Hopefully only one mdf file is corrupt 🙂 But you can also select multiple.

3-Selected database

There is also the ability to select the SQL Server version if the software is not able to detect the MSSQL Server database file version.  In this case, just  select the version of the SQL Server where your database was originally created.

4-Select Version

After the process completes with recovery, you can inspect what has been recovered by Stellar.

2020-01-16 08_48_03-Photos

 

 And I can recover only selected table and prior to recovering / saving, I can also inspect the content of my data in right pane.  Since the models are serialized and stored as blob, I can only hope that R will be able to de-serialized it and used it again using sp_execute_external_script.

Save the data back to  MDF file and later attach it to the SQL Server or in my case, exporting to CSV file is also a possibility since the table was not that big.

After attaching back new MDF file with table holding the models:

2020-01-16 08_55_13-SQLQuery8.sql - SNTK.UmbriaPrediction (SNTK_Tomaz (77))_ - Microsoft SQL Server

i’m able to run successfully the R script and score new values.

After all, I am happy that I found this software that helped me dealing with corrupt table / database and I can score the data on my older model that has great accuracy.

As always, happy coding!

Tagged with: , , , , , , , ,
Posted in Uncategorized

Sudoku game with R

Sudoku is a classical logical game based on combinatorial number replacement puzzle. Objective is to to fill 9×9 matrix with digits so that each column, each row, and each box (3×3 sub-grid) of nine contain all of the digits from 1 to 9.

Solving sometimes can be a nagging process. For this purpose, here is the R helper function for you to solve this with R.

grid

Let’s get the Sudoku grid we want to solve into R:

sudoku <- matrix(data=c(
6,0,0,2,1,0,0,3,0,
5,0,9,0,0,0,6,0,0,
2,0,0,9,7,0,0,0,4,
0,0,2,3,0,4,0,0,0,
0,6,0,0,5,0,0,9,0,
0,0,0,1,0,9,7,0,0,
9,0,0,0,3,8,0,0,6,
0,0,7,0,0,0,2,0,5,
0,8,0,0,4,2,0,0,9), nrow=9, ncol=9, byrow=FALSE
)

 

Now, we will need a function that will find all the 0 values – these are the values we need to work on.

get_zeros <- function(board_su){
  #empty df
  df <- data.frame(i=NULL,j=NULL)
  for (i in 1:nrow(board_su)){
    for (j in 1:ncol(board_su)){
      if (board_su[i,j] == 0) {
        a <- data.frame(i,j)
        #names(a) <- c("i", "j")
        #df <- rbind(df, a)
        df <- a
        return(df)
      } 
    }
  }
}

In addition we will need a function to solve and validated the solution.

Function validater will validate for the sudoku board a particular solution at a particular position:

validater(sudoku, 1, c(1,4))

In matrix, at position x=1, y=4, where there is 0, it will test if number 1 is valid or not. If the number is valid, it returns TRUE (number) to outer function for finding complete solution.

This function iterates through all the possible 0-positions and iterates through solutions that are still available based on the rules:

  •  each row can contain only one number in range of 1..9
  • each column can contain only one numer in range of 1..9
  • each sub-grid of 3×3 can contain only one number in range of 1..9

And the nuts and bolts of the validater function:

validater <- function(board_su, num, pos=c(NULL,NULL)){
  status <- FALSE
  a <- as.integer(pos[1])
  b <- as.integer(pos[2])
  num <- as.integer(num)
  while (status == FALSE) {
    for (i in 1:9) {    
      if ((board_su[a,i] == num & b != i) == TRUE) {
        status <- FALSE
        return(status)
      }
    }
    
    for (i in 1:9) {    
      if ((board_su[i,b] == num & a != i) == TRUE) {
        status <- FALSE
        return(status)
      }
    }
    
    #which box are we in
    boxNx <- as.integer(ifelse(as.integer(b/3)==0, 1, as.integer(b/3)))
    boxNy <- as.integer(ifelse(as.integer(a/3)==0, 1, as.integer(a/3)))
    
    #looping through the box
    for (i in boxNy*3:(boxNy*3 + 3)) {
      for (j in  boxNx * 3 : (boxNx*3 + 3)) {
        if ((board_su[i, j] == num &  i != a & j != b) == TRUE){
          status <- FALSE
        }
      }
    }
    status <- TRUE
    return(status) 
  }
}

With the following solution:

solution

For sure, this is not to be taken seriously, as you get the application on your mobile phone where you make a photo of your grid to be solved and the phone solves it for you, using library like OpenCV. The code was created only and just for fun (and because the Advent of Code for 2019 is over).

Happy R coding 🙂

As always, the code is available at Github.

Tagged with: , , ,
Posted in Uncategorized

Working with Windows CMD system commands in R

From time to time, when developing in R, working and wrangling data , preparing for machine learning projects, it comes the time, one would still need to access the operating system commands from/in R.

In this blog post, let’s take a look at some most useful cmd commands when using R.  Please note, that the cmd commands apply only to windows environment, for Linux/MacOS, the system commands should be slightly changed, but the wrapper R code should remains the same.

1

1. R commands to access the CMD

R commands that are used for accessing, running and doing changes through cmd are:

  • system
  • system2
  • shell
  • shell.exec

All invoke the OS command that is specified and followed. All of the stated functions are part of the base R, so no additional packages are needed.

Let’s say, we want to start with DIR command and return the results in RStudio.

## 1 Simple dir command with order switch
system("dir /o")
system2("dir /o")
shell("dir /o")
shell.exec("dir /o")

and you will see that shell command will output the results back to console pane in R Studio, where as the others will not give you the immediate desired output. The main difference is that shell function will run the desired command under the shell (following the POSIX standard or here you can find POSIX functions)  and the system command will invoke OS command.

In R Base package,a set of dedicated functions is also available to create, open, append and close file and URL connections, as well as opening and loading the compressed files. Just listing the functions:

  • object connections: file, url
  • compression and encoding: gzfile, bzfile, xzfile, unz, pipe
  • connection functions: open,close,flush
  • boolean functions: isOpen, isIncomplete

Rule of thumb (with default settings) on when to use system or shell function, shell will work always, system will only work when the system settings (paths) are correctly prepared. Example:

### Checking the usage
Sys.which("copy")
Sys.which("ping")

and the result is that system and system2 functions will work for ping CMD command, but not for copy CMD command, and later in the blogpost, also the rename, move command.

4

2. Chaining the CMD commands in R

For the simplicity, let’s continue with shell function and try how chaining can be achieved.

Following example will not work, as every time, the shell function is called, the environment is initiated. So running shell sequentially will not work!

setwd("C:\\Users\\Tomaz")

shell("dir")
shell("cd ..")
shell("dir")

Chaining the cmd commands together in one command will work:

shell("dir && cd .. && dir")

Note that this will not alter your working directory, so every time the shell is completed, you will be returned back to initiated environment. By checking:

getwd()

you can see, that the working directory has not changed. Checking before and after the R environment variables, these will also remained intact:

Sys.getenv(c("R_HOME","HOME"))

You can also define the variable, use the echo command, use the build in system variables like %TIME% and many more. More is available at Github.

3. Most typical CMD commands executed from R

Here is the list of some CMD commands we will encapsulate in R:

  • fc / compare
  • rename
  • move / copy
  • ping (pingpath)
  • systeminfo
  • tasklist (taskkill)
  • ipconfig / netstat

 

3.1. Comparing two files in CMD

Having capability to compare two files in CMD is just another of many ways to check if your dataset is the same.

Let’s create two *.csv files with iris dataset and run the file compare in cmd:

setwd("C:\\Users\\Tomaz")

#create two files
write.csv(iris, file="iris_file1.csv")
write.csv(iris, file="iris_file2.csv")

#compare both files
shell("FC /a C:\\Users\\Tomaz\\iris_file1.csv 
             C:\\Users\\Tomaz\\iris_file2.csv")

The result information is: FC: no differences encountered. Same as if the command would be run from command line:

2

Same result could be achieved with R function that comes in base build: all.equal.

file1 <- "C:\\Users\\Tomaz\\iris_file1.csv"
file2 <- "C:\\Users\\Tomaz\\iris_file2.csv"

# or in R using all.equal
all.equal(readLines(file1), readLines(file2))

Since we are in R, we would like to have results stored in data.frame. To do so, let’s use the previously mentioned System2 function. This is a newer version of System function that uses arguments and commands separately and some additional settings.

# run the same command using newer function "system2" and set the arguments

cmd_command <- "FC" 
#cmd_args <- "/a C:\\Users\\Tomaz\\iris_file1.csv 
                 C:\\Users\\Tomaz\\iris_file2.csv"
cmd_args <- c('/a', file1, file2)

rr <- system2(command=cmd_command,
                      args= cmd_args, 
                      stdout=TRUE,
                      stderr=TRUE, 
                       wait = TRUE)

#suppose we want to store the results in data.frame
#empty dataframe
df_rr <- data.frame(file1 = character(),
                    file2 = character(),
                    fcompare = character(),
                    stringsAsFactors=FALSE)

#temporary results
temp <- data.frame(file1=file1, file2=file2, fcompare=rr[2])

#bind all into dataframe
df_rr <- rbind(df_rr, setNames(temp, names(df_rr)))

 

with results stored in data.frame when comparing multiple files.

3

3.2. Renaming a file in CMD

Keeping the file with iris dataset: C:\\Users\\Tomaz\\iris_file1.csv, let’s do the renaming.

# renaming the file using shell command
shell("cd c:\\users\\tomaz && ren iris_file1.csv iris_file1_copy.csv")

and with the parametrized shell function (remember the copy will not work due to Sys.which(“copy”) is returned empty):

file_old_name <- "c:\\users\\Tomaz\\iris_file2.csv"
file_new_name <- "iris_file2_new.csv"

cmd_command <- paste("RENAME", file_old_name, file_new_name) 

# System2 Does not work
# system2(command=cmd_command)
shell(cmd_command)

In both ways, the files are renamed. Fortunately or not, the output in this case is none, when there is no syntax error and file exists.

3.3. Copy a file in CMD

Keeping the file with iris dataset: C:\\Users\\Tomaz\\iris_file1.csv, let’s do also the copying.

#Copying file
shell("copy c:\\Users\\Tomaz\\iris_file1.csv iris_file1_copy.csv")

and with the parametrized shell function:

orig_file <- "c:\\Users\\Tomaz\\iris_file1.csv"
copy_file <- "iris_file1_copy1.csv"
command <- "copy" 

cmd_command <- paste(command, orig_file, copy_file)
shell(cmd_command)

Now check your files.

3.4. Ping

Since ping CMD is available with the sys.which() function, we can use it directly as:

URL <- 'tomaztsql.wordpress.com'

cmd_command <- "Ping" 
cmd_args <- c('-n 1', URL)

system2(command=cmd_command,
           args=cmd_args, 
           stdout=TRUE,
           stderr=TRUE, 
           wait = TRUE)

And since we want to check many URLs, let’s create an empty data.frame to store the result and a function to walk through the list.

URLs <- c("google.com", "tomaztql.wordpress.com", "youtube.com")

#empty dataframe
df_rr <- data.frame(URL = character(),
                   reply = character(),
                   package = character(),
                   stringsAsFactors=FALSE)

ping_fn <- function(url) {
         system2("ping",c(url,' -n 1'),
         stdout=TRUE,
         stderr=TRUE)
}

for (i in 1:length(URLs)){
       site <- print(URLs[i])
       rr <- ping_fn(site)
       temp <- data.frame(URL=rr[2], reply=rr[3], package=rr[6])
       df_rr <- rbind(df_rr, setNames(temp, names(df_rr)))
}

head(df_rr)

and the result is binded in a single data.frame:

5

 

3.5. Systeminfo

Assume, you need the information about the system not only about the R environment. Some of the R functions are available here:

# R
R.Version()
Sys.info()
.Platform

And all are very useful. But if you need Systeminfo CMD information into your R IDE environment, save it in data.frame, using system2() function is a way to go.

# Using system2

cmd_command <- "systeminfo" 
rr <- system2(command=cmd_command,
stdout=TRUE,
stderr=TRUE, 
wait = TRUE)

and getting the result:

6

So grabbing the e.g.: “System Manufacturer” just walk to the 13th position in the vector:

rr[13]

 

3.6. Tasklist

Getting the list of all the running task

cmd_command <- "tasklist" 
rr <- system2(command=cmd_command,
               stdout=TRUE,
               stderr=TRUE, 
               wait = TRUE)

# getting the results into something more readable format
dd <- tibble::enframe(rr)
stack(dd)[1]

and can get all the additional information from the data.frame.

In addition, you can also kill a specific task

# kill a specific task
shell("taskkill /F /PID 6816")

with the returned information: “SUCCESS: Sent termination signal to the process with PID 6816.

3.7 Ipconfig / netstat

Getting the list IpConfig information or all the netstat information, the task is the same:

cmd_command <- "netstat" 
rr <- system2(command=cmd_command,
               stdout=TRUE,
               stderr=TRUE, 
               wait = TRUE)

and storing the results with tibble:

dd <- tibble::enframe(rr)

and the results are same as in CMD:

7

4. Conclusion

Using CMD in R IDE will for sure not be your daily driver when crunching numbers and wrangling data. But when you will need to operate closely with your files, datasets, system or tasks, system2 and shell functions are absolute winners to do the job.

 

As always, complete set of the code is available on Github.

Happy R-coding! 🙂

 

Tagged with: , , , , , , , , , , , , , , ,
Posted in Uncategorized

Custom Power BI filter pane with hide/show feature

Having as much possible Power BI estate when it comes to putting the visuals for data exploration, is everyone’s dream. But the slicers for “slicing and dicing” the data is also very important. Having all the slicers hidden has always been mine go-to design.

Several similar ideas have been shown, Guy in a cube (Adam and Patric) have both done similar ideas over past years, many questions have also been answered on Power BI community website. And mine requirements were similar:

  1. have the ability to hide the filter pane
  2. have the selected slicer items listed

 

01

In general, what always bothered me, was that left or upper side of Power BI Page would always hold the slicers and selections, which might make page slightly crammed and hence rather unclear.

1. Getting data

 

Diamonds data-set is available at Github. Another way to get this data-set is with R script:

library(ggplot2)
attach(diamonds)
write.csv(diamonds, file="diamonds.csv",sep=",")

Once you have the data, let’s start digging in Power BI. In general, we will create two main things: 1) hidden filter pane and 2) filter selection cards.

2. Creating custom filter pane

 

First, we will need to get the data into Power BI. Once you add your desired visuals on the page, we can start with creating the filter pane.

1) Add a shape on Page, in the case of this example, the rectangle

2) Add the slicers for the data to be “sliced and diced”. In case of this example, we are adding two:  Cut and Clarity. So it should look like this:

02Now we need to give it a little bit of logic in order for the pane to work. Under view, select the pane “Selection” to be viewed besides the Visualization and Fields pane.

03

3) Group the elements: Shape, Slicer, Slicer:

04

And rename it to Filter Pane.

4) Under Insert, go to Buttons and add Back (Back button) to the Filter Pane, and it will be automatically grouped.

05

5) Add an Image or Button or any type of placeholder for your user to be able to show (invoke) the filter pane (menu icon is also available on Github). So adding the image on Power BI Page and placing it where the user will be able to see it and use it easily.

06

(Note: At this time, the shape with both slicers and back button is hidden).

6) We will create Bookmarks for both: a) shown filter pane and b) hidden filter pane. Under menu View, select Bookmarks.

07

7) Create new bookmark with Filter pane hidden. Rename it as “Filter Pane Hidden” and click the ellipsis and uncheck Data (as shown on  print-screen). This will not apply any selected slicer values when hiding or showing filter pane (clicking the bookmarks).

08

8) Create another new bookmark with Filter pane shown. Repeat all the steps as with the first bookmark.

09

By clicking on the bookmarks, you should be able to see the filter pane appear or disappear.

9) With both bookmarks created, now we need to assign both bookmarks to menu button and to back button. Make sure you have the filter pane hidden. Click on menu image (1).

010

Under the Format Image, select Action (2), Change (3) Type to Bookmark and select (4) Bookmark “Filter Pane Shown”. Optional, you can add (5) Tooltip as a caption to the menu image.

10) Repeat for the Back Button. Make sure you have selected the “Filter Pane Shown” bookmark. Click the (1)  Back button and under the Visualization, under (2) Action, select (3) Type Bookmark and select (4) bookmark “Filter Pane Hidden” and optional add the caption for the back button under (5) Tooltip.

011

Before going into testing the bookmarks, make sure you have the correct order of objects in Layer order under the Selection Pane:

012

In this Layer ordering, when opening Filter Pane it will always be on top, regardless of objects underneath the Filter pane.

To test the Menu, Filter Pane and Bookmarks, make sure you hit CTRL + mouse Click. This way you will invoke the filter pane.

013

3. Show selected filters

Now that we have the filters hidden from the users, it would be also fair, to have the information about the selected filters in form of a card, available for the end users.

For both slicers (cut and clarity), I will add a card visual for each.

Create new measure and add DAX:

Diamonds Cut Selection = IF
(
  ISFILTERED(diamonds[cut]);
  SELECTEDVALUE(diamonds[cut]; "Multiple cuts selected");
  "Filter not selected"
)
And for Clarity, we will create two measures with DAX. First one will be same as the previous one:
Diamonds Clarity Selection = IF
(
  ISFILTERED(diamonds[Clarity]);
  SELECTEDVALUE(diamonds[clarity]; "Multiple colors selected");
  "Filter not selected"
)
And the selections measure will include the previous one:
Diamonds Color Selections = 
   VAR Conc = CONCATENATEX(VALUES(diamonds[clarity]);
               [Diamonds Clarity Selection];",")
RETURN(Conc)

This combination will concatenate the selected values, so that the end users will be able to see the list of all selected values.

014

At the end both results are seen as (and some additional design enhancements):

015

 

Code to this example is available at Github.

Happy DAXing!

 

 

 

Tagged with: , , , , , , ,
Posted in Uncategorized

Time functions in DAX

Time functions in DAX are besides simple calculation functions, the most useful functions; mainly because time slicers are one of the most frequently used slicers in Power BI.

2019-12-08 21_02_26-Window

New functions are coming to DAX (Data Analysis Expression Language) regularly, and just recently, the function QUARTER was added.  And this gave me thinking; why not do a standardized DAX, to be added as a “time dimension” to every Power BI, that I create.

Upon opening new Power BI Desktop document, go to Enter Data and add two rows, representing the start and end time:

2

I have inputed two rows:

  • 1978/08/29
  • 2019/12/07

After this, go to Modeling tab and select New Table:

3

Simply copy/paste the following code:

DimTime =
     VAR V_Calendar = CALENDARAUTO()
RETURN
    GENERATE (
        V_Calendar;
        VAR V_Calendar_date = [Date]
        VAR LetoDatum = YEAR(V_Calendar_date)
        VAR Kvartal = CEILING(MONTH(V_Calendar_date)/3;1)
        VAR MesecCifra = MONTH(V_Calendar_date)
        VAR DanN = DAY(V_Calendar_date)
        VAR KonecMeseca = EOMONTH(V_Calendar_date; 0)
        VAR TedenCifra = WEEKNUM(V_Calendar_date; 2)
        VAR DanVTednu = WEEKDAY(V_Calendar_date;2)
        Return ROW(
            "Day"; V_Calendar_date;
            "OnlyDay"; DanN;
            "Year"; LetoDatum;
            "Month (number)"; MesecCifra;
            "Quarter"; Kvartal;
            "Month"; FORMAT(V_Calendar_date; "mmmm");
            "DayOfWeek"; DanVTednu;
            "NameOfWeek"; FORMAT(DanVTednu+1; "dddd");
            "Year Month"; FORMAT (V_Calendar_date; "mmm yy");
            "End Of Month"; FORMAT (KonecMeseca; "dd mmm yy");
            "Week Number"; TedenCifra
        )
    )

And it should look like this:

4

Yielding the corresponding fields in the data structure:

5

Notice that the DimTime will be automatically populated within the minimum and maximum years; so the range goes from 1978/01/01 until 2019/12/31. You can also add the relation if you want to filter based on the context, but it will have no effect on populating the DimTime table.

6

Now I can visualize and count, for example, how many Friday, 13th (since one is just around the corner) are between 1978/01/01 and 2019/31/12. And I can do this simply by adding couple of slicers:

7

And by selecting Friday and 13, I get the result: 74.

8

And If I want to check this; let’s see, how many are in Year 2019:

9

Only two! 🙂

Power BI (with DAX) is available at GitHub here.

Happy data slicing 🙂

 

 

Tagged with: , , , ,
Posted in Uncategorized

Advent of Code 2019 challenge with R

I have decided to tackle this year’s Advent Of Code using R (more or less). I know there are more preferred languages, such as Python, C#, Java, JavaScript, Go, Kotlin, C++, Elixir, but it was worth trying.

Into the 8th day of the competition, in the time of writing this blog post, I have had little problems using R. There was a competition on second day, that initiated the array of arrays with 0-based first position, and knowing that R is 1-based first position, I had some fun (and aggravation) finding out the problem. And at the end, I rewrote everything in Python 🙂 The same set of instructions continued on the 5th and 6th day, but R should also be just as good as any other language.

On the day 3, the Manhattan distance (similar to Manhattan wiring) from starting point to closest intersection of two different wires needed to be calculated. Which of course, it was fun, but an extra little tickle was to visualize both wires in 2-D space.

The following dataset is, the dataset I have received, your’s will be different. And the code was just a product of 10 minutes writing (hence, not really optimized).

My dataset is presented as:

library(ggplot2)
# read instructions for both wires
d <- c("R1007","D949","R640","D225","R390","D41","R257","D180","L372","U62","L454","U594","L427","U561","R844","D435","L730","U964","L164","U342","R293","D490","L246","U323","L14","D366","L549","U312","L851","U959","L255","U947","L179","U109","R850","D703","L310","U175","L665","U515","R23","D633","L212","U650","R477","U131","L838","D445","R999","D229","L772","U716","L137","U355","R51","D565","L410","D493","L312","U623","L846","D283","R980","U804","L791","U918","L641","U258","R301","U727","L307","U970","L748","U229","L225","U997","L134","D707","L655","D168","L931","D6","R36","D617","L211","D453","L969","U577","R299","D804","R910","D898","R553","U298","L309","D912","R757","U581","R228","U586","L331","D865","R606","D163","R425","U670","R156","U814","L168","D777","R674","D970","L64","U840","L688","U144","L101","U281","L615","D393","R277","U990","L9","U619","L904","D967","L166","U839","L132","U216","R988","U834","R342","U197","L717","U167","L524","U747","L222","U736","L149","D156","L265","U657","L72","D728","L966","U896","R45","D985","R297","U38","R6","D390","L65","D367","R806","U999","L840","D583","R646","U43","L731","D929","L941","D165","R663","U645","L753","U619","R60","D14","L811","D622","L835","U127","L475","D494","R466","U695","R809","U446","R523","D403","R843","U715","L486","D661","L584","U818","L377","D857","L220","U309","R192","U601","R253","D13","L95","U32","L646","D983","R13","U821","L1","U309","L425","U993","L785","U804","L663","U699","L286","U280","R237","U388","L170","D222","L900","U204","R68","D453","R721","U326","L629","D44","R925","D347","R264","D767","L785","U249","R989","D469","L446","D384","L914","U444","R741","U90","R424","U107","R98","U20","R302","U464","L808","D615","R837","U405","L191","D26","R661","D758","L866","D640","L675","U135","R288","D357","R316","D127","R599","U411","R664","D584","L979","D432","R887","D104","R275","D825","L338","D739","R568","D625","L829","D393","L997","D291","L448","D947","L728","U181","L137","D572","L16","U358","R331","D966","R887","D122","L334","D560","R938","D159","R178","D29","L832","D58","R37")
d2 <- c("L993","U121","L882","U500","L740","D222","R574","U947","L541","U949","L219","D492","R108","D621","L875","D715","R274","D858","R510","U668","R677","U327","L284","U537","L371","U810","L360","U333","L926","D144","R162","U750","L741","D360","R792","D256","L44","D893","R969","D996","L905","D524","R538","U141","R70","U347","L383","U74","R893","D560","L39","U447","L205","D783","L244","D40","R374","U507","L946","D934","R962","D138","L584","U562","L624","U69","L77","D137","L441","U671","L849","D283","L742","D459","R105","D265","R312","D734","R47","D369","R676","D429","R160","D814","L881","D830","R395","U598","L413","U817","R855","D377","L338","D413","L294","U321","L714","D217","L15","U341","R342","D480","R660","D11","L192","U518","L654","U13","L984","D866","R877","U801","R413","U66","R269","D750","R294","D143","R929","D786","R606","U816","L562","U938","R484","U32","R136","U30","L393","U209","L838","U451","L387","U413","R518","D9","L847","D605","L8","D805","R348","D174","R865","U962","R926","U401","R445","U720","L843","U785","R287","D656","L489","D465","L192","U68","L738","U962","R384","U288","L517","U396","L955","U556","R707","U329","L589","U604","L583","U457","R545","D504","L521","U711","L232","D329","L110","U167","R311","D234","R284","D984","L778","D295","R603","U349","R942","U81","R972","D505","L301","U422","R840","U689","R225","D780","R379","D200","R57","D781","R166","U245","L865","U790","R654","D127","R125","D363","L989","D976","R993","U702","L461","U165","L747","U656","R617","D115","L783","U187","L462","U838","R854","D516","L978","U846","R203","D46","R833","U393","L322","D17","L160","D278","R919","U611","L59","U709","L472","U871","L377","U111","L612","D177","R712","U628","R858","D54","L612","D303","R205","U430","R494","D306","L474","U848","R816","D104","L967","U886","L866","D366","L120","D735","R694","D335","R399","D198","R132","D787","L749","D612","R525","U163","R660","U316","R482","D412","L376","U170","R891","D202","R408","D333","R842","U965","R955","U440","L26","U747","R447","D8","R319","D188","L532","D39","L863","D599","R307","U253","R22")

Creating two empty dataframes (each for different group) and a helper function to decode the instructions: R1007 -> should be undestood as Right for 1007 steps and in accordance, the coordinates change.

gCoord2 <- function(pos, prevX, prevY){
direction <- substring(pos,1,1)
val <- as.integer(substring(pos,2,nchar(pos)))
#a <- c(0,0)
if (direction == "R") {
a <- c(prevX,prevY+val) 
return(a)}
if (direction == "L") {
a <- c(prevX,prevY-val) 
return(a)}
if (direction == "U") {
a <- c(prevX+val,prevY) 
return(a)}
if (direction == "D") {
a <- c(prevX-val, prevY) 
return(a)}
}

And not to iterate through both datasets and binding them into single one:

df <- data.frame(x = c(0), y = c(0), group = 1)
df2 <- data.frame(x = c(0), y = c(0), group = 2)

for (i in 1:length(d)){
ii <- d[i] 
print(ii)
#get last value from df
X <- tail(df$x,1)
Y <- tail(df$y,1)
x1<- gCoord2(d[i],X,Y)[1]
y1<- gCoord2(d[i],X,Y)[2]
df <-rbind(df, c(x=x1, y=y1, group=1))
}

for (i in 1:length(d2)){
ii <- d2[i] 
print(ii)
#get last value from df
X <- tail(df2$x,1)
Y <- tail(df2$y,1)
x1<- gCoord2(d2[i],X,Y)[1]
y1<- gCoord2(d2[i],X,Y)[2]
df2 <-rbind(df2, c(x=x1, y=y1, group=2))
}

df3 <- rbind(df,df2)

Finally, touch of ggplot2:

ggplot(df3, aes(x = x, y = y, group = group, colour=group)) +
geom_path(size = 0.75, show.legend = FALSE) +
# theme_void() +
ggtitle('Advent Of Code 2019; Day 3 - Graph; starting point(0,0)')

And the graph with both wires:

plot_zoom

Happy R-coding! 🙂

Tagged with: , , , ,
Posted in Uncategorized

November 2019 update and decomposition tree

I am super excited about all the new features that November 2019 update brought to Power BI Desktop. Complete list is here.

Great new visual for all the data analysts is: decomposition tree.

2019-11-20 21_43_49-decompositiontree - Power BI Desktop

To enable the decomposition tree, navigate to Options,  Preview features and select  “Enable Decomposition tree visual”

2019-11-20 20_54_15-Options

Import your favorite data set; I am taking:

select * from [dbo].[vTargetMail]

from the AdventureWorksDW database.

In the visualization pane, select the variable from your dataset that you want to analyze and and drag it into the “Analyze”.  In the “Explain By” drag and drop all the variables you want to decompose and understand how the variable under Analyze is behaving.

2019-11-20 21_01_14-Untitled - Power BI Desktop

Decomposition tree is a data presentation of slicing and dicing of selected metrics based on the attributes of these metrics or with combination of other metrics. Another great aspect of this visual is to analyze the selected variable with many metrics or attributes (dimensions) as the same time.

2019-11-20 21_15_56-Untitled - Power BI Desktop

With multiple attributes and metrics in the tree, data analysts can play with data, selecting or deselecting different metrics or attributes with clicking on values, of tree branches or on the plus to add another branch or to change the value of the branch.

Worth mentioning are the light bulbs for (both working when AI is enabled)

  • High Value – higher value of the split is selected; based on all the features (variables in “Explain by”), the visual suggests which visual is best to split further in order to get higher values.
  • Low value – higher value of the split is selected; based on all the features (variables in “Explain by”), the visual suggests which visual is best to split further in order to get higher values.

2019-11-20 21_20_29-Untitled - Power BI Desktop

Both (High and Low) resemble  decision tree logic, where Entropy and Information Gain is calculated in order to select best feature and splits, this logic appears very much similar here as well.

“Enable AI splits” must be enabled under the visual settings. And the analysis type is available as relative or absolute.

2019-11-20 21_27_51-Untitled - Power BI Desktop

 

Another super cool feature with November 2019 update, is refreshed Ribbon. This feature too,  can be switched on under Options; new and refreshed Ribbon.

Refreshed Ribbon is more tactile, more adaptive, more responsive and the icons are grouped together now much better.

2019-11-20 21_40_36-Presentation1 - PowerPoint.png

Five minutes into working with refreshed Ribbon and everyone will love it.

 

Enjoy your data and stay cool.

Tagged with: , , , , ,
Posted in Uncategorized
Categories
Follow TomazTsql on WordPress.com
Programs I Use
Programs I Use
Programs I Use
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and DLM in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.