When building a tabular model people face problems wherefore already a solution exist. Marco Russo and his colleagues created a lot of solutions in tabular and Power Pivot and transformed those into patterns (a general reusable solution to a commonly occurring problem) so they can be easily reused in other projects where the tabular model or Power Pivot is used. These patterns are collected and put on a special website: http://www.daxpatterns.com/. A couple of examples he showcased were "Cumulative Balance (without using snapshots)", "Custom calendar", and "Actual Vs. Budgets".
PDW (Parallel Data Warehouse)
- The Data Warehouse runs on the appliance.
- The performance is great (you should experience it to believe).
- The appliance has been made High Available (they just bought two appliances and put them on different locations).
- Analysis Services works smoothly with SQL Server 2012 PDW and the combination with a Multidimensional cube with ROLAP is perfect as the query response from PDW is superfast.
- When a lot of ad hoc queries are executed, an appliance is really helpful. When queries become used more widely throughout the company it's recommended to build those in a cube or tabular model.
Building a tabular model is easy but when it comes to performance there are a couple of things you have to take into account.
- Reduce the number of distinct values for columns (less memory needed, increases scan speeds).
- You can check which part of the tabular model needs attention by using the Excel workbook that is provided on this website: http://www.powerpivotblog.nl/what-is-using-all-that-memory-on-my-analysis-server-instance/
- How do you determine the hardware you need? The best road to follow is to first develop your model and filling it incrementally (increments of 10%) with real production data. By doing it this way you can create a good prediction of the memory usage. But the problem is that it normally takes a while to order hardware, so it’s not always handy to wait till a tabular model is developed and filled.
- When choosing hardware you need to take into account the following:
- CPU (the fastest you can buy, largest L2 cache, lowest number of cores (less than 8)
- Memory (the fastest you can buy)
- Disk / other hardware (don’t waste your money here)
It seems that the latest Xeon processor of iNTEL has a large L2 cache wich is shared by all cores. You can switch-off cores so more cache is available for the other cores.