If you need to use C# in Excel, you're doing it wrong. There's very little programming that you should be doing with VBA, too.
Most of your programming should be DAX plus a bit of worksheet functions. Some SQL to filter your data before loading into Power Pivot.
SQL yes, of course. You run SQL queries to load data into Power Pivot through a native SQL Server driver or native drivers for your DB or worst case ODBC.
Then you do all the BI analytics in DAX and show results in pivot tables. DAX is a very fast, concise and very, very powerful language for analytics. This is the whole purpose of OLAP.
aeorgnoieang|8 years ago
More generally, you can do all of this same 'stuff' in any language. This seems like a possibly better way to do the same things tho.
fomoz|8 years ago
Most of your programming should be DAX plus a bit of worksheet functions. Some SQL to filter your data before loading into Power Pivot.
SQL yes, of course. You run SQL queries to load data into Power Pivot through a native SQL Server driver or native drivers for your DB or worst case ODBC.
Then you do all the BI analytics in DAX and show results in pivot tables. DAX is a very fast, concise and very, very powerful language for analytics. This is the whole purpose of OLAP.
Check out this video when you have time :)
https://www.youtube.com/watch?v=0WwFJ0Zg3d8