MS SSAS and SSIS connectivity for netstandard (Adomd, Amo etc. netstandard libraries )

[voltcode]

Current state of .NET libraries does not allow to communicate with MS SQL Server Analysis Services - ADOMD libraries (both AdomdClient and Adomd) are not available as netstandard libraries:

• https://www.nuget.org/packages/Microsoft.AnalysisServices.retail.amd64/
• https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/

Such libraries would allow to build new generation of services that can freely access and manage datasets contained in OLAP cubes and Tabular models, from both Windows and Linux.

For some applications that provide reporting capabilities on data collected in SSAS cubes and models, lack of netstandard Adomd libraries is the main blocker to migration onto .NET Core.

Developers have to choose between migrating to NET Core and ditching SQL Server Analysis Services, or staying on NET FX but being able to use SQL Server.

Perhaps, now that System.Data.OleDb has been ported, it can be a good time to implement those libraries.

Please consider providing a netstandard version, instead of Core-only, so that existing NET Framework apps can switch to new libraries first without being fully retargeted.

Since SSIS is another important component of SQL Server, please consider providing netstandard libraries for SSIS connectivity as well, acc. to MS docs these are:

• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.Smo (seems like this one is on nuget, but not sure if it supports netstandard)

[voltcode]

as per https://github.com/dotnet/corefx/issues/23542#issuecomment-491025958, giving a shout to @divega :)

[AfsanehR-zz]

@danmosemsft I am not particular sure if this is SqlClient area. @saurabh500 Could you please take a look if this is related to System.Data.Odbc or System.Data.OleDb?

[saurabh500]

This has nothing to do with .Net core. I think these are libraries shipped with Sql server family of products. I don't think those product teams are looking at issues here.

[danmoseley]

@saurabh500 maybe you could help direct to the right place to give feedback on those products?

[divega]

@danmosemsft I think for feature requests that like this are about technologies that are SQL Server or PowerBI related, but not SqlClient related we can use area-System.Data in the future.

Also, the actionable item I see here is collecting as much background as possible from customers, pass the customer feedback to the SQL Server and PowerBI teams and help establish a priority. If there is someway to convey that it is about components external to CoreFx (e.g. move the to some other repo or add a label), let me know.

[divega]

@voltcode and anyone else interested in this functionality: it can help a lot if you can provide concrete information about specific applications that you plan to build or port if this functionality becomes available on .NET Core.

In the meantime, I have moved the issue to the backlog for future consideration.

[voltcode]

@divega we have plenty of such cases, both in our own environments and in environments of our clients.

1) We maintain many .NET business line applications for our clients, which sometimes need to pull specific calculations or raw data from a SSAS data source (either OLAP or Tabular models). For example - on an employee HR dashboard, show his stats like whether his sales objectives were met, etc. . The applications do not have access to raw transactional data to perform such calculations and their shouldn't - they should be using the master data that is the OLAP cube to pull this information.
These applications are written either as an SPA or in MVC, and they use either built-in controls or 3rd party controls like pivot tables or charts that have already been migrated to .NET Standard or Core. Several of them are desktop applications written in WinForms, but given the recent announcements, this also is no longer an obstacle to migrate to .NET Core. The obstacle for migration is now the ADOMD* libraries.

2) Some of the aforementioned applications, need to alter or create SSAS and Tabular models. For example - a column is added to the underlying transactional data model and it needs to be exposed in the OLAP or Tabular model. We have automated the process, but it requires the Adomd library to do so. Another common task is permission management - create or alter a role for existing model, or to access SSIS and show the status of recent jobs to our user.We are talking about around 20 applications in 50 different environments (multiple companies), and around 5000 direct app users in those companies, at least 15000 users affected indirectly (they consume the outputs of those applications).

