Yaniv Mor

BI Course coming up next month…

2011-02-18T08:20:00Z

I’m excited to deliver, together with the Madeira team, an introductory course on Business Intelligence next month. In this course we will learn:

The basics of managing a BI project
Dimensional modelling theory and concepts
Design and implementation guidelines, using the Microsoft BI product stack, including:
- The Relational database setup
- The ETL process, using SSIS
- Analysis Services and the OLAP model
- Reporting Services

This is an introductory course, best suited to DBAs who want to explore the BI niche, Project Managers who wish to understand the challenges of running a BI project in the organisation, and for BI developers at a beginners’ level.

Details and registration forms are available here: http://www.madeira.co.il/introduction-to-microsoft-bi/

Looking forward to seeing you there!

Yaniv

Technorati Tags: Business Intelligence,Training,Madeira,Microsoft

More on the CALCULATE function…

2010-10-05T11:23:23Z

A bit more information regarding the new CALCULATE function in DAX. I’ve described what this function does in a previous post, and I want to expand on how to treat this function in a PowerPivot report. Consider the following report:

We have Order Quantity sales ([Order Quantity Total]) and two calculated measures, which we are going to discuss shortly. We have the promotions dimension in the Y axix and 2 slicers: Product Category Name and Calendar Year.

The DAX syntax for the calculated measures is as follows:

Order Quantity in 2003:

CALCULATE(SUM(FactInternetSales[OrderQuantity]), DimDate[CalendarYear]=2003)

Here we aggregate the [Order Quantity] column and apply a filter to look only at records from the calendar year 2003.

Order Quantity for Accessories product category:

CALCULATE(SUM(FactInternetSales[OrderQuantity]), DimProductCategory[EnglishProductCategoryName]="Accessories")

We again aggregate the [Order Quantity] column and apply a filter to look only at records that relate to a sale of a product that belongs to the “Accessories” product category group.

Note also that we currently have not applied any selections (filters) on our available slicers.

The question comes to mind: if we now apply a filter on the Calendar Year slicer and say, choose the year 2002. What will we be seeing in the [Order Quantity in 2003] column? This is what actually happens:

You will see that the calculated measure [Order Quantity in 2003] displays the same data it displayed, as if the 2002 calendar year filter hasn’t been applied. The rest of the columns display filtered data for the year 2002. What we need to understand is that the calculated measure is evaluated independently of the slicer, but just in case the dimension used for filtering is the same dimension used in the slicer. You’d see that the second calculated measure that filters data by product category is reflecting the calendar year 2002 filter and displays no data.

A similar test using the Product Category Name slicer will show the same results:

We now filter by product category Bikes, but the calculated measure that displays data filtered by the Accessories category displays the same data as if no slicer filter has been applied.

It is important to understand when these calculations are taking place, so your report will actually display the data you want it to display.

HTH,

-Yaniv

(This is post number 24 for week number 45 of my SQL blog challenge)

Technorati Tags: PowerPivot,Excel 2010,DAX

DAX functions – CALCULATE and CALCULATETABLE

2010-10-01T08:51:52Z

I’m getting more and more involved in designing PowerPivot models and want to share a bit of DAX tips in the coming posts. I’ve already touched on DAX in previous posts and in this post I’d like to discuss the new CALCULATE and CALCULATETABLE functions and their uses in a model. DAX is a powerful expression language and one of its main differences from the expression language we know in Excel is the fact that we can reference entire tables rather than cells in a spreadsheet.

When we need to look into a series of values in a PowerPivot table and display it as a column in another table we can use the array of functions that except tables as parameters. Such is the CALCULATETABLE function which excepts a table and a list of filters which can be used as inputs to other functions that will perform calculations against this set of records.

Say, for example that we want to display the total sales amount of a product in a certain year in a PowerPivot report that lists all products. Using the Adventure Works data model, we could add the following calculated column in our products table model:

=SUMX(CALCULATETABLE(FactInternetSales, DimDate[CalendarYear]=2003), FactInternetSales[OrderQuantity])

