Caffeinated Wisdom

How to embed PowerFX in NET app

2025-12-22T21:17:00.000-05:00

How to embed PowerFX in a .NET Application

PowerFX is a language from Microsoft aimed at power users. Writing PowerFX feels like Excel formulas, and one of the building blocks of PowerFX is actually “formulas” that are reactive to other formulas. Microsoft uses PowerFX mainly as the low code language for the PowerApps offering.

A few years ago, Microsoft open sourced PowerFX with the goal of letting others take advantage of the power and simplicity of the language. The main repo is located at https://github.com/microsoft/Power-Fx . The repo contains a link nice introductory video about the what, why and how of PowerFX (https://www.youtube-nocookie.com/embed/ik6k89WNjuk ).

If you are a .NET developer, you too can take advantage of the power and simplicity of PowerFX by embedding a .NET PowerFX Interpreter created by Microsoft. One use case for embedding PowerFX in a .NET app is to allow Business Users to define Business Rules in PowerFX and then integrate them into your app. This allows the Business users to maintain the rules while you maintain the hosting app.

To use the interpreter:

Add the Microsoft.PowerFx.Interpreter NuGet to your project ( https://www.nuget.org/packages/Microsoft.PowerFx.Interpreter )
Instantiate an object of type RecalcEngine.
Add formulas by passing the formula as a string to the SetFormula method of RecalcEngine.
Notice that adding Formulas does not execute them. To make the engine execute the formulas, call RecalcEngine’s Eval (or EvalAsync) method, passing a string representing the expression to execute. The expression can reference formulas added with SetFormula.

Here is the minimal C# example:

using Microsoft.PowerFx;
using Microsoft.PowerFx.Types;

var engine = new RecalcEngine();
// notice the escaped quotes as we are passing a PowerFX string literal. Our second parameter needs to be
// a valid PowerFX expression.
engine.SetFormula("MyName", "\"Juanito\"", OnFormulaUpdate);
engine.SetFormula("Greeting", "\"Hello, \" & MyName & \"!\"", OnFormulaUpdate);
var greetingResult = engine.Eval("Greeting");
Console.WriteLine(greetingResult.ToObject());
return;

void OnFormulaUpdate(string arg1, FormulaValue arg2)
{
// this method is called whenever the formula is evaluated by the engine
}

I have been working on a new app ( https://github.com/TheJuanitoLearnsShow/PuppyWorkbooks ) that allows users to maintain an executable workbook containing PowerFX formulas. The app and its tests provide examples of how to use the interpreter. The repo has examples of how to create your own PowerFX function to be used by PowerFX expressions, a future post will cover the steps on how to accomplish that.

How to read build variables in Source Generators

2025-12-10T22:51:19.704-05:00

How to read build variables in Source Generators

I used GitHub copilot to help me create a source generator that reads the schema of a database and generates C# ADO.net code that can call SQL Stored Procedures, Views and Functions in that database (https://github.com/TheJuanitoLearnsShow/Puppy.Ado.SourceGenerator). The connection string to the database is set on a build variable in the consuming csproj file. The problem is that copilot missed the step described in Microsoft’s Source Generator Cookbook . The missing step is to add a CompilerVisibleProperty element to an ItemGroup for each of the build variables you want the source generator to use.

For example, my source generator uses two build variables: DbGen_EnableLiveSchema and DbGen_ConnectionString, so they are defined in the PropertyGroup element in the consuming csproj file:

<DbGen_EnableLiveSchema>true</DbGen_EnableLiveSchema>
<DbGen_ConnectionString>Data Source=.\sqlExpress;Database=AutomatedTESTS_AdoGenerator_SampleDb;Integrated Security=True;TrustServerCertificate=True;</DbGen_ConnectionString>

In addition to the above, I have to add the corresponding CompilerVisibleProperty elements in that same csproj file to allow the source generator to read those variables via the AnalyzerConfigOptionsProvider’s GlobalOptions dictionary:


<ItemGroup>
    <CompilerVisibleProperty Include="DbGen_EnableLiveSchema" />
    <CompilerVisibleProperty Include="DbGen_ConnectionString" />
</ItemGroup>

C# code reading the variables:


    var options = context.AnalyzerConfigOptionsProvider.Select(
        (p, _) => new GeneratorOptions(p));
…
public GeneratorOptions(AnalyzerConfigOptionsProvider provider)
{
    var global = provider.GlobalOptions;
    global.TryGetValue("build_property.DbGen_EnableLiveSchema", out var enable);
    global.TryGetValue("build_property.DbGen_ConnectionString", out var conn);
    EnableLiveSchema = bool.TryParse(enable, out var b) && b;
    ConnectionString = conn;
}

APIM requests not showing downstream requests in application

2025-11-05T21:45:00.000-05:00

APIM requests not showing downstream requests in application insights

In Application Insights, if you are not seeing the telemetry for the backend service being called by APIM, it could be you have the wrong type of correlation type option set under that API’s settings in APIM. The correct setting, for newer web apps that use the latest open telemetry or application insights nugets should be W3C. The default value is Legacy so you will need to change it to W3C.

Without that correlation, the appropriate headers will not be included in the request to the backend service from APIM and the two parts of the request would appear as separate flows inside Application Insights.

Expose SQL Logic as APIs - Domain Driven Design in SQL

2023-06-28T21:06:00.005-04:00

Most Domain Driven Design articles and books teach you to develop your business logic in such a way that there is no trace of a database or other dependency that would make your business logic (also known as Core code or Domain Logic) impure. That is great advice if you are developing your core business logic in a programming language that is not SQL. But what if all you know if SQL or SQL is your strong skill? The answer is yes, you can develop your domain logic in SQL in a domain driven way, if you try to adhere to the spirit of the DDD concepts.

Objects or Tables?

One of the main arguments for developing Domain Logic in Object-Oriented languages is that “real” world things are objects and thus is easier to model those real-world things in classes. But if you are in an organization and you start talking to Business Units about the logic they need, you would find that the concept of “spread sheets” are actually very familiar to them. It is so familiar, that countless of Business Units across history have tried to solve their problems but modeling their logic in Excel Spreadsheets. Thus, rationalizing the problem domain at hand in terms of tables (a.k.a. spreadsheets) is not very far of how a businessperson thinks day to day when solving their problems.

It might even make your domain easier to explain to a Business Unit.

SQL features friendly to DDD

Beyond tables, SQL allows us to also model relationships. Relationships are an important Business concept as “things” in Businesses do not exist in a vacuum.

Most SQL variants (such as T-SQL) also allows us to avoid Primitive Obsession via custom types that we can define to model Business values. For example, instead of using varchar(50) we can define a custom type VendorName and use it in our tables and stored procedures to make clear the value represents a Vendor’s name not just a random varchar with max length of 50 characters.

Another tool that SQL provides to ensure our domain modeling is enforced, is the use of check constraints.

Granted, a lot of the features we discussed are also found in OOO languages, but the point is that SQL also offers them.

One database, one Bounded Context

One popular advice in the Micro Services realm is that each Micro Service needs its own database that one that service can access. What if the database itself is the service? And if we follow another popular opinion that a service should implement only one DDD Bounded Context (Designing a DDD-oriented microservice | Microsoft Learn) , then it follows our database should only model one Bounded Context.

Does it mean we expose everything in the db as a service? No, instead we should have at least two schemas (or the equivalent of a MS SQL schema in other RDBMS systems):

One schema that acts as our internal implementation that we can change at will (much like the private members and classes in typical programming languages).
And another schema that acts as our contract to the callers outside our database. This schema serves the purpose of the public members/types in other programming languages.

This collection of Stored Procs, types and Views that compose your “public contract” schema should not change much as it is what you are agreeing the callers can expect to interact with when working with the logic and data in your database. This will free you to make any changes you need to your objects in your internal schema because you know that as long as your public API schema is complying with the contracts established, your callers will not break.

Less Risky Deployments

One argument against database deployment is that they are risky. And sometimes they are because the databases that are being deployed are big in the sense that are trying to model multiple bounded contexts and thus the risk is that one thing can break another seemingly unrelated thing.

But the same is true if you are going to deploy a large application, if the application is one single unit of deployment. And just like the solution for reducing the risk of deployments in applications is to try to break the application into smaller, independent parts, we can mitigate this risk in the database world by limiting the database to modeling only one Bounded Context.

There are also ways to automate the deployments of the changes from a Repo, just like other programming languages have.

Breaking Schema Changes

Some of you might be wondering: what about breaking changes? And that question might come to mind because you might have experience in the past a painful task of a database change that broke something. The question is how do other programming languages deal with breaking changes? The answer for them also applies to SQL: a versioning strategy.

For example in C#, there are three types of changes to a class: removal of members, changing types or names of members and addition of members. Additions usually do not break things, but changes and removals do. And although is OK in C# to do those destructive changes in the internal implementation of your service, you better not break your public classes that define your public contract. And if you do, the most common solution is to version the library or the classes themselves.

A similar solution can be applied to SQL. Your objects in your “private” schema can have destructive schema changes as long as your do not violate your public contract. As for data loss, if a change would incur data loss and the data needs to be retained, it could retained in a table whose purpose is to archive that data or to have a separate database whose mission is to keep an archive version of that data. It all depends on the requirements of the business for that data that would be lost. As for changes to your public contract, just like in services implemented in other languages, you would have to adopt a versioning strategy with either having a separate stored proc for older versions or a separate db altogether. It all comes down to what makes more sense for the specific situation.

Unit Testing

Although not as polished as other languages, there are ways to create quick unit tests in SQL and have them run by a tool or command in the CI/CD pipeline. But if you touch the db then is not a unit test anymore! Well, technically, even though your unit tests might involve temporarily storing data into tables, your test are still self-contained in transactions. And they might not run as fast as their .NET counterparts, but they would be fast enough to give you a good test dev loop.

To get started with unit testing, you can start simple, by having a schema for Test objects. Then define in that schema your Stored procs that will implement the test. Inside those procedures, you can do the typical Arrange, Act, Assert steps for the test and wrap them in a transaction to isolate the test. To run the test, just run the stored proc.

Conclusion

In a future post and video, we will try to show an example of a database that follows the above advice.

Expose SQL Logic as APIs

2023-06-26T21:49:00.003-04:00

HTTP-based APIs have become a very popular way of exposing functionality to external entities outside an organization. For example, in order to support certain features of their products, some Vendors require integrating with your data real-time by calling HTTP APIs to get the information they need. Or maybe you have a very tech-savy customer that would like to get their data via APIs your organization exposes.

Typically, a Development Team spins up those APIs using programming languages and platforms such as .NET and Java. But what if your organization only has Microsoft SQL developers? In this series of posts, I would like to propose that they are able to encapsulate the needed logic into T-SQL stored procedures and/or views without the need of the work of a typical development team. This is great for those “multiple hats” DBAs who might not be strong enough in regular languages, but with good enough skills to do some basic managing in Azure.

Here is an overview of the process:

Design/Develop your database to address the use case. Follow Domain Driven Design guidelines in SQL as if it were a regular programming language by using separate schemas for public and private objects and keeping logic and db limited to one Bounded Context.
Publish your database to a SQL server that can be accessed by Azure. This could be an Azure SQL db, managed instance, Azure VM or an on-prem SQL db (if you have Network connectivity between Azure and your on-prem data center).
Build the HTTP-triggered Logic app. Logic Apps can be built using a graphical interface, no need for code except for some formulas. This logic app will need to call your SQL server. There are many safe ways to authenticate the Logic App with your SQL server. The logic app will take care of transforming the data from the Stored Procedures/Views
Expose the Logic App via Azure APIM. This product will also allow you to add extra features to your API such as rate-limiting, subscription keys, analytics without code except for some XML and JSON (JSON for defining your OpenAPI schema).
Implement security in layers, by:
1. Limiting what can access your SQL server (the logic app)
2. Limiting what can access your Logic App (the APIM)
3. Setting up OAuth Client Credentials for your consumers by using Azure AD and APIM token validation policies.

The Azure offerings mentioned in the above list have either a free or reduced cost pricing option, making this approach inexpensive. In future posts we will dig deeper into each of the items in the list. For a video series please see: https://youtube.com/playlist?list=PLVObt25IiZ6gxbnlGMIDNa_iquKiBjUmo

2023-05-19T09:05:00.002-04:00

How can you allow a Business User to maintain the business logic of your application? If your business users are Excel-savvy (as much business users are), then you could use Microsoft’s Graph API to access and run the logic within those Excel workbooks (workbook resource type - Microsoft Graph v1.0 | Microsoft Learn).

This is not the OLE Automation of years past. We are talking about calling an HTTP based API.

The approach is simple, from the business user side:

Have the business users create the workbook and adjust it so that it performs the business rules it needs. They can designate some of the cells to be used as the “parameters” for the business logic. For example, given the values of cells A2 and B2, run a series of formula calculations whose final result is in cell B30.
Save the excel workbook in a folder in your business’ OneDrive.

Then, from your application code, make a series of HTTP calls to the MS Graph API:

Get the Auth Token
Call the createSession endpoint to create a non-persistent session. The non-persistent session will prevent changes from being saved but still allow you to get the calculated results within the session. E.g.:

POST https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/createSession

Call the worksheets endpoint with a PATCH verb (make sure you pass the session id of your non-persistent session) and the values to use to update the cells your business user designated as the “parameters”. E.g.:

PATCH https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='A3')

Read the “results” cell(s) from the workbook by calling the worksheets endpoint with a GET. E.g.:

GET https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='B8')

