iris-analytics-for-money

I regret getting a late start in the InterSystems Analytics contest. I had tried a couple of times before to use Analytics, but I had not gotten too far. I have been recording financial transactions in Excel workbook ever since I had to retire Microsoft Money. Since my iris-for-money was not fully functional, I created a worksheet per account I was tracking. I had developed a CSP page in iris-for-money to import transactions by reading a CSV file. I realized this process requires escaping many characters that can cause the process to fail.

One week ago I realized I had just two days left to enter the Analytics contest. I knew I needed data to perform Analytics. I also knew I wanted to get a better view into how I was doing financially. Therefore I started working on iris-analytics-for-money.

I spent a lot of time last Saturday trying to import data into IRIS. I knew how to save one sheet in Excel as a CSV file. I started to display the data in a table on the CSP page with a button to import one row at a time making REST call passing an INSERT statement to IRIS. I added a button to import all rows via a loop. I learned that I had to escape characters such as # which marks the end of the query string.

I also realized that I had many sheets in my Excel workbook because I was tracking multiple checking, savings, and credit card accounts. I learned how to use VBA in Excel to export all sheets into one CSV file.

I had to make sure all sheets had the same columns in the right order. And I wonder about Debit and Credit or Deposit and Withdraw. I am not a financial expert, so I associate using a Debit Card with spending money, so it is Amount Debit and if a deposit is credited that is Amount Credit. What if I use a credit card? I think a return is credited so a credit card purchase is also spending so it goes to Amount Debit. I hope I got this right.

I was successful in exporting data into one CSV file. I wrote a class method that reads the CSV file and imports each line. I was happy that I did not have to escape characters like # using this way to import data.

Later I realized I had some bugs in my import code. I had to be careful when I identify each column by the header row. I had to make sure I spelled Category and SubCategory correctly. And it took me a while that nothing went to Amount Debit because there was another column header labeled Debit Card and I was just looking for Debit.

Once I had some data in a persistent table, I wondered how to start doing Analytics. I saw in iris-analytics-template we can use a wizard to generate a cube class from a regular data class. Initially the data did not look useful because my Transaction table has numeric Ids for Accounts, Categories, and Merchants. I searched through Documentation and Tutorial, and I learned to use Analytics > Architect from IRIS Management Portal to build a custom cube class where I can define to use the Account, Category, and Merchant names instead of just an Id.

I learned how to define Time Dimension based on Transaction Date. I eventually figured out how to define pivot table to show Categories in rows with columns for Amounts Debit and Credits. The pivot was improved by showing each Year-Month I had data for and inside each month I could show all the categories that were used that month. I saved the pivot in a folder.

I started a new Dashboard, and I added the pivot. I saved the Dashboard in the same folder as the pivot. Next, I needed to figure out how to export the items in the folder so that they can be included in docker image.

At that time, I did not understand how the iris-analytics template accomplishes this. I like to thank Evgeny for taking time to be available to help and explain this to me. Prior to that, while Evgeny was traveling to Boston, I found in documentation how to export pivot and dashboard in a Container Class. Evgeny did not seem happy when I mentioned this accomplishment, but it got the job done. When I included the container class in the Riches package, it got compiled and I could find my dashboard in IRIS portal.

I also learned that zero is not a valid date in Analytics. I still want to learn to display the Amounts as dollars with two digits for the cents. And I need to review my data collection, because sometimes I see large amounts in both debits and credits columns. I expect to see credits in Spending category if I return an item for refund. Or is it that I have mixed up debits and credits somewhere?

I tried to learn to use ZPM. I appreciate the feedback from @Sennin when he tried to zpm install my application. Last night, after I had a conversation with Evgeny, where he explained many of the resources in module.xml to me, I read his post on testing zpm.

Now I understand to clone my repo and start a container with docker-compose up -d –build. Then I enter into IRIS terminal inside the container. I switch to IRISAPP namespace and enter ZPM. First step is load /irisdev/app which is the container path to the repo directory. This is where my efforts eventually failed, because I was not able to use FileCopy to copy the export.csv to populate the data tables.

If I remember correctly, next I would package my application and then connect to test repo and deploy my application. Then I would start a clean IRIS container and install my application from the test repo. I got this to work even building a cube, but there was no data because there was no export.csv.

My situation is different from most. I use containers, but only run containers on AWS EC2. I like to use IRIS 2020.1, because I have Studio for this version. And I observed, when I changed to latest community edition in my Dockerfile I got an error. I believe the later IRIS did not like my cube name iris-for-money.  I switched back to IRIS 2020.1 image and the error went away.

I am glad I participated in this contest. I wish I had another week or two to improve my application. I will continue to improve it, because I want to be able to analyze my money. And I know so much more about Analytics compared to last week. I enjoy learning in this community.