Serving medium size and large enterprises nationwide by building and customizing resources planning, consulting, customer management solutions. Research, design, development and support of bussiness information systems.

Mastering OLAP Cube Reporting: Solving Grand Total Calculations in Snowflake Warehouse and AAS Cube Projects

March 27, 2024 5:28 pm

Welcome to the first installment of our promised series of articles detailing the challenges we encountered and conquered during our work on the “Snowflake Warehouse and AAS Cube” project.

Today, we’re excited to share how we tackled a particularly thorny issue while developing an AAS Cube project for one of our valued clients. Our task involved creating measures to extract financial data such as Sales and EBITDA. Given that reports are generated in specific currencies (e.g., USD), we needed to incorporate currency (FX) conversions within these measures. The parameters for these calculations varied depending on the particular company, with each company having its base currency code. More


Navigating Complexity: Empowering Data-driven Decisions in Portfolio Management

February 20, 2024 6:06 pm


We’ve embarked on yet another project recently, one that poses our biggest challenge to date.
We have a long-standing client for whom we developed a web-based portfolio management system over 20 years ago. Since then, we’ve continuously improved and expanded its functionality by incorporating new modules, ETL processes to import data from external sources, and various types of reports and exports. Recently, the client presented us with an intriguing request.
The client emphasized the need for extensive flexibility across their portfolio management teams, including S&A, CFOs, and Fund Admins. They required seamless addition/removal of fields and enhanced data uploading capabilities. Additionally, they expressed interest in self-service business intelligence tools for end-users, such as PowerBI, MS Excel, and Tableau. Their objective was to empower users to manipulate existing data dynamically to address a wide array of inquiries pertinent to their areas of focus, aiding management and strategists in making well-informed decisions.
To accommodate these evolving needs, the client decided to democratize their data by establishing a Snowflake Enterprise Data Warehouse. One segment of this warehouse contains raw data synced daily from the legacy platform. We devised an ETL process to populate a newly defined database within the same warehouse, structured with facts and dimensions tables. The ultimate goal is to leverage the data from all client’s systems and bring it into the data warehouse allowing users to seamlessly combine and analyze information from various sources.
Within Azure Analytics Services, we defined an OLAP cube. This cube interfaces with data sources to process raw data, performing aggregations and calculations for associated measures. In the project’s initial phase, drawing from our legacy platform expertise, we aimed to define a comprehensive set of DAX expressions within the cube. These expressions would enable end-users to create reports using Excel independently.
A significant challenge arose from the legacy system’s heavy reliance on complex SQL Server stored procedures for reporting. We had to reimagine these processes within the cube’s star schema using DAX expressions. Similarly, the Finance Formulas engine built in the legacy system, which allowed users to define various financial formulas, posed a challenge. We had to replicate this functionality without stored procedures, opting instead for cube-based solutions.
As a proof of concept, we started with a simple task: creating an Excel report from the cube displaying Sales and EBITDA per portfolio company. This report offered filtering options by fund, with subtotals for each fund and the selected period.
Throughout the process, we iteratively improved the EDW structure and Cube definition. We gradually tackled more complex reports, incorporating additional filters, currency conversions, and IRR calculations. The journey was fraught with challenges, demanding continuous learning and problem-solving.
The outstanding performance and scalability of the AAS cube impressed everybody involved in this project. This new infrastructure delivers the results to the end user almost instantly while producing similar output in the legacy system using traditional relational database queries was few times slower.
We intend to share our experiences through a series of blog posts, detailing the significant challenges encountered and our solutions. Stay tuned for more insights.
By the conclusion of Phase I, we had developed a fully functional Cube with defined DAX expressions. The client successfully utilized it to recreate most reports from the legacy system in Excel, while also introducing new variations and details as needed.
Looking ahead to the next phase, our plans include further expanding the EDW structure, adding new DAX expressions to the Cube, migrating existing ETL processes from the legacy system, and integrating new ones from external data sources.


Connecting Property Asset Management with Guesty: A Successful Collaboration

February 9, 2024 4:50 pm