3) Another special case for us is that we work on a product, which we want to scale faster and quicker. Some of our customers and potential customers demand that we remove the requirement for Microsoft Windows as the base operating system, mainly because they operate Docker Swarm or Kubernetes in private or public clouds and would like to receive appropriate Linux image from us. Those customers do not accept Windows images, for various reason (worse support, rougher edge cases, need for extra competencies in their Ops team, licensing, etc.). In order to port our product in full, we would need to be able to use all SSAS (Adomd*) and SSIS client libraries (both data access and management client libs) from Linux and this is not possible at the moment.

These are just examples from our daily work, we have cases now where we deliberately not consider SSAS or SSIS in the architecture of the target new app and replace it with something else, because the current state of data connectivity in .NET Core with regard to SSAS and SSIS is an unknown.

[voltcode]

@divega would it be possible for you guys to say if we can expect this in netcore3-ish timeframe or not at all? Or maybe - what's the current best way to communicate with SQL team directly to ask about SSAS client libraries and .NET core?

[divega]

@voltcode this issue is assigned to the Future milestone. This means it isn't planned for any particular release and certainly it won't happen in the 3.0 wave.

Thank you for the detailed description of what you would like to see. I will identify the right people to talk about this feedback.

[divega]

BTW @voltcode feel free to send me email (alias is the same) if you would like to provide contact information for the team to contact you directly.

[killergege]

In my company we're also waiting for a .net core compatible version of the Adomd client, and this is the only blocking point for migration.

Our product is an API that provides aggregated financial information from a SSAS Cube (tabular model) to external applications (mostly widgets). So we mostly use the client to execute dynamically built DAX queries (mostly summarizecolumns with filters to select a few attributes & kpis).

Out of despair, we tried the "unofficial" Adomd client. It kinda works, but we quickly found a lot of bugs and the creator doesn't seem to maintain it, so... not a good solution :)

The reason to move to .net core is the company policy to unify the infrastructure for all APIs by moving to docker/kubernetes, and running standard (non-windows) images.

[voltcode]

@divega I tried contacting you via e-mail, but it was rejected. I'd be grateful if you could shed some light on whether the Analysis Services team has committed to a timeline regarding netstandard/net core version of their client libraries.

[voltcode]

@danmosemsft I noticed that @divega is no longer in Microsoft. Would you mind pointing me to another person that I could talk to regarding this issue?

[danmoseley]

@voltcode you're right. @David-Engel @roji can you please help route this request?

@voltcode the SqlClient team has created a fork named Microsoft.SqlClient, so they can ship more often than we do. You could ask in that repo, where they will certainly see it. : https://github.com/dotnet/SqlClient/issues

[roji]

Issue opened on SqlClient repo: https://github.com/dotnet/SqlClient/issues/387.

@David-Engel @cheenamalhotra any idea who the owners of this are?

[cheenamalhotra]

@roji

It's out of scope for dotnet/SqlClient repository and our driver team so I'll close the issue opened at our end. I'll let David respond regarding area owners.

[David-Engel]

@roji @danmosemsft I have no idea. But there is a relevant issue on the SQL Server feedback site which people can vote for. From comments, it also looks like they are planning a preview in Q1: https://feedback.azure.com/forums/908035-sql-server/suggestions/35508349-adomd-core

[roji]

@David-Engel @cheenamalhotra thanks! @danmosemsft I think we can close this as external.

[danmoseley]

Ok, thanks. Glad there seems to be a solution coming.

[voltcode]

@danmosemsft the linked issue has been quiet with official information. Could you please pull some internal strings and obtain a more official response from the responsible team? Or maybe get me in touch with them directly?

[illegalnumbers]

I'm also interested if there has been any movement on this.

[danmoseley]

@illegalnumbers @voltcode the place to watch is https://feedback.azure.com/forums/908035-sql-server/suggestions/35508349-adomd-core -- I see they posted an update there. Please do keep your feedback directed there (of course you're welcome to continue to post here, but this repo doesn't include that code)

cc @JeremyLikness