Note that the CALCULATETABLE function excepts a table as a first parameter and then a filter, using any of the columns related to this table. In this example I chose to filter by year (in a PowerPivot report you could actually make this filter parameterized and let the user choose how to filter the data), but you could also apply more filters to the same expression. Simply add it to the list of filters, separated by a comma.

We then use the SUMX function that receives the subset of the [FactInternetSales] table and applies a SUM function on the [OrderQuantity] column.

There is another DAX function, the RELATEDTABLE function which is a synonym of the CALCULATETABLE function, use can choose to use either of them, the behaviour is similar.

The difference between the CALCULATE function and the CALCULATETABLE function is that the first excepts and returns a single value, whereas the CALCULATETABLE function excepts and returns a table.

I can actually display the same data as showed in the previous example, using the CALCULATE function, like so:

=CALCULATE(SUM(FactInternetSales[OrderQuantity]), DimDate[CalendarYear]=2003)

Again, I’m filtering data in the [FactInternetSales] table by year, but this time, I’m first calculating the total sales of the products. The CALCULATE function here receives as an input a single value (total sales, linked by product), applies the date filter and returns a single value for each product.

The question comes to mind when would I use the CALCULATE function and when the CALCULATETABLE function? Firstly remember that they produce different result sets: one produces a single value and the second a table. So when you need to use functions that operate on tables rather than single valued expressions, you need to use the CALCULATETABLE function. And, why would you use this function instead of simply referring to the whole table? The CALCULATETABLE function can apply filters on the table data, so obviously this can be handy in your reports.

I’ll post more on practical uses of this function really soon…

-Yaniv

(This is post number 23 for week number 44 of my SQL blog challenge)

Technorati Tags: PowerPivot,Excel 2010,DAX

If you’re a BI professional - You probably should learn Sharepoint…

2010-09-27T05:56:35Z

One cannot ignore the focus Microsoft puts on the Sharepoint platform as of the past couple of years. The Sharepoint product is, in fact, the best server-side product seller for Microsoft ever. I did not fully realize the importance of this platform until recently, when I was attending the “Data Platform” conference several months ago and went to hear Mr. Donald Farmer. Mr. Farmer said that the new workforce, used to network and collaborate using Facebook and Twitter is expecting to find the same networks available at the workplace. Using email as the means for communication seems awkward and inefficient to them. The Sharepoint platform is the new workplace “Facebook”, where these new-age employees could post ideas, comment on ideas by others (when will Microsoft add the “Like” feature in Sharepoint?) and brainstorm on new initiatives.

With the advent of PowerPivot, which has gained massive popularity since its launch and its tight integration with Sharpeoint, I believe that every BI professional who is specializing in the Microsoft product stack should spend some time to get to learn Sharepoint as well. Analysis Services, in the R2 release is also getting closer and closer to this platform, and according to Amir Netz from Microsoft, the next release of SSAS is going to be tied even more with Sharepoint than it is now.

The traditional lack of direction by Microsoft, when it comes to OLAP viewers and reporting tools is probably over. PowerPivot, Sharepoint and Reporting Services 2008 R2 comprise an impressive set of tools to deliver BI in the organization. While there is still room for third-party tools to be integrated with the Microsoft products. organizations that do not wish to invest in these can probably manage without.

Happy Sharpeoint learning!

Yaniv

(This is post number 22 for week number 44 of my SQL blog challenge)

Technorati Tags: Sharepoint,PowerPivot

SSAS Performance tips – Lessons Learned – Part 2

2010-05-18T09:21:31Z

On a previous post I wrote about how the number of databases in an SSAS instance can make an impact on the overall performance of the instance. In this post I want to discuss another, more “explainable” property that you can set in order to improve cube processing performance.

