So you’ve got a bunch of XML data. How do you get it into PowerPivot? Various ideas:
- Import it into Excel using its normal data input capabilities. Once you’ve got the data into a worksheet, you have two options depending on if you are going to modify it later on. If the data is dynamic, i.e. you might change it by-hand later on, then mark the range and convert it into a table. Once you have a table, you can create a linked table with PowerPivot. The advantage is that you can go back and edit the data and PowerPivot will re-import the data on the change. However, a far simplier approach is an option if the data is static and read-only. If this is the case then there is no need to link a table. Once you have the data imported, then just cut-and-paste it directly into PowerPivot – and you’ve got a new table. From here you can create relationships and start integrating the data. The only problem, in both of cases, is that the data cannot be refreshed.
- Import the data into SQL Server and from there load it into PowerPivot. In this case the data may be refreshable depending on the format and source. This is a common IT function, typically solved via XML datatypes, and SSIS – so a DBA should be able to help you.
- Use SSRS (Reporting Services). Once you have a report on top of the data, then you can export it as a data feed to PowerPivot. So long as the report can render any updated data, this means that the information is fully refreshable by PowerPivot using its automated data refresh facility.
Hope this helps.
_-_-_ Dave
Recent Comments