1. Again, make sure you pass the session id of your non-persistent session.

Having your business rules encapsulated in an Excel workbook would allow your business users to ensure the business logic is always right. Please note that this approach requires the following:

The Business User is now responsible to ensure the logic is correct, which should be fine as they are the domain experts. What the business units are probably not familiar with is versioning of that logic. Thus, it is important to help them create a flow where the “ready” version of the Excel file gets delivered to the appropriate OneDrive folder so that unfinished or incorrect files are not used by the application.
You can still do integration testing from your code to test the accuracy of the business logic in an automated way. This means that you still need to talk to the business users and understand the success and failure conditions and create automated tests for that. This is not different from your unit tests on your domain logic code, with the exception that you are now calling APIs (i.e.: integration tests). But you should only need to do that testing when the business unit passes down in the flow a file ready to be used. Again, it is highly important to establish the proper “release” flow with the business.

Sql Server Projects (sqlproj) deployment via CLI

2023-04-12T21:03:00.001-04:00

Sql Server Database projects are a great way to define your database structure and keep in in source control. Instead of maintaining a large list of migration scripts, you can instead define your database declaratively in the Sql Project as if you were creating a .net solution. This project style also facilitates the publishing of changes to Sql Servers. The publish process automatically detects what needs to change and creates the necessary Sql code to perform those changes.