Trying to optimize processing time of our databases I set up a performance monitor trace to identify if we are having any resource bottlenecks that we need to address. Looking at the “Physical Disk: Disk Writes Bytes/sec” counter I noticed that it recorded a fairly small numbers most of the times (between 1MB and 500KB of data written during a “dimension update” or partition processing” events). This is obviously way below the amount of data that can be written to disk (we use a very advance storage system that can cope with a much more intense disk write profile).

I also was looking at the SSAS counters “Threads:Processing pool busy threads”, “Threads: Processing pool idle threads”, “Threads:Processing pool job queue length” and “Threads:Processing pool job rate”. These counters show how the threads allocated by SSAS to perform the processing operations are behaving. I noticed that:

1) There was no job queue at all – which means all processing threads never waited – which is good.

2) The total number of threads (busy and idle) never exceeded 60.

In SSAS there is an (advanced) server property which tells the SSAS processing engine how many threads it can create for a processing operation, this is the Threadpool\Process\MaxThreads property. Its default value is 64 and that correlates with the number of threads I observed during processing. I increased this value to 128 and indeed the number of threads created during processing increased to approximately 120, but even better, I noticed that the disk write rate increased up to 15 MB/sec. There was still no job queue reading so I figured I have succeeded in this modification. Needless to say processing time decreased as well by 40%. I’ve increased this property to 256 but at this stage job queue was starting to increase from zero up to 15 and the number of threads created did not increase over 140 and I was not able to identify an increase in disk write rate as well. So, for me, 128 was the magic number for optimal processing performance.

If you are trying to optimize processing performance of your SSAS databases and are running your SSAS instance on a multiple CPU server, look to to the Threadpool\Process\MaxThreads property to see if this needs to be modified.

-Yaniv

(This is post number 21 for week number 25 of my SQL blog challenge)

Technorati Tags: SSAS 2005,SSAS 2008,Performance

SSAS Performance tips – Lessons learned – Part 1

2010-05-15T15:50:01Z

I’ve been researching a few performance issues lately on an Analysis Services 2005 instance with 20 (almost) identical databases on it, each weighing roughly 2.5GB and containing 6-7 cubes with approximately 400 partitions in each cube. As you can imagine, this makes up quite an impressive number of files stored on disk. SSAS stores everything on files, and unlike its relational relative (no pun intended), which will store hundreds of GB of data on a fairly small number of large files (I know this is an over simplification of how one should plan for data file allocation on SQL Server but its good enough and true enough in most cases for the sake of this post), SSAS stores a single database as described above in hundreds and hundreds of files. The number of files required is determined by the number of partitions, dimensions, cubes and aggregations and since we have lots and lots of those elements we will eventually have to deal with a heavily congested file system structure.

We have identified that a similar instance, containing only 10 databases performs better in both processing time and query response time. One thing I should note is that on the instance with the 20 databases, only 10 are in use, the rest were there purely due to historical reasons (we actually moved those 10 idle databases to a new instance – the instance that now performs much better).

What we first had to make sure is that both instances are operating under the same server properties, the same disk system and have available to them identical resources such as CPU and Memory. Indeed all of these parameters were similar.

For each SSAS database there is a metadata overhead, this means that when databases are initialized, metadata on the database is loaded into a non-shrinkable section of the SSAS memory. If you have many databases, initialization will take longer. If these databases are complex (i.e. contain many dimensions and partitions), initialization may become a more serious issue. However, we did not experience memory issues on the machine that ran the 20 databases instance. Still, we figured that the fact the disk was loaded with files (1000’s of them), it is best to drop all unnecessary databases and see what happens. Once 10 databases were dropped, performance on this instance became equivalent to performance on the first 10 database instance.

I do not have a clear—cut answer as to why this was the silver bullet we were looking for. As said, the 10 databases that we dropped were not in use in any way – not for querying and certainly not for processing. Still, dropping them and leaving only 10 “live” databases proved to be the trigger that we were looking for to improve performance on this instance.

So ,there you have it: make sure you do not have too many databases on a single instance, especially not “complex” databases that contain many dimensions, partitions and aggregations.

BTW – if any of the readers can shed some light into this – please do so, it will be much appreciated!

