SaaS Losers and Winners: Workday and NetSuite

June 29, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

“Well, I’ll be damned
Here comes your ghost again
But that’s not unusual…”
“Diamonds and Rust”, Joan Baez

I’ve said in the past that I consider it a travesty when a SaaS provider doesn’t give you direct access to the data in its native storage, such as by ODBC and OLE DB providers, and forcing you to use file extracts or APIs (often horrible). This greatly inhibits data integration scenarios, such as extracting data for data warehousing. I wrote on this subject many times, including here, here and here. Let’s look at how two popular vendors have approached data access.

SaaS Loser: Workday

Workday is a popular HR cloud platform. Unfortunately, the only way to get your data out is via their horrible APIs, as a large law firm has discovered. If you use Workday, I recommend you explore the Workday Query Language (WQL) so you don’t have to parse the API results yourself. A third-party (CDATA) driver makes the WQL calls easier but if you don’t want to rely on external software, try the following approach.

Workday’s REST API, particularly the /data endpoint, supports WQL queries directly, allowing custom applications to execute WQL without a third-party tool. The /data endpoint (e.g., https://wd2-impl-services1.workday.com/ccx/api/wql/v1/tenant_name/data) accepts WQL queries, including joins based on Workday’s object relationships. Once you enable WQL, you can use ADF or another data integration tool to send SQL-like queries:

sql
SELECT w.Worker_ID, w.First_Name, o.Name
FROM Workers w
LEFT JOIN Organizations o ON w.Organization_ID = o.ID
WHERE w.Active = 1

Results are returned as JSON which you need to flatten as a dataset. At least, you don’t have to parse the horrible JSON nested payload yourself!

SaaS Winner: NetSuite

NetSuite is a very popular ERP software. I’m currently amid a data integration project with NetSuite. NetSuite has provided two interfaces:

REST (and SOAP) API: This is your best bet if you want to automate writes to NetSuite, such as creating orders and invoices. The REST interface also supports sending SQL queries via the SuiteQL endpoint (https://<account_id>.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql). This is a good option if you want to use SQL for lookups and queries that don’t return a lot of data.
ODBC: Use this option to extract massive datasets, such as when loading a data warehouse. To dump some cold water on NetSuite here, not many integration tools come with native NetSuite providers, so you must resort to the NetSuite ODBC driver (this requires a self-hosted runtime if you use Azure Data Factory). Further, since being an Oracle product, NetSuite probably uses an Oracle backend database, performance is suboptimal, as I have come to expect every time I integrate with Oracle. But a better option for sure than dealing with REST APIs.

Live Monitoring of Fabric Mirroring

June 10, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

A current project called for mirroring a Google BigQuery dataset to Fabric. This feature is currently in private preview so don’t try to find it. However, the tips I share here should be applicable to other available mirroring scenarios, such as mirroring from Azure SQL Database.

One of the GBQ tables was a transaction fact table with some 130 million rows. The issue was that the mirroring window would show this table as normally replicating table with Running green status, but we waited and waited and nothing was happening…

First, the “Rows Replicated” counter is not continuously updating (doesn’t report live mirroring info). Instead, it’s updated when the table is fully replicated. That’s because the Delta table operations are atomic in nature.

So, how do we do live monitoring of the initial synchronization then? We can use the Azure Storage Explorer to access directly the delta files of the mirrored database as described here. After some experimenting (shorten the URL below to the workspace name only to see all OneLake artifacts in the workspace), I’ve found that the itemType of a mirrored database is MountedRelationalDatabase. So, the URL to an Adventure Works mirrored database in a workspace named Fabric Tutorial would be
https://onelake.dfs.fabric.microsoft.com/Fabric Tutorial/Adventure Works.MountedRelationalDatabase/

This lets us get to the underlying delta tables and delta logs in Azure Storage Explorer which are of course updating as data is being replicated. Examining the underlying storage is also useful to detect potential issues. Here is an example of the first table that was successfully replicated. Note that there is a parquet file containing the actual data.

And here is an example of the second table. As we can see, the delta log was created but there is no parquet file. So, something went wrong although according to Fabric all is well.

To summarize, the Fabric mirroring UI has left a lot of room for improvement. By examining the delta log file size and delta files of a specific table we can discover how the mirroring session of the initial synchronization is progressing.

Atlanta Microsoft BI Group Meeting on June 2nd (Power BI monitoring with Log Analytics Workspace)

May 26, 2025/0 Comments/in Blog, Events/by Prologika - Teo Lachev

Atlanta BI fans, please join us in person for our next meeting on Monday, June 2nd at 18:30 ET. Akshata Revankar (Senior Data & Applied Scientist with Microsoft) will show us how to streamline and optimize your Power BI environment using Azure Data Explorer (ADX) and Kusto Query Language (KQL). And your humble correspondent will walk you through some of the latest Power BI and Fabric enhancements. For more details and sign up, visit our group page.

Delivery: In-person

Level: Intermediate

Food: Pizza and drinks will be provided

Agenda:

18:15-18:30 Registration and networking

18:30-19:00 Organizer and sponsor time (news, Power BI latest, sponsor marketing)

19:00-20:15 Main presentation

20:15-20:30 Q&A

Overview: Are you an admin overseeing multiple Power BI workspaces and dealing with the challenge of monitoring activities and performance across all of them in a unified view? This session is designed precisely for you. Join us as we delve into a comprehensive guide on how to streamline and optimize your Power BI environment using Azure Data Explorer (ADX) and Kusto Query Language (KQL).

Speaker: Akshata Revankar (Senior Data & Applied Scientist with Microsoft) has 20+ Years of experience in the data engineering and data reporting space. She has worked with Oracle database, SQL Server, SSIS, Informatica Power Center, Hadoop systems, Qlik and Power BI. Akshata enjoys being in the data space and learning new things.

Sponsor: At CloudStaff, we believe technology should make work easier, not more complicated. We help big companies use AI and automation to solve real problems and make employees’ lives better (CloudStaff.ai).

First Look at Fabric Translytical Task Flows

May 20, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

The May release of Power BI Desktop includes a new feature called Translytical Task Flows which aims to augment Power BI reports with rudimentary writeback capabilities, such as to make corrections to data behind a report. Previously, one way to accomplish this was to integrate the report with Power Apps as I demonstrated a while back here. My claim to fame was that Microsoft liked this demo so much that it was running for years on big monitors in the local Microsoft office!

Are translytical flows a better way to implement report writeback? I followed the steps to test this feature and here are my thoughts.

The Good

I like that translytical flows don’t require external integration and additional licensing. By contrast, the Power Apps integration required implementing an app and incurring additional licensing cost.

I like that Microsoft is getting serious about report writeback and has extended Power BI Desktop with specific features to support it, such as action buttons, new slicers, and button-triggered report refresh.

I like that you can configure the action button to refresh the report after writeback so you can see immediately the changes (assuming DirectQuery or DirectLake semantic models). I tested the feature with a report connected to a published dataset and it works. Of course, if the model imports data, refreshing the report won’t show the latest.

The Bad

Currently, you must use the new button, text, or list slicers, which can only provide a single value to the writeback function. No validation, except if you use a list slicer. From end user experience, every modifiable field would have a separate textbox. Ideally, I’d like to see Microsoft extending the Table visual to allow editing in place.

Translytical flows require a Python function to propagate the changes. Although this opens new possibilities (you can do more things with custom code), what happened to low-code, no-code mantra?

The Ugly

Currently, the data can be written to only four destinations:

Fabric SQL DB (Azure SQL DB provisioned in Fabric)
Fabric Lakehouse
Fabric Warehouse
Fabric Mirrored DB

Notice the “Fabric” prefix in all four options? Customers will probably interpret this as another attempt to force them into Fabric. Not to mention that this imitation excludes 99% of real-life scenarios where the data is in other data sources. I surely hope Microsoft will open this feature to external data sources in future.

So, is the Power Apps integration for writeback obsolete? Not really because it is more flexible and provides better user experience at the expense of additional licensing cost.

In summary, as they stand today, transalytical task flows attempt to address basic writeback needs within Fabric, such as changing a limited number of report fields or performing massive updates on a single field. They are heavily dependent on Fabric and support writeback to only Fabric data sources. Keep an eye on this feature with the hope that it will evolve over time to something more useful.

App Integration Tools and Patterns

May 14, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

“…That’s when the teardrops start, fella…
…Dirty deeds and they’re done dirt cheap”

“Dirty deeds done dirt cheap”, AC/DC

A few months ago, I did an assessment for a large organization seeking to modernize their data analytics processes. As a part of standardizing the toolset, the client was considering Salesforce MuleSoft for both application and data integration. After all, the vendor told them the tool can do “everything”. Indeed, taken from the vendor page, we learn that “MuleSoft is a platform that gives IT the tools to automate everything. This includes integrating data and systems, automating workflows and processes, and creating incredible digital experiences — all on a single, easy-to-use platform.”

Let’s parse this claim.

Defining integration types

Quick as a bunny and excited that I finally have one tool that does it all, we hop to the Gartner’s Magic Quadrant for Data Integration Tools. Surprise, Salesforce MuleSoft is not even there. Where is MuleSoft? It’s on the Gartner’s Magic Quadrant for Integration Platform as a Service and the Magic Quadrant for API Management. Confused? Let’s start with definitions.

Data integration (note the focus on data)

“Gartner defines data integration as the discipline that includes architectural patterns, tools, and methodologies for accessing, harmonizing, transforming, processing, and moving data across various endpoints and infrastructures. It ensures consistent access and delivery of data across different subject areas and structures to meet business and application needs.”

Integration Platform (note the focus on applications)

“Gartner defines integration platform as a service (iPaaS) as a vendor-managed cloud service that enables end users to implement integrations between a variety of applications, services and data sources, both internal and external to their organization.”

API Management (note the focus on APIs)

“Gartner defines the application programming interface (API) management market as the market for software that supports API life cycle stages such as planning and design, implementation and testing, deployment and operation, and versioning and retirement.”

So, should we use MuleSoft for ETL and data integration patterns typically involving large data volumes, such as to load a data warehouse? I say we don’t. You probably could, but it will be like trying to fit a square peg into a round hole. Did that client try it despite my advice against it? They did and came to the same realization. Should we consider MuleSoft for enterprise-level app integration, such as something happening in one system triggers something happening in another system by calling APIs? Probably, that’s what’s designed to do anyway. Let’s choose the right tool for the job.

Application integration done dirt cheap

What about a true application integration scenario? In a current assessment, a client uses a third-party app which stores data in a SQL Server database. Each subsidiary has its own database that tracks invoicing locally through the app. However, a central NetSuite ERP system is used to consolidate invoices from all subsidiaries. So, the integration task at hand is to implement a bi-directional synchronization where each new local invoice (and its associated line items) is exported to NetSuite and the local invoice is then updated with the corresponding NetSuite transaction id (it’s more complicated than this but you get the idea).

Now this is where we can surely use “specialized” tools like MuleSoft, Boomi, Celigo, etc., right? And we surely pay a premium, to the likes of thousands of dollars per month. I don’t claim to know these tools but for the most part they appear to me as nice UI wrappers on top of the vendor APIs. They certainly avoid dealing with the actual API complexities, JSONs, etc, but should you lock yourself and break the bank for small scale app integration? Nowadays, everyone wants to do more with less.

Therefore, knowing and using the Microsoft Azure stack, my personal choice is to first consider Azure Data Factory. True, it’s a generic data integration tool and I must learn how to call the vendor APIs and deal with JSONs but it’s not a big deal (AI to help here). ADF can even do webhooks. And it will save you tons of money.

Atlanta Microsoft BI Group Meeting on May 5th (Optimize Power BI Reports and Semantic Models Using External Tools)

April 29, 2025/0 Comments/in Blog, Events/by Prologika - Teo Lachev

Atlanta BI fans, please join us in person for our next meeting on Monday, May 5th at 18:30 ET. Lakshmana R Boya will show us how to optimize Power BI reports by identifying the performance and size issues using “Measure Killer” community app. Your humble correspondent will walk you through some of the Power BI and Fabric enhancements of late. For more details and sign up, visit our group page.

Delivery: In-person

Level: Intermediate

Food: Pizza and drinks will be provided

Agenda:

18:15-18:30 Registration and networking

18:30-19:00 Organizer and sponsor time (news, Power BI latest, sponsor marketing)

19:00-20:15 Main presentation

20:15-20:30 Q&A

Overview: We will deep dive into how we can optimize Power BI reports and semantic models by identifying the performance and size issues using the “Measure Killer” app.

Sponsor: Prologika (https://prologika.com) helps organizations of all sizes to make sense of data by delivering tailored BI solutions that drive actionable insights and maximize ROI. Your BI project will be your best investment, we guarantee it!

Two Takeaways from FabCon 2025

April 4, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

The 2025 Fabric Community conference has just concluded. There were many announcements but I found two of them intriguing to make a case for Fabric more compelling should a suitable project comes around:

OneSecurity – Analysis Services Tabular and Power BI semantic models have long supported a comprehensive and flexible row-level security (RLS), such as to support scenarios requiring dynamically denying rows. Unfortunately, column-level security (aka object-level security) has been less flexible as I explain in more detail here. I hope OneSecurity will prove more flexible over time. Currently, it doesn’t support dynamic security, but I hope it eventually will, such as by allowing a SELECT statement to evaluate runtime conditions. So, keep an eye out for OneSecurity as it evolves over time if you face more complex column-level security requirements.
Copilots for all F SKUs – Previously, I complained that Microsoft has established that AI must require premium pricing starting at $5,000/mo (F64 and above). But now they’ve promised that by the end of April copilots would go mainstream and available in all F SKUs should you need AI assistance.

Alas, no major announcements about Fabric Warehouse. As with Synapse, Microsoft went back to the drawing board with Fabric Warehouse. Consequently, the its T-SQL surface area doesn’t support various features I use. For example, IDENTITY and MERGE were promised long ago but unfortunately didn’t materialize. I personally don’t care for the most part about relational-like lakehouses or feature bundles. I don’t dare even dream about “advanced” features that SQL Server had for a long time, such as multiple databases, temporal tables, etc. Without robust warehousing, I still find it difficult to substantiate a good case for Fabric for most of my projects.

Atlanta Microsoft BI Group Meeting on April 7th (Source Control for Power BI)

April 1, 2025/0 Comments/in Blog, Events/by Prologika - Teo Lachev

Atlanta BI fans, please join us in person for our next meeting on Monday, April 7th at 18:30 ET. Neel Pathak will explain how to do source control for Power BI with Git integration. Your humble correspondent will walk you through some of the Power BI and Fabric enhancements of late. For more details and sign up, visit our group page.

Presentation: Source Control for Power BI
Delivery: In-person
Level: Intermediate
Food: Pizza and drinks will be provided

Agenda:
18:15-18:30 Registration and networking
18:30-19:00 Organizer and sponsor time (news, Power BI latest, sponsor marketing)
19:00-20:15 Main presentation
20:15-20:30 Q&A

Overview: The days of sharing pbix files and copying in changes are gone. Git integration now enables development teams to collaborate and provides version control/rollbacks.

Speaker: Neel Pathak (Senior Consultant with 3Cloud) a BI and analytics professional with an expertise in Power BI and guiding clients through their analytics transformation journeys.

Sponsor: 3Cloud is the #1 Microsoft Azure partner. No other partner combines 3Cloud’s expertise in Azure with a deep understanding of infrastructure, data and analytics and app innovation. We have proven our impact and are trusted with client’s most complex business problems. We look for people who share our passion for technology and want to unleash their potential.

Hacking Adventures: Automating Button Click

March 15, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

Sometimes you may encounter annoying training content that forces you to watch a section before you can explicitly advance to the next one. Never understood why I have to do this or sit for long trainings altogether if I already know the answers and can pass the naive quizzes. Usually, a web button to continue gets enabled after you watch a video or after some time elapses.

With some investigative work and low-code JavaScript, you can easily “automate” the process to some extent, such as until it’s time for the quiz, as follows:

The trick is to find the id of the button and what makes it enabled/disabled. In the Edge browser, right click the “button” and then click Inspect. This will open the Dev Tools and you’ll will see the button HTML definition. You can right-click it on the HTML code and then click Copy OuterHTML to paste it in Notepad to inspect it. In my case, the element id is “button-next”. When the button is disabled, the app adds an attribute disabled as follows:
<button … disabled=”” type=”button” id=”button-next”>…</button>
In the browser Dev Tools Console (F12), copy and paste the following JavaScript code, and then press Enter (note that you must execute allow pasting before you can copy and paste code for the first time as the Console will inform you):
```
function checkAndClick() {
const targetElement = document.getElementById('button-next'); // Get the element by ID

if (targetElement) { // Check if the element exists
if (!targetElement.hasAttribute('disabled')) { // Check if it does NOT have the 'disabled' attribute
   targetElement.click(); // Simulate a click
   console.log('Element clicked!');
} else {
  console.log('Element is disabled.');
}
} else {
    console.log('Element not found.');
}
}
// Set an interval to run the function every 2 seconds
const intervalId = setInterval(checkAndClick, 2000);
// clearInterval(intervalId); Disable the timer when no longer needed
```
This code watches every two seconds if the disabled attribute is removed from the button, in which case it “clicks” the button to advance the content. Hope this will save you precious time for more productive work. Happy training!

LLM Adventures: xAI Grok

February 18, 2025/0 Comments/in Blog/by Prologika - Teo Lachev

I’ve been recently impressed with xAI Grok compared to Microsoft Copilot. Grok performs better that Copilot which seems to be crippled with political correctness to a point of absurdity although it seems to be getting more permissive of late. But the most important Grok advantage is that it’s always recent… and it doesn’t crash every now and then.

A couple of examples:

Question: Where is this image from?

1. Microsoft Copilot – what exactly is inappropriate is not clear.
2. Grok (impressive!) – This image depicts the rock formations and cave dwellings of Cappadocia, a historical region in central Turkey. The unique rock structures and the cave homes carved into them are iconic features of this area, particularly around the town of Göreme.

Question: What are the most important political events to watch for tomorrow (as of 2/18/2025)?
1. Microsoft Copilot (chaos of past and current events):
  1. Presidential Primaries: Former President Donald Trump and President Joe Biden have both secured enough delegates to become their parties’ presumptive nominees for president. The primaries in Georgia, Mississippi, and Washington state have been particularly significant.
  2. Senate Hearings: There are ongoing confirmation hearings for various cabinet positions, including the Department of Homeland Security and the Department of Housing and Urban Development.
  3. International Relations: Discussions and negotiations on international issues, such as the ongoing conflict in Ukraine and relations with China, are likely to be in the spotlight.
  4. Domestic Policy Debates: Debates on domestic policies, including immigration reform, climate change, and economic policies, will continue to be important topics
2. Grok – Based on the available information, here are some of the most important political events to watch for on February 19, 2025:

- 1. Pat King’s Sentencing in Canada: This event is noted in posts on X, indicating its significance as people are discussing its impact. Pat King was involved in the Freedom Convoy protests, and his sentencing could have broader implications on discussions around civil liberties and protest laws in Canada.
  2. Global Political Dynamics: Although not specific to February 19, the context from web sources suggests a focus on ongoing geopolitical tensions and diplomatic engagements. For instance, the broader political calendar highlights events like the NATO defense ministers’ meeting in Brussels, which might influence or be influenced by daily developments in international relations.

I’m looking forward to Grok 3!