One of the challenges of using Sql Server Database projects is to include the Sql Project in your CI/CD pipeline so that it can automatically publish the changes to a Sql Server.

Microsoft has migrated the Sql Server Database project format to the new “SDK style” project (introduced by .NET core).

A generic way to integrate it is to call the commands from the command line as most CI/CD pipelines support some sort of command line invocation. The steps to run from the pipeline are:

Build the project so that it generates a dacpac file
Install the SqlPackage dotnet tool
Run the SqlPackage tool with the “publish” option to publish the dacpac to the desired Sql Server.

The actual commands look like the following (assuming your database is named MyDb and your project is called MyDb.sqlproj):

Cd <folder where your sql project is>
dotnet build /p:NetCoreBuild=true /p:Configuration=Release
dotnet tool install -g microsoft.sqlpackage
SqlPackage /Action:Publish /SourceFile:".\bin\release\AdventureWorksLT.dacpac" /TargetConnectionString:"ConnectionString to the db you want to publish to”

Microsoft For the older style of project (i.e.: non sdk style sqlproj), you can replace the second line with:

msbuild "nameofsqlproject.sqlproj" /p:Configuration=Release

References

Build a Project from the Command Line - Azure Data Studio | Microsoft Learn

SqlPackage Publish - SQL Server | Microsoft Learn

Can use Integrated Security via .net but not via JDBC

2021-11-08T19:29:00.007-05:00

I encountered an obscure issue when trying to connect to a SQL Server database using Windows Integrated Authentication from Java. My JDBC URL was correct but I kept getting the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