-Yaniv

(This is post number 20 for week number 24 of my SQL blog challenge)

Technorati Tags: SSAS 2005,Performance

[OT] – How I started working with SQL Server

2010-05-05T07:06:03Z

I was asking the readers to tell us how their SQL Server career started and I though I’d share my story with you as well: I have actually started working with databases when I was a student. I was working at Rambam Medical Center in Haifa in a part-time job as a developer, writing applications in Access and Delphi (?!) with an underlying Oracle database. When I completed my studies I got a job as a Project Manager at Panorama Software Systems. This company manages data warehouse projects and sells front-end business intelligence software based on the Microsoft platform (well, at least back then it was purely Microsoft oriented). So, on my first day at work I was given a desk, a chair and a desktop and I started learning SQL Server 7.0, from the ground up: database engine, analysis services, dts, the works! More than 10 years down the road – and I’m still working with the product, which have changed dramatically in this period as you may know.

Important note: I was given a clarification regarding the giveaway ticket for the BI seminar – The free ticket will grant the winner access to a single day of the seminar (and not the two days), so apologies for the confusion.

-Yaniv

(This is post number 19 for week number 23 of my SQL blog challenge)

Technorati Tags: SQL Server

SQL & BI Data Platform seminar – Your chance to win an entry ticket!

2010-04-29T13:33:22Z

Microsoft and John Bryce have organized a 2 days data seminar which focuses on SQL Server technologies. This can be an excellent chance for anyone who is involved in any way with SQL Server platform to attend a large number of technical sessions. Mr Donald Farmer, a Principal Program Manager for Microsoft SQL Server at Microsoft will also be attending and his session is not to be missed by any BI professional.

I was given an entry ticket to ~~both days of the seminar (these would cost you 1,800NIS if you were to purchase them)~~ a single day of this seminar (this would cost you 900NIS if you were to purchase it) to give away to a reader. So if anyone is interested, here’s how you can get your hands on the ticket: simply let me know (using the “comments”) how you first got into the SQL Server business to begin with. The guy with the most interesting story will be the happy winner…

Good luck!

-Yaniv

(This is post number 18 for week number 22 of my SQL blog challenge)

Technorati Tags: SQL Server 2008 R2,Business Intelligence

Database data file size limitation - SSMS

2010-04-26T11:37:09Z

I had to configure a data file in a very large database this one time and I used Management Studio for that. The file size I was aiming at was 3TB (a very large database indeed). I was quite surprised to see that SSMS has a limit to the size you can configure, which was 2,097,152 MB (i.e. roughly 2TB). I thought I was missing something here and quickly turned to SQL Server Maximum Capacity Specifications to see how big a data file can get, and to found out it is 16TB, not 2TB (the transaction log is restricted to 2TB, but I was trying to configure the data file). Modifying the data file using the ALTER DATABASE statement resulted in the expected behaviour and I was finally able to expand the file as I wished. However, the SSMS behaviour is unreasonable, and maybe it is because of the limitations once posed by the MBR partition tables which did not enable to you to configure drives larger than 2TB. However, this is something that can be resolved by using GPT instead and no longer is a limitation as such.

So, I’ve opened a bug in Microsoft Connect (ID 554026). Be sure to vote for it!

On aside note, I’ve been away for some time now and the challenge I was giving to myself seems a bit farther now. It has been an incredibly busy month for me and I was simply unable to add more content to my blog, which is a shame. Still, I’m back and need to catch up. So in the next few weeks I’ll try my best to get back on track with the posts.

-Yaniv

(This is post number 17 for week number 22 of my SQL blog challenge)

Technorati Tags: SQL Server 2008 R2,SSMS

SSAS – Cache Usage and Profiler

2010-03-19T08:05:21Z

I recently was required to check on the performance of an SSAS instance. We had a new OLAP model with several MDX queries that we wanted to ensure were performing adequately. Now, you may know that SSAS is using caching in order to speed up its response time. Initial queries are directed to disk and once data is first read, it is then being placed in cache to serve future queries faster, eliminating the need to access disk again.

What we did first was to clear the cache, using the following XMLA command:

<DatabaseID>DBNAME</DatabaseID>

</Object>

</ClearCache>

</Batch>

Where the “DBName” stands for our OLAP database. Next we started hitting the server with MDX queries and ran Profiler against the instance to gauge response time and query behaviour. We were using the “Query Begin” and “Query End” events as well as the “Get Data From Cache” and “Query Subcube Verbose” to monitor cache behaviour.

We ran Windows Performance Monitor in the background as well, with the following counters:

1. Total Queries From File: this counter tells us the number of queries being serviced by the file system, i.e. before data is cached.

2. Total Queries From Cache: this counter tells us the number of queries being serviced by the cache.

When we ran the first MDX query, we saw an increase in the “Total Queries From File” counter and the next time we ran the same query we saw an increase in the “Total Queries From File” counter, which is exactly the behaviour that we were expecting.

However, looking at Profiler, we noticed that the “Get Data From Cache” event was raised even when we ran the MDX query for the first time. We can provide an answer to this seemingly abnormality by looking the “Query Subcube Verbose” event. When running the query for the first time, you will see an indication that the engine queries “Non-cached data” first and that issues a query on “Cached Data”. The second time you run the MDX query, we only see the "Cached Data” query events being raised. So, the way this all works is like this: the Query Processor engine will always execute its queries from cache. This is why we will always see the “Get Data From Cache” event raised, regardless of the fact cache was cleared or not to begin with. However, when no data is in cache, it will trigger a “Non cached data” query first, the Storage Engine will fetch data from disk, place it in cache, and the the Query Processor engine will query this newly fetched cached data.

Hope this will help anyone who is doing SSAS performance monitoring!

-Yaniv

(This is post number 16 for week number 16 of my SQL blog challenge)

Technorati Tags: SSAS 2008,SSAS 2005,Performance

Does “Not Supported” mean “Not Possible”? – SSAS 2008 upgrade plan…

2010-03-08T21:32:52Z

I was reading through the (excellent) “SQL 2008 Upgrade Technical Reference Guide” paper from Microsoft the other day, which describes in great detail all the best practices of how to go about and upgrade your existing SQL Server 2000 and 2005 installations to SQL Server 2008. I was especially interested in the SSAS upgrade section, as I’m in the process of planning such an upgrade in a highly sensitive and data-intensive SSAS 2005 environment, so I wanted to be sure I was not overlooking anything important.

Initially I was thinking of performing a side by side upgrade, where you install a second instance of Analysis Services on the same box, and move the databases across to the new instance. Once I’m happy with the move and the way the databases perform on the new, 2008 instance, I was planning on switching off the 2005 service, and point the applications that use the OLAP databases to the new instance. However, after reading the first paragraph of the upgrade document I was slightly surprised to read that “for SSAS 2005, only an in-place upgrade is supported”. So I went through my initial plans and checked again:

1. Can I install an instance of SSAS 2008, side by side with an existing instance SSAS 2005? I certainly can and I even have my own laptop to prove it works.

2. Can I backup an SSAS 2005 database and restore it on a SSAS 2008 instance and will that database still be readable and not require any additional modifications? I certainly can. In fact, assuming this database does not contain any features which no longer exist in SSAS 2008 (and you need to ensure this is not the case any way, regardless if you’re upgrading in-place or side-by-side), the database will be available immediately after the restore on the SSAS 2008 instance is complete and you do not need to reprocess any dimensions or cubes.

So, there you have it: I could not come up with any good reason why this upgrade option is not supported. I continued reading, and learnt that Microsoft does support either an in-place upgrade or a side-by-side upgrade if you upgrade from SSAS 2000 to SSAS 2008. In order to perform a side-by-side upgrade from SSAS 2000, you will need to use the Database Migration Wizard which only works when the source database is an SSAS 2000 database. You cannot use this tool if you need to upgrade from 2005 to 2008, then again, you do not need to use this either, because you can backup and restore just the same.

