Languages, similar to DAX? Any Python based library?

[vyacheslavg]

Hello colleagues,

Could you please advise if it's possible to replicate some of DAX functionality in some Python libraries?

For example, most (all?) M functions might be done in Python (+Pandas, Numpy, other modules).

But DAX is so different, that I don't know any general programming language which can replicate it.

SQL is not very good for data analysis (strictly IMHO), your mileage may vary.

Qlik script is fine, but its usage is limited to Qlik products.

I could not find anything similar to DAX in Python or R ecosystem.

As I understand a landscape of very fast analytics (Memsql, Mapd, ...) they use column stores for history data (approach, identical to Power BI and Qlik). These RDBMS use SQL for analytics workloads, without inventing their own Data Analysis Language, like Microsoft or Qlik. Is this the only approach or is there open-source data analytics language somewhere out there?

TLDR: are there any analogues of DAX or Qlik script for analytical workloads for column stored data? Preferably something Python based.

[Greg_Deckler]

Not familiar enough with Python and R to know. MDX, but that's not Python or R.

I guess the question is why exactly? You can use Python and R from within Power BI so you can have access to M, DAX, R and Python all in one platform.

There's a reason why different languages exist. You can do whatever you want in a general purpose language like C#, etc. but when it comes to list processing, Lisp sure is nice. Then again, you don't want to use Ruby if you are trying to interface with control systems, C is probably a better choice. There's a wide world of programming languages outside of the Python bubble.

[vyacheslavg]

Thanks.

My question is not limited to Python - even if I have a Python preference, because Python is great in my opinion -  I'm not limited to it.

Saying that Python is "integrated" in Power BI is a big overstretch. Python is a very small (optional) part of ETL in Power BI, and thus its role is very limited. You can't use Python to have access to DAX or even perform any action on columnar data model in general -  at least I've not found any information on how to do that. Also the Python "integration" with M is very slow. That's why using of ligthing fast memory and SSD Python technologies, such as Parquet or Arrow is useless withinPower BI ETL process.

Question - why - is a very good question . I'm looking for other smart approaches to deal with data analytics, preferably open-source and linked with general purpose languages.

As I wrote, Python+Pandas+Numpy is very similar to M, functionally wise. I can't think of any realistic ETL task I can't perform with it. 

Why other columnar databases RDBMS do not create their "own" data analysis languages and use SQL, which is far better for operational databases, not for analytical ones? It is nice, that such specialized analytical languages, such as DAX or Qlik script exist, but maybe there are alternatives?

[Greg_Deckler]

Actually, you can use Python to access the data just like DAX. Use the Py visualization that is built right into Desktop. In VISUALIZATIONS area it is the yellow "Py" icon. Right next to the "R" icon. 

I'm still not getting it. So, Python should have it's own version of SQL that does the same things SQL does versus just using SQL? I'm confused.