We recently took on a new and exciting challenge from a startup client that expanded our portfolio into the realms of Laravel (a PHP framework), MySQL, and the Guesty Open API. This client, a forward-thinking startup, had developed a groundbreaking platform for property asset management, opening up a world of possibilities for property owners. With this platform, users can effortlessly register and oversee all assets, whether owned or loaned, across their entire property portfolio. The comprehensive management suite encompasses asset and inventory management, warranty tracking, maintenance and service history, task scheduling and tracking, document management, and end-of-life alerts. Our innovative platform finds its ideal application in various sectors, including hotels, gyms, multi-family houses, apartment complexes, office and commercial buildings, churches, and residential properties.

Our mission was clear: help our client build an additional module that seamlessly integrates their system with Guesty ( Guesty, a powerful platform designed to supercharge hospitality operations, offers a range of features that simplify the management of rental properties. Property owners can now streamline their business operations, automation, and organization, all from a single platform.

The primary objective was to establish a robust connection between both platforms using Guesty’s Open API, which includes the following key features:

1. Properties Synchronization: This feature ensures that all properties registered for a particular user in Guesty are automatically transferred to their Property Assets Management system account.

2. Calendar Synchronization: With this functionality, users can view property booking dates in the Property Assets Management system’s calendar and efficiently plan service and maintenance schedules. Furthermore, any dates set in the calendar are synchronized back to Guesty, marking the property as unavailable for booking during those periods.

3. Messaging Integration: Guesty boasts an internal messaging system that allows guests to submit requests, complaints, and recommendations to property owners. We seamlessly integrated this system with the Property Assets Management platform, ensuring that messages could be handled appropriately.

In addition to these enhancements, we undertook several bug fixes and improvements to enhance the existing billing, invoicing, and internal messaging functionalities.

While delving into Laravel was a new experience for us, we quickly mastered it and successfully completed the project within the specified timeframe and budget. We found ourselves falling in love with the Laravel framework due to its simplicity, elegance, and remarkable power. It equips developers with all the necessary tools to construct robust and exceptional web applications.

If you have a Laravel project in need of professional assistance, please don’t hesitate to contact us. Our dedicated team is ready to bring your vision to life and exceed your expectations. Together, we can achieve greatness in web development.


Automatically set unique permissions in SharePoint with Microsoft Flow

April 24, 2019 8:15 am

We have a customer who is using SharePoint Online and they recently asked us to find a solution for the following situation in the Documents library.

There are a couple of root-level folders which are shared with specific user groups using a special permission level which doesn’t allow the users to delete those parent folders. However, the customer needs to allow the users to create new sub-folders and also delete them. The problem is that the sub-folders automatically inherit the permission settings of their parent folder which doesn’t allow the delete operation. So, they need to automatically break the inheritance and set appropriate permissions for the new sub-folders.

We found that Microsoft Flow is a great tool which helped us to resolve this problem by automating the process of breaking the default permissions inheritance and setting the desired permissions in SharePoint.

In this particular request we need to programmatically break inheritance of permissions for a given file/folder. Also, we need to assign unique permissions for that file/folder. These operations could be performed programmatically by calling the SharePoint REST API. This is a secured API and all API calls should be authenticated and authorized. This authentication is based on access tokens.

We ran into some peculiarities while implementing the solution and that is why we decided to share our experience here.



Mobile App with Corporate Authentication (Ionic + ASP.NET Web API + OAuth 2.0 tokens + Okta)

December 9, 2015 4:06 pm

Recently we had to build a mobile application and one of the requirements was to implement a corporate authentication for managing the user accounts. We think the approach that we used could be interesting and helpful in similar scenarios that is why we would like the share our experience.

The app that we built is simple and allows pulling and displaying some corporate information for authenticated users. Following are some details about the technologies that we used to build the app:

  • Ionic – We used the Ionic framework to build the mobile app itself. Ionic is a great framework for building hybrid mobile apps. It is based on AngularJS and provides handful components and tools. The apps build with Ionic are easily distributed to iOS and Android devices.
  • ASP.NET Web API – We used the ASP.NET Web API 2.2 to build the server part. It pulls the data from the database and sends it to the mobile app in JSON format by implementing a RESTFul API. It also handles the authentication part by implementing part of the OAuth 2.0 protocol.
  • Okta – Okta is a third-party authentication provider and our client had already been using it for managing their Active Directory accounts.

In short, the mobile app sends the credentials to the API server which is responsible to validate them by communicating with Okta. Once the credentials are validated then it generates a token which is stored on the mobile device and used for further communication between the mobile app and the API server.


TinyMCE in ASP.NET UpdatePanel

December 20, 2014 12:03 pm

TinyMCE is a great HTML editor and it is pretty easy to install and configure the editor on a web page. You should just call the init() function while loading the web page at the client’s browser and the target text-area will be transformed into a nice looking HTML editor.

However, we run into some troubles when we tried to use the TinyMCE editor on an ASP.NET page containing server inputs that are handled by the Microsoft’s UpdatePanel control.


Online Technical Documentation based on WordPress

December 11, 2014 4:32 pm

Recently, we were asked to implement an online technical documentation solution by one of clients. We had to move the existing offline documents (mainly MS Word and PDF files) to an online accessible format that would also allow the users to modify the documents by themselves. We thought about the following options for implementing this:

  • Implement a custom web based ASP.NET solution, starting from scratch
  • Use Wiki engine
  • Use CMS (e.g. WordPress)

We did not choose the custom implementation because it was the most time consuming option. We also did not choose the Wiki software because we found it a bit outdated and too restrictive and finally we focused on the CMS solution.


WIF SSO and Forms Authentication in ASP.NET

December 8, 2014 4:43 pm

One of the projects on which we are working is a long-lived ASP.NET Web Forms system that is customized for a specific client. It is hosted by another company on a server which is external to the client’s environment and it does not have an access to the client’s internal network. The system is built by using the Form Authentication mechanism to authenticate and authorize the users. The list of users and their hashed passwords is stored into the database and the login functionality works in a classic manner – the credentials provided by the user on the login page are validated against the list of users in the database. If the provided credentials are valid then a new Forms Authentication session is established by calling the standard method FormsAuthentication.SetAuthCookie().

Recently, we had to extend that authentication mechanism by adding a single sign-on (SSO) capability which allows the client to integrate the ASP.NET web application with their internal Active Directory (AD) infrastructure. The requirement was to allow some internal employees to access the ASP.NET web application through SSO, but also keep the exiting database login functionality for the rest of the users who are external and they do not have internal AD accounts.

The ASP.NET web application is hosted on an external server and it does not have a direct access to the secured AD infrastructure. After doing some research, we found that in order to connect the external ASP.NET web application to the internal AD environment we can use a middle service called Security Token Service (STS).



HTML 5 – The Offline Challenge

December 19, 2013 3:54 pm

HTML 5 – The Offline Challenge

I. Introduction

Some time ago our team had to add a new feature to one of our web projects. There was a request to add a capability to allow the users to use the application in “offline” mode. It was a great opportunity to dive into HTML 5 and more specifically the “offline” part of it. The new features of HTML5 are pretty interesting, but also “tricky” and this is why I would like to share some of the challenges we met.

First, I would like to say a few words about the technology on which the project is build. That is ASP.NET application which is storing data in SQL Server database. This is not a public application and only authorized users can access it. The system allows the users to go through their business process by entering the information in some structured web forms and at the end it allows them to generate a Word document, which is their final product. The web application is completely client-orientated and there is a huge usage of jQuery and AJAX. The AJAX calls are handled by WCF services. Frankly, this architecture made the implementation of the “offline mode” pretty straightforward.


Sending Email Messages with Embedded Images

December 19, 2013 12:32 pm

Sending Email Messages with Embedded Images

Usually, when we implement email notification from various applications we send emails containing HTML body. And when there should be an image within the email body we put the image on a web server and use a reference to that image in the HTML code. Following as an example of this:

Hello world

 <img alt="sample image" src="http://webserveraddress/Images/sample_image.png" />

The drawback with this scenario is the need for connection to the web server when reading the email. This is causing issues, for example, when the server is part of an intranet network that is not always accessible by the clients. More