There are pros and cons to performing an in-place upgrade versus a side-by-side upgrade. For example, when performing a side-by-side upgrade on the same box, you will need to accommodate storage for double the database size you actually need. You also have a new instance name which needs to be updated in the calling application. But there are advantages in performing a side-by-side upgrade as well, namely you control the process (which is so important when you deal with production environments). You can check each database, ensure functionality has not been impacted by the upgrade and move on to test another database until you’re done and happy with the outcomes. you also don’t impact the current production environment and the SSAS 2005 instance is up and running until you decide you’re ready to make the switch.

Happy upgrading!

-Yaniv

(This is post number 15 for week number 15 of my SQL blog challenge)

Technorati Tags: SSAS 2008,SSAS 2005

In the cloud…

2010-02-25T15:21:10Z

Yesterday I presented at the Israeli BI User Group at Microsoft Headquarters in Raanana. I gave a presentation on the Data Profiling Task in SSIS and how to dynamically configure it and use it to be a part of your ETL workflow logic. I hope the crowd enjoyed it and that I was able to add value to at least some of the attendants. Meir Dudai from “Valinor” also gave a presentation about SQL Azure and the Cloud Computing concepts. Cloud computing is definitely one of the coolest buzzwords at the moment and from a second hand impression I got on this technology, using the Amazon MapReduce platform I know it is addressing a plethora of issues organizations are facing, dealing with massive data volumes that require processing and storing capacity.

I haven’t looked at the SQL Azure product, besides from what Meir presented, and from the looks of it, Microsoft still has a long way to go until this product becomes actually usable by the organizations described above. The multitude of features which are absent, the ridicules size limitations (10GB per database) and the throttling of queries which consume too much resources are nothing short of a showstopper to anyone who is considering testing, let alone moving to this environment. However, I believe these limitations are merely the signs of a product which is simply “not there yet” and will be removed as the product matures. I see great possiblities with utilizing Cloud Computing in an OLTP environment and even more so in a DWH/BI environment, where the requirements for transporting and manipulating large data sets is really a run of the mill activity and is occurring on a day by day, or even on an hour by hour basis. I also believe Cloud Computing can get us even closer to all those terms we started using several years ago, such as Real Time Data warehouse and/or OLAP. Terms I don’t think the industry has actually been able to deliver in a reliable way up until now (I’m not talking about really small data marts, of course, rather on large-scale, with TBs of data involved…)

Having said all that, I will start looking more closely on this technology, and on SQL Azure in particular, to see how the SQL Server BI product stack can be integrated with it and benefit from it.

-Yaniv

(This is post number 14 for week number 13 of my SQL blog challenge)

Technorati Tags: SQL Azure,Cloud Computing,SSIS,BI,Microsoft,SQL Server 2008 R2

SSIS – Making the Text File Destination Dynamic

2010-02-15T13:24:47Z

One of the data flow destination objects in SSIS is the Flat File destination object. You use this destination object when you export data from a source (SQL Server, Oracle, or anything else for that matter) to a flat file. This can come handy when you maintain an Operational Data Store (ODS) in a Data Warehouse environment and you need to store data in text files before loading it to the Data Warehouse database for example or when you need to distribute a segment of data to the outside world in an easy, accessible and readable format.

In SSIS, this Flat File Destination object needs to be linked with a Flat File Connection Manager. In this object you define the location of the file, the code page and the structure of the file (columns, data types, data length etc.). The problem with using this approach is that once a Flat File Connection Manager has been configured, the file structure has to be maintained and adhered to for the duration of the lifetime of the package. This means that if the source data has changed in any way, for example a data type change or an addition of a column, you will need to modify the connection manager to comply with these changes.

There is another option of how to deal with these kind of data changes using a Script Component as a Destination object. This component still needs to be linked with a Flat File Connection Manager, however, this connection manager needs to contain a single, blank, generic column and you do not need to add columns to this object at all.

