This folder contains one item called “item.data”. We will find a folder called “xl” which contains a sub-folder called “model”. This allows us to browse the Excel file structure: #Ssas tabular model zip#doc x, …) are basically ZIP files, we can inspect our original Excel workbook by simply rename it to “.zip”. Knowing that all new office formats ending with “x” (.xls x. Well, the origin of the file has to be our Excel workbook that we imported. So far so good – but where does this file come from? SSAS actually restores a backup from a “Model.abf” backup file which is located in our project directory that we just created: The most important event for us is “Command End” with the EventSubclass “9 – restore”: At this point you will notice a lot of events happening on our SSAS Tabular server. Then create a new Tabular project in Visual Studio based on an existing Power Pivot workbook. To do this start SQL Server Profiler and connect to your tabular instance. And in this blog post I will show how this can be done!īut before taking a closer look into how this can be accomplished, lets first see how the import from Power Pivot to SSAS Tabular works. sales people to take it with them on their every day work. #Ssas tabular model Offline#Several use-cases come into my mind but I am sure that the most important one is to making data available offline for e.g. This all works just fine but there may also be scenarios where you need to do it the other way round – converting a Tabular model to Power Pivot. Even further, this process is also a major part of Microsoft’s strategy to cover Personal BI, Team BI and Corporate BI within one technology being xVelocity. Microsoft even created an wizard (or actually a separate Visual Studio project) that supports you doing this. There’s more to this systems’s performance problems than just this, NUMA for a start, but thought I’d throw this out there in case anyone else is blindly following the performance tuning white paper without doing your own experimentation.Įach environment, data set and server spec is different, so if you need to eek out the last ounce of performance, run your own tests on the SSAS settings and see for yourself.It is a very common scenario to create a SSAS Tabular Model out of an Power Pivot Model contained in an Excel workbook. Note that the server’s ProcessingTimeboxSecPerMRow setting has been set to 0 to allow for maximum compression. This setting creates 80 segments, 2.5 times the number of cores available, but achieved the best performance. As well as this, processing time was reduced, as was compression. It clearly shows that in this environment, reducing the DefaultSegmentRowSize property down to 2m improved the query performance (on a cold cache) from 42s down to 27s – 36% improvement. So I ran some timing tests on the distinct count measure, and the results are quite interesting. But the extra compression with 16m segment size may be of benefit. So, with 32 cores to play with, we should be looking at the default segment size (8m) or maybe reduce it to 4m to get 40 segments. It also indicates that a higher segment size may increase compression, and consequently query performance.Ĭalculating the number of segments for our data set, gives us the following options: P88 of the Performance Tuning of Tabular Models white paper discusses the DefaultSegmentRowCount, explaining that it defaults to 8m, and that there should be a correlation between the number of cores and the number of segments. 80m distinct CustomerKey values in primary factĪ simple distinct count in DAX of the CustomerKey, with no filtering, is taking 42 seconds on a cold cache.7.6Gb SSAS tabular cube, running on a 2 x CPU 32 core (Xeon E5-2650 2Ghz, 2 x NUMA nodes) server with 144Gb RAM.I’m currently investigating a poorly performing Tabular model, and came across some interesting test results which seem to contradict the advice in Microsoft’s Performance Tuning of Tabular Models white paper.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |