4 ms·
I'm not clear on why you are referring to Access as an alternative to a "SQL based application". Access's SQL dialect is extremely annoying sometimes, but usin
by perl4ever 7y ago
I'm not clear on why you are referring to Access as an alternative to a "SQL based application".
Access's SQL dialect is extremely annoying sometimes, but using SQL is a very significant (if not the most) reason for utilizing it rather than Excel.
I went around and around trying to find a way to query Excel "tables" with SQL or something similar, but eventually gave up. There is something called DAX, but it seems different just to be different, and nothing is orthogonal and logical - particularly external data is not on an equal footing with tables within your spreadsheet.
- SNACKeR99 7y agoIf you are on Windows there is QueryStorm, and it works amazingly well IMO.
- LegitShady 7y agoI use DAX in Power BI which I believe shares a common data model with excel. Dax is powerful but sometimes frustrating. When you need bidirectional relationships you end up making custom measures to keep the whole model performant. Depending on the scale of the problem being looked at it becomes a mess. But Power BI is the access of dashboard world...
- perl4ever 7y agoBased on my limited experience, Power Pivot has a maddening lack of smooth integration with Excel. Power BI is a separate install, I believe, so not currently an option. I'm stuck with Office 2013 for the time being and can't upgrade or install anything. If I could use anything, I'd probably prefer to go back to SSRS. Maybe Informatica, or OBIEE or something. I'm biased by spending so many years writing SQL, but I found it extremely cumbersome to do basic things in DAX even after I figured out how, and haven't seen any obvious advantages. But I do know for a fact that software newer than 2013 adds some crucial features that would make it better.
- LegitShady 7y agoYa it's tough if you're stuck on 2013. Sometimes even random commands in excel are great and you find out they work at home but not at the version at work, or require office 365 (?!). Access? I recommend watching some youtube power bi videos for dax tricks. Lots of dax stuff on youtube. Or radacad.
- kthejoker2 7y agoDAX and SQL are qualitatively different languages. They serve different use cases entirely. DAX is designed solely for analytical queries using dynamic context (so you don't have to code for every user selected filter or relationship dependency in your model.) Its meant to be written once and then used interactively by your users. It has tons of helper functions like time intelligence, iterators and generators .. SQL is obviously much broader in scope and functionality, but to match the level of built in context driven dynamism of DAX would require a frankly unmaintainable mess. So yeah if you just need an answer to a single specific analytic query, just write a SQL query. But if you need to create a measure to show weighted average pricing for any user selected combination of equities, time periods, markets, vendors, and 500 other dimensional attributes - and then create 5 more measures on top of that to show year to date, year over year, performance vs. overall market, simulated demand at a 5% decrease in price, and price excluding large institutional purchases - DAX is your friend.
- infinite8s 7y agoI wonder if this is where Looker succeeded with their modeling language LookML.
- akho 7y agoDAX is unrelated. There’s M in PowerQuery. It is also not SQL, but I do believe that it’s better for the task.
- phonon 7y agohttps://sheetjs.com/sexql/index.html https://sheetjs.com/sexql/index.html