When you configure the Script Component, you can use the StreamWriter Class to access the file defined in the Flat File Connection Manager and start appending data to it, based on the source data columns. This way, when you have a new column, you only need to modify the code in the Script Component and not the file definition in the connection manager.

At the end of the day, you cannot escape changing sections of your SSIS package when meta data changes, and its a personal preference whether you choose to use the connection manager configuration or the Script component to do so. Personally, I find the Script component changes more manageable.

As a side note, I have made some performance tests using the Script component as a destination as opposed to a Flat File destination and the results were similar with a slight preference towards the script component, and this is yet another reason (for me) to use it.

-Yaniv

(This is post number 13 for week number 12 of my SQL blog challenge)

Technorati Tags: SSIS,Flat File

SSIS – Importing data from an Oracle data source

2010-02-08T19:49:46Z

A quick note this week: this may be a known issue for some of you, I have only got to know this little trick the other day when I had to import data from an Oracle database to a SQL Server database (believe it or not – I did not have to use an Oracle database as a source up until now…). There were quite a few tables involved and a considerable amount of data to be moved, so the obvious solution was to use SSIS for this task. If you want to use the Microsoft provider for Oracle you have to install an Oracle client on the machine that runs your SSIS packages, so that was pretty easy to accomplish and once the client was installed we started to configure the ole-db source adapter. The data was a mix of numbers and character-type data and the next message that popped up the minute I configured the source was: “Cannot retrieve the column code page info from the OLEDB provider. If the component supports the “DefaultCodePage” property, the code page from that property will be used…”. Needless to say, when you hook this into a SQL Server ole-db destination and try to run the package, it does not run and throws this error again.

So, what to do? You need to change the “AlwaysUseDefaultCodePage” property of the Oracle ole-db source to True. This way the component will indeed use the machine’s default code page and things should go smooth from this point onwards.

-Yaniv

(This is post number 12 for week number 11 of my SQL blog challenge)

Technorati Tags: SSIS,Oracle,SQL Server 2008

PowerPivot – Part 4: what’s inside?

2010-02-04T04:52:01Z

In this 4’th instalment of the PowerPivot blog posts series we’ll take a quick look at PowerPivot internals. Firstly, what you need to understand is that a PowerPivot database is similar to an Analysis Services cube, but it is not exactly the same, certainly not in the way it stores data internally within the Excel file.

There is an easy way to see what is inside an Excel file (thanks to the crowd at the recent Israeli BI User Group meeting…): simply change the Excel file extension from xlsx to zip and then open the “compressed” file to look at its content. What you will see there is a bunch of xml files as well as other files. What we are interested in is the PowerPivot data file, which can be located under the /xl/CustomData/ folder under the name “item1.data” (it may come in other names, I have not made any tests on this, though). You will see that this file is the file that takes up much of the disk space that the Excel file in general takes. So, when you open up an Excel file containing a PowerPivot model. this data file is read into memory and from this point onwards, all the slicing and dicing of data and calculations is done in-memory, which is how PowerPivot models are able to work really fast.

What I could not find in the Excel file zipped content is any trace of the familiar Analysis Services folder structure (such as the map, header files). So, the format is not the same but the behaviour of the PowerPivot model is the same: you can query a PowerPivot model using MDX, you can create named sets and so on. As important is the fact that you can deploy a PowerPivot model to Sharepoint, using the Sharepoint SSAS module and later on you can use this model as a source to an Analysis Services database.

There is an excellent post describing the client architecture of PowerPivot here. You can also read more about how Analysis Services server can hook into a PowerPivot model on BOL here. This pre-release article discusses how SSAS can connect to a Sharepoint PowerPivot model in a VertiPaq mode. The vertiPaq engine is the special PowerPivot data engine that enables the in-memory storage of data and the quick calculations and retrieval of data and aggregations.

-Yaniv

(This is post number 11 for week number 10 of my SQL blog challenge)

Technorati Tags: SQL Server 2008 R2,PowerPivot,Excel 2010