And the SQL logs in the SQL server were showing the following:

SSPI handshake failed with error code 0x80090346, state 46 while establishing a connection with integrated security; the connection has been closed. Reason: The Channel Bindings from this client are missing or do not match the established Transport Layer Security (TLS) Channel. The service might be under attack, or the data provider or client operating system might need to be upgraded to support Extended Protection.

The puzzling thing was that I was able to connect fine suing the system.data.sqlclient from .net and from SQL Server Management Studio from the same machine and domain account that was getting the JDBC error. In addition, Integrated Authentication was working fine when connecting to other SQL servers. So, it had to do something with that particular server. After searching in the internet, I found that the issue is due to the JDBC not implementing the Channel Binding feature needed to connect to SQL servers whose Extended Protection is turned on. The native SQL Client does implement that feature and because the system.data.sqlclient from .net and SSMS use the native SQL Client under the covers, the connection for those succeeds. The following link takes you to the issue in GitHub for this scenario: https://github.com/microsoft/mssql-jdbc/issues/963

First Twitch Stream

2020-08-20T20:12:00.001-04:00

Inspired by what Jeff Fritz (https://www.twitch.tv/csharpfritz) said on .net rocks (https://www.dotnetrocks.com/?show=1575), I decided to try doing a very small F# tutorial using Twitch:

https://www.twitch.tv/videos/716521139

How to debug workflow console projects in Visual Studio 2019 using Designer Breakpoints

2020-08-07T10:32:00.007-04:00

I ran into an issue with debugging a Workflow Foundation workflow by using breakpoints in the designer in Visual Studio 2019. The breakpoints in the designer were not being hit when running the console workflow program in debug mode.

A question posted by another member of the community at https://developercommunity.visualstudio.com/content/problem/846593/not-able-to-debug-windows-workflow-designer.html led me to another link at https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/retargeting/4.7.2-4.8#windows-workflow-foundation-wf.

In that last page, at the bottom, under the "Workflow XAML checksums for symbols changed from SHA1 to SHA256", I found the piece of configuration code to add to my app.config file in the console workflow VS project. The following is the full configuration of my project that allowed VS to pause at the designer breakpoints:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
</startup>
<runtime>
<AppContextSwitchOverrides value="Switch.System.Activities.UseSHA1HashForDebuggerSymbols=true" />
</runtime>
</configuration>

Previously trusted client certificate not longer trusted by IIS

2020-06-17T14:38:00.000-04:00

I was getting the following error from iSS when testing locally a web api that requires ssl client certificates:

403.16 The client certificate used for this request is not trusted by the Web server

One way to troubleshoot is to use the certutil tool that comes with Windows. There is a verify switch that you can use to verify the public portion of the client certificate:

certutil -f -urlfetch -verify "public client cert.cer"

The tool will show any errors encountered when validating the cert, such as a failure to lookup the revocation list (CRL) of the issuer.

You can also capture the details of the client certificate, like the subject name, issuer and serial number, by adding custom fields to your IIS log. Instructions are here and the fields are under "Server Variables".

The specific issue I was having, however, is described in the answer to https://stackoverflow.com/questions/26247462/http-error-403-16-client-certificate-trust-issue . I had a Trusted Root cert that was invalid. After removing that cert from the Trusted Root Store at the computer level, the IIS was able to trust my client certificate. The interesting thing was that the Trusted Root cert I removed was not related at all to my client cert, so IIS was basically just failing because of an unrelated cert.

Power Automate: SFTP action "Test connection failed"

2020-05-26T14:38:00.002-04:00

When I added an SFTP create file action to my Power Automate flow (https://flow.microsoft.com) , I got the following error in the action step, within the designer:

"Test connection failed"

To troubleshoot the Power Automate connection, I had to:

go the Power Automate portal
then "Data"->"Connections"
the sftp connection was there, I clicked on the ellipsis, and entered the connection info

It turns out, that screen provides more details about the connection error. In my case, it was complaining that "SSH host key finger-print xxx format is not supported. It must be in 'MD5' format". I had provided the sha fingerprint that WinScp shows. Instead, I needed to use the MD5 version of the fingerprint. To get that, I had to run in command line (I was in a folder that had openssh in it):

ssh -o FingerprintHash=md5 mysftpsite.com

To get the fingerprint in MD5 format. I took the string (without the "MD5:" part of the string) and put it in Power Automate where it asks for the host fingerprint.

Windows Workflows Foundation in Azure Functions

2020-05-25T14:58:00.002-04:00

Unfortunately, I have not been able to make the CoreWf library (https://github.com/UiPath-Open/corewf) run on an Azure v2 or v3 functions. Something about the Azure function runtime not able to fins the dependencies like System.Xaml or System.CodeDom.

However, Azure v1 functions (which run on .net 4.6.1) have no problems running a Workflow created using the Workflow Designer in Visual Studio. A sample of such function is in https://github.com/jptarqu/WorkflowForCorePlayground/tree/master/src/AzureSamples .

I did run into an issue creating from Visual Studio the Function App Service with runtime version of "1". I was seeing "runtime: error" in the portal's overview page for the app service. To work around that issue, I created the function app service inside the Azure Portal and then changed it to version 1 inside the portal (the default is v3 and you cannot change it until the App Service is created).

Workflows using CoreWF in .net core

2020-05-21T20:53:00.001-04:00

Workflow Foundation is a great tool when designing workflows for .net. Unfortunately, just like WCF, that technology is not going to be ported to .net core. However, a company named UI Path (https://www.uipath.com/) that heavily relies on Windows Workflow Foundation, has started an unofficial port to .net (https://github.com/UiPath-Open/corewf).

In order to see how compatible would be the the new port with the current Windows Workflow for simple workflows, I created a playground project at https://github.com/jptarqu/WorkflowForCorePlayground . The Visual Studio solution contains two projects:

WorkflowForCorePlayground : a VB.net Windows Workflow project created using the VS template for .net 4.8.
CoreWfSample : a .net core console app that runs a cleaned version of the same xaml that project #1 has.

The idea is to explore the possibility of still having business users design the workflow using the regular .net framework 4.8 beautiful and easy-to-use designer, while still been able to run the workflow in .net, in for example an Azure Server-less Function. Obviously, not all Workflow Activities would work with .net core, but hopefully simple workflows and flow charts encapsulating business rules can be used.

In order to use the CoreWF you must install the nuget in your project. But the nuget does not seem to be in Nuget.org, so you have to add the following url to yur nuget sources in Visual Studio:

https://www.myget.org/F/uipath-dev/api/v3/index.json

The .net core project works without needing to modfiy the xaml. I was able to copy and paste the xaml code from the xaml generated by the visual studio designer into the xaml file in my .net core.

My next experiment is to try creating an Azure Function that runs the xaml workflow.

Inexpensive, Non-realtime Sites using Queues

2014-10-11T09:26:00.002-04:00

A lot of the sites for small and medium businesses rely on dynamic engines (such as ASP.NET, PHP, etc) to generate most of the content at run-time. This kind of typical setup requires expensive hosting (relative to a small and medium size company) and (if not designed properly) may not perform very well. I would like to argue that most of those sites could be served with static HTML pages generated offline at different time intervals (either fixed or on-demand intervals, discussed later) and a Javascript Client front end that communicates with a slim server side service whose only job is to store commands from visitors into a queue. The queue can then be read and processed by non-hosted computers, freeing the business from having to pay extra hosting fees for application server.

The costs are reduced by the following:

Static HTML sites mean less power required.
Queue storages are usually cheaper services than full SQL Databases.
A slim service that just relays the commands from the user to the queue and from the queue to the user should require less power than a full blown Web Application.

Static HTML sites do not require a high end server to achieve great performance. In fact, static HTML sites can take advantage of CDN and client caching. Many cloud services such as Google App Engine provide a generous quota for static content. To generate the static site, one could use many of the static site generators out there, one example is Jekyll.

A lot of user interaction in a small or medium business’ (SMB) public site do not require the ACID properties of SQL Databases. A simple Queue can reliably ensure storage of the visitor’s request for future processing, for example, a customer’s request to buy a product only needs to be stored in the Queue for the off-cloud Application Processor to retrieve it from the cloud’s queue and then do what it needs to do to process the sale. Cloud SQL databases can be expensive (e.g SQL Server on Azure), but queues such as GAE’s Queue are inexpensive and have quotas for free usage.

By having as little code running in the cloud, the cost of processing minutes can be saved. In the customer purchase example, the code to process the sale does not need to live in expensive clouds, it could live in an on-premises regular computer that checks the queue every so often. The computer that does the heavy processing can also be placed on another cheaper hosted environment if that is required by the business needs. The point is that your public facing site (the front line of your business) is as quick as possible and your back-end services (that do not require real time responses) can be placed in inexpensive environments (on-premises or a hosting provider).

Obviously, an application must be designed to make reliable Application Processors that can scale horizontally (i.e. more Application Processors can be added without impacting reliability). The back-end database to which the Application Processors connect can still be a SQL ACID database, but it does not need to be in the cloud anymore. In the customer purchase example, the sale and its details would go to its normal SQL Database after processed.

In the next posts, I will be linking to examples of sites with content that take advantage of content generated at deployment time. I will also linkt o examples using slim services that take advantage of cheap or free code hosting such as Google App Engine and Azure Websites.

R Cookbook

2014-06-02T20:40:00.000-04:00

The R Cookbook by Paul Teetor (O'Reilly Media) aims and succeeds in introducing programmers to the technical aspects of the R language. R is a programming language designed for Statistical work, therefore it might not share too much ground with other typical languages like Python or C. The R Cookbook gives a tour to programmers to this wonderful language, from the very screen output beginnings, through ploting, to the creation of statistical models and Time Series.

The author does a great job exposing the differences and similarities of R to other languages. For example, the author, explains how in R the single brackets applied to a vector (myvector[1]) mean "give me a vector of one item from myvector", he then explains that the correct way to get a single item is myvector[1]. Comparisons to other languages like SQL are dispersed through the chapters.

Mr. Teetor provides a throughout explanation of other aspects of R that might not be common in other computer languages. For example, in Chapter 5, he explains in detail and with clear examples what the Recycling Rule is. I doubt I would have been able to grasp such concepts so easily without an explanation like his.

This book however, is not a book about Statistics. The author goes ankle deep in Statistics Theory, just deep enough to have a general understanding of the examples. That is fine because he even states at the beginning of the book that this is not a book on Statistics. There are other books that deal with the theory, some of them are referenced in the book as a pointer to further reading. I recommend reading an introductory Statistical analysis book such as Data Science for Business before reading the cookbook.

This book fits very nicely as a step between an introductory book to Statistics and a book for Regression Modeling. I recommend this book for those programmers that have some basic notion of Statistical modeling and want to learn how to implement those concepts in R. This is exactly the place I was when I started reading the R Cookbook.

List to ICollection AutoMapper and WebAPI problem

2014-05-29T15:08:00.004-04:00

I was getting an exception from Automapper when it tried to map a List to an ICollection. The problem was that WebApi was building the MyClass from the xml sent by the client and instead of using a List for the ICollection property, it used a plain array. When Automapper tried to map the List to the Array, it failed because even though Arrays are ICollections, they do not support some of the ICollection operations, such as Add.

To solve the problem, I used Json in the client side when posting. After that, webapi chose to use List instead of an Array when the data came as Json.

Cryptic message when serializing Computed Observable to JSON

2014-05-29T12:41:00.005-04:00

If you have an error in one of your computed observable in Knockout.js, the toJson function will set the value of that property to a cryptic script snipped when serializing it to JSON. For example:

var SortField = function (expression) {
        this.FieldDef = ko.observable(null);
        this.SortOrder = ko.observable("");

        this.ExpressionType = "SortField";

        this.Label = ko.computed(function () {
            return this.FieldDef() == null? "" : this.FieldDef().Label + ' ' + this.SortOrder();
        }, this);

    }
var FieldDefinition = function (table_name, field_name, table_alias) {
        this.TableName = table_name;
        this.TableAlias = ko.observable(table_alias);
        this.FieldName = field_name;
        this.Label = ko.computed(function () {
            return this.TableAlias() + '⇨' + field_name;
        }, this);

In this example SortField’s Label function is not calling the Label function properly from the FieldDef property. If you serialize to JSON, you will get the following:

function dependentObservable() {\n        if (arguments.length > 0) {\n
 if (typeof writeFunction === \"function\") {\n
     // Writing a value\n
     writeFunction.apply(evaluatorFunctionTarget, arguments);\n
 } else {\n
     throw new Error(\"Cannot write a value to a ko.computed unless you specify a 'write' option. If you wish to read the current value, don't pass any parameters.\");\n
 }\n
 return this; // Permits chained assignments\n        } else {\n
 // Reading the value\n
 if (!_hasBeenEvaluated)\n
     evaluateImmediate();\n
 ko.dependencyDetection.registerDependency(dependentObservable);\n
 return _latestValue;\n        }\n    } ASC"}],"ExpressionType":"Hierarchy","Label":"function dependentObservable() {\n        if (arguments.length > 0) {\n
 if (typeof writeFunction === \"function\") {\n
     // Writing a value\n
     writeFunction.apply(evaluatorFunctionTarget, arguments);\n
 } else {\n
     throw new Error(\"Cannot write a value to a ko.computed unless you specify a 'write' option. If you wish to read the current value, don't pass any parameters.\");\n
 }\n
 return this; // Permits chained assignments\n        } else {\n
 // Reading the value\n
 if (!_hasBeenEvaluated)\n
     evaluateImmediate();\n
 ko.dependencyDetection.registerDependency(dependentObservable);\n
 return _latestValue;\n        }\n    }

For the value of the Label property of the SortField.

The fix is to call the Label function as this.FieldDef().Label() inside SortField's Label.

Compile Postgres in Windows with GSSAPI

2014-01-06T15:21:00.002-05:00

Configuration Phase

Install the 64 bit versions of OpenSSL and MIT Kerberos
Get the professional version of Visual Studio 2010 or 2012.
Open the 64Bit of the Visual Studio Command Prompt (not the 32 bit version)
cd to src\tools\msvc in the folder that contains Postgres’ source code
Change the config_default.pl:

add these lines:

includes => 'C:\\Program Files\\MIT\\Kerberos\\include:C:\\OpenSSL-Win64\\include',
libraries => 'C:\\Program Files\\MIT\\Kerberos\\lib\\amd64:C:\\OpenSSL-Win64\\lib',

after

“iconv => undef,”

change the krb5 and ssl lines to:

krb5 => 'C:\\Program Files (x86)\\MIT\\Kerberos', # --with-krb5=
openssl => 'C:\\OpenSSL-Win64', # --with-ssl=

Build Phase

in the vs command prompt, type Build
When done, if no errors, then type Install C:\PathWhereYouWanToInstallPostgres
Go to the folder where OpenSSL is installed
Search in the folder and subfolders for libeay32 and ssleay.dll and copy those files
Go to the folder where it Postgres was installed (C:\PathWhereYouWanToInstallPostgres)
Go to the BIN folder and paste libeay32 and ssleay.dll
Start the regular command prompt in that folder and call pg_ctl -D PATH_TO_YOUR_DATA_FOLDER

Please refer to the Postgresql documentation on how to configure GSSAPI in an installed Postgresql service here.

How to use Windows SSO with OpenXava

2013-11-19T18:33:00.001-05:00

One of the nice things about the .NET web environment is the dead easy way to implement Single Sign On in your web apps through Active Directory authentication. In the Java world there are multiple alternatives to use Windows’ Single Sign On with Java based web apps. One of those alternatives is Waffle. Waffle allows your Java web app to authenticate against Active Directory groups (and users). The only caveat is that your web server needs to be running in Windows, which kind of makes sense.

In this article, you will learn the steps required to have your OpenXava web application use Waffle to authenticate your Windows users.

The first step is to download Waffle from their site and then copy the JAR files outlined in https://github.com/dblock/waffle/blob/master/Docs/tomcat/TomcatSingleSignOnValve.md to the OpenXava’s tomcat server.

In your OpenXava project, create servlets.xml in the Web-inf, containing the following:

<security-role>
<role-name>YOURDOMAIN\YourADGroup</role-name>
</security-role>

<security-constraint>
<web-resource-collection>
<web-resource-name>
Demo Application
</web-resource-name>
<url-pattern>/*</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>YOURDOMAIN\YourADGroup</role-name>
</auth-constraint>
</security-constraint>

Add a new file called filters.xml to Web-inf:

<filter>
<filter-name>SecurityFilter</filter-name>
<filter-class>waffle.servlet.NegotiateSecurityFilter</filter-class>
<init-param>
<param-name>allowGuestLogin</param-name>
<param-value>false</param-value>
</init-param>
<init-param>
<param-name>waffle.servlet.spi.NegotiateSecurityFilterProvider/protocols</param-name>
<param-value>
Negotiate
NTLM
</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>SecurityFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

After creating the filters.xml file, create the a context.xml file in the META-INF folder (not Web-inf):

<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Valve className="waffle.apache.NegotiateAuthenticator" />
<Realm className="waffle.apache.WindowsRealm" />
</Context>

That’s it! that will limit access to members of YOURDOMAIN\YourADGroup and the users that visit the site will have their Windows credential pass-thorugh their browser. Please consult OpenXava and Waffle on how to get the current username in order to handle custom logic based on the current user’s Active Directory groups.

Data Science for Business by Foster Provost & Tom Fawcett O’Reilly Media

2013-11-13T20:30:00.000-05:00

Data Science for Business is a book that makes a phenomenal job teaching the fundamental concepts of Data Science (a.k.a. Data Analysis and Data Mining). Foster Provost and Tom Fawcett explain in plain English, clear examples and beginner-level math the processes surrounding Data Science and the basics of its algorithms.

The authors go over the various steps of the CRISP method using situations found in the real world such as Customer Churn and Online Advertising. The most common data analysis models are reviewed and explained in detail such as Clustering, Decision Trees and Support Vector Machines. Extensive explanation is given to the difference between supervised and unsupervised methods. Even if you use software tools that create those models, this book will help you understand how to use/test them correctly and how to avoid over-fitting.

Multiple examples are given in each chapter and most of the math is visually aided with graphs. The authors explain step by step any equation presented in the book. A notable example is how the authors show how the different parts of the Bayes’ Rule equation come together in chapter 9. There are also special Math-intensive sections that business managers might skip, but software developers and future data scientist need to examine closely.

I would recommend this book to any DBA or Developer looking for an useful introduction to Data Science. For a practical application of the concepts in the book, I recommend Data Analysis Using SQL and Excel by Gordon Linoff after reading Data Science for Business. As a SQL Server DBA, I will apply the concepts I learned with the book to SQL Server Analysis Services.

How to make MS SQL integrated security work in Spoon

2013-11-02T10:17:00.001-04:00

How to make Micorsoft SQL Server's integrated security (SSPI) work in Spoon:

Download the MS SQL JDBC sql drivers
Copy enu\auth\x64\sqljdbc_auth.dll to {spoon installtion folder}\libswt\win64
Copy enu\auth\x64\sqljdbc_auth.dll to {spoon installtion folder}\libswt\win32
Copy C:\sqljdbc_4.0\enu\sqljdbc4.jar to {spoon installtion folder}\libext\JDBC
Open Spoon
When creating the data source, make sure to check "Use integrated authentication"

Please note that you are copying the 64 bit version of sqljdbc_auth.dll to both \libswt\win64 and \libswt\win32 if you have a 64 bit processor.

Alert if file missing using Powershell

2013-07-02T21:26:00.001-04:00

The following Powershell script can be used to send an email alert when a file is missing from a folder or it is the same file from a previous check:

$path_mask = "yourfile_*.txt"

$previous_file_store = "lastfileread.txt"

$script_name = "File Check"

###### Functions ##########

Function EMailLog($subject, $message)

{

$emailTo = "juanito@yourserver.com"

$emailFrom = "alert@yourserver.com"

$smtpserver="smtp.yourserver.com"

$smtp=new-object Net.Mail.SmtpClient($smtpServer)

$smtp.Send($emailFrom, $emailTo, $subject, $message)

}

Try

{

#get files that match the mask

$curr_file = dir $path_mask | select name

if ($curr_file.count -gt 0)

{

#file found

#check if the file is different from the previous file read

$previous_file = Get-Content $previous_file_store

$curr_file_name = $curr_file.Item(0).Name

if ($previous_file.trim() -eq $curr_file_name.trim())

{

$msg = "Found same file as previous check: $previous_file and $curr_file_name"

$msg

EMailLog "$script_name Error" $msg

}

else

{

#different file, record its name for comparing in the next run

$curr_file.Item(0).Name | out-file -filepath $previous_file_store

}

else

{

$msg = "$path_mask Not found"

$msg

EMailLog "$script_name Error" $msg

}

Catch [system.exception]

{

"Caught a system exception "

$error

$error.Exception | EMailLog "$script_name System error" $_

}

Thoughts on how to prevent Abortions

2013-04-06T15:38:00.000-04:00

I would like to first set the scope of this article; this is about abortions in cases where the couple had consensual sex and there is no life-or-death health risk to the pregnant mother. In regards to cases of rape or life-or-death risks to the mother, I think it is best for a panel of women (both pro-choice and pro-life) to discuss it. The panel should include people who have gone through those situations and who have taken either direction. No man (pro-choice or pro-life) should be included in that panel and meetings. I also want to clarify before we being that I am pro life in that I believe life begins as soon as the baby starts forming in the womb.

Having set the scope of this article, it is important to address the fact that it is very important to point out that when a baby is conceived, the couple is pregnant not just the mother. The responsibility falls to both the man and the woman. Paying child support is not even near the share of responsibility that the father should take on. It is sad that the justice system makes it seem that by just paying child support, the father is off the hook. The father should be forced by the justice system to have more responsibly such as baby sitting, spending sleepless nights, taking the child places and cleaning diapers. My suggestion is that if the father fails to comply with any of those, he should pay a fine and also be sterilize. This might sound like the government intruding on areas it should not, but I prefer that than having women abort their children because they will not have the support they need. By sterilizing the bad fathers, we as society can ensure that they won't be doing that to another woman.

Sterilizing procedures might sound a little bit extremist, but it can help curve spending on social welfare. I do believe in having some sort of safety net for poor children and poor elderly, however we as society need to be smart about it. Allowing people to have a large amount of children and then ask for welfare assistance sounds really dumb. People might disagree on how many children is too much for families assisted by the government. I suggest that two is enough. If a family has two or more children and they need welfare assistance the government should sterilize the couple to prevent them from having more kids. This will free up more welfare resources for other children, like those with severe health challenges who might otherwise be aborted by couples who believe that abortion is a way to avoid the cost and time associated with taking care of a child challenged with severe health problems.

This bring another excuse people often use to justify abortion. Some people believe is OK to abort a child if the child is going to have severe health problems during his/her life. We don't often see (or even allow) parents to go their child dying of cancer and killing him/her to "spare" him/her from the pain. Many people who are born and live through sever health challenges do not commit suicide, which could mean that even if life is very tough there is either light at the end of the tunnel or there are some aspects in life that are worth living for. The same could be said about the "undesired" children, children who grew in families were they were not loved like they should. Many of them have had a terrible childhood life; but many of them are able to break away and find happiness even if it is at latter point in time or a reason to live for.

If couples are not willing to take care of their children if they are born with severe health problems, then I suggest they don't try to have babies on their own at all. There will always be risks with new babies and many parents are willing to receive their new babies as they are. If some couples are not willing to take that responsibility, then I suggest they sterile themselves to "spare" themselves from that risk. I then suggest they find an orphan toddler who looks healthy and adopt the child. I am not being sarcastic, I think this is a better solution than having the couple abort a child because the child happened to have some health problems.

Unplanned pregnancies can be avoided by abstinence or by using any kind of contraceptive. I truly believe abstinence works 100% of the time, but for people who do not believe in abstinence, contraceptives like condoms should be a no-brainier. There is no excuse for not using one. Thanks to movies, TV shows, schools, peers and the Internet, pretty much everybody knows what they are and how to use them. This responsibility should fall 100% on the man; no condom, no sex. If you are stupid enough to decide not to use them, please do society a favor and have a vasectomy (yes I am suggest this a lot, but it is very effective). Human kind does not need your stupidity genes in our Gene Pool, we have plenty enough.

If a couple do not have money to buy contraceptives, then they have bigger problems than trying to get contraceptives. Instead of spending time having sex, they probably should be spending all their spare time on finding ways of earning more money; like a second job or inventing something everybody needs. Sexual relationships are not unnecessary to human survival like food or medicines, so I do not support the government subsidizing contraceptives. We might as well subsidize video game consoles and music downloads. However, if it really comes down to either using tax money for contraceptives or aborting children, then please take my tax money and spare the babies.

The final excuse that I want to address is probably the most prominent. Women's right to choose what they want for their bodies. I do believe on individuals on having the right to do whatever they want o their bodies. If they want a tattoo or chop the ears, let them do it. Because of biological reasons, women are the ones who host the development of new humans beings in their bodies. Yes, nobody asked them, but that is just reality. So even though women do have the right to do anything they want to their body (arms, ears, liver, lungs, etc), the new human baby developing in their womb is off-limits because anything damaging the baby inside is violating the baby's right to his/her body.

This brings another popular excuse: the baby is not really a person and is just a fetus and equivalent to a liver or to a kidney and therefore has no rights. The baby inside feels pain, moves, has a brain, has a palpitating heart, digests food and breaths. To me, that pretty much shows he is a human child. I really don't understand when people give this excuse since most of that same people would rightly fight tooth-and-nail to protect a child that is outside the womb. One of the excuses is that the baby inside the womb is completely dependent on his/her mother and therefore cannot be classified as a person. This argument is moot if you consider that newborn babies are also 100% dependent on another human being for protection and for food and they are classified as persons. I honestly do not see the difference between a newborn outside the womb and a child developing inside his/her mother. They are both humans and they both depend on their mothers to survive. They should have the same rights, including the most important one: the right to life.

How to easily import data from SQL Server into PostgreSQL

2013-04-01T21:47:00.002-04:00

I was looking for an ETL software similar to SSIS that would allow me to easily move data from SQL Server to PostgreSQL. I tried using SSIS, but I was never able to make BIDS generated the schema of the destination table automatically like it would do with a SQL server destination table.

I downloaded Pentaho’s Spoon data integrator and it surpised how easy it was to do it there. The designer is somewhat similar to BIDS. You drop your input tables (from SQL Server) and then you drop your destination tables (PostgreSQL tables).

The good thing is that you do not need to create the tables in PostgreSQL beforehand. You just need to create the destination database. When you open the properties of the Table Output, you can enter the name of the new table and then do the following to create the table based on the data input:

Click “Database Fields”

Click “SQL”

Click “Execute”

That’s it. Your table will be created in PostgreSQL using the correct data types.

Pentaho’s Spoon data integrator also have other nice features, such as Json input/output, Google Analytics input, Hadoop and SalesForce connectors, and more. The software is free and can be found at http://www.pentaho.com/explore/pentaho-data-integration