EXCEL Data Model 和DAX

最近自学到了一些Excel新技能：之前用excel，做个财务报表、画个图，最多也就弄个pivot table。感觉也就如此了。想要高端一点的，肯定是要转化成CSV，然后Import进SQL，做成table了，好像才能有一些复杂的数据操作。而学到了Data Model和DAX之后，这个概念颠覆了。先做一下简单的对比，感受一下SQL和EXCEL的异同之处。

数据操作对比：SQL vs EXCELSQLEXCELSELECT statementVLOOKUP functionTableData ModelDerived table or ViewsCalculated field and columnsForeign KeyRelationsor RELATED functionAggregate functions (Sum, Min/MAX)Calculated measuresVariableExplicit measuresDate ImportLoad to Data modelStrong data types Strong Data typesFiltering (Between, In)FilteringSubqueriesSUMX orALL, CALCULATED functionsGrouping sets and Pivoting DataPIVOT/ UNPIVOT chart or table

基本上，EXCEL已经具备了programability。尤其是在处理Data model的时候，有“Action Steps”可以用M language把行动记录下来。产生的这样一个script就具备了实现自动化操作的条件。相比SQL，其最大的优势是visualization。图表功能（记得要用Slicer和Hierarchy）已经完全符合高端商务人士做个Keynote的需求。相比之下，SQL在做动态图方面还不够强大，可能要用到Power BI来配合。尤其喜欢EXCEL里一些对Business Analysis非常有用的和calendar有关的一些functions，比如自动生成calendar table，比如以下着三个做财务报表的利器.

• SAMEPERIODLASTYEAR:
  https://support.office.com/en-gb/article/SAMEPERIODLASTYEAR-Function-DAX-b8f7f423-22f5-470f-abd3-b76a1250bcc1.
• PREVIOUSQUARTER:
  https://support.office.com/en-US/article/PREVIOUSQUARTER-Function-DAX-D6DD1BA0-0541-4C03-B928-F6884078E736.
• TOTALYTD:
  https://support.office.com/en-US/article/TOTALYTD-Function-DAX-E2E45CB6-C882-4F84-A8B5-F3FFAAE27320

要说缺点：在较大数据量（几万+）的时候，EXCEL有点力不从心。不小心就会崩溃。而且OLAP/CUBE的功能还是不够好。处理大数据还是用SQL比较好。而且，据我所知，.NET的entity framework支持SQL的connection，还不支持EXCEL的connection。