Programatically changing powerpivot query

Question

Hi,

We are using excel services to present in MOSS a set of pivot tables to each user. the stored procedure behind is the same for all users, but the parameters are different. I need a way to progrmatically change the parameters of the query (what I would do in table properties in side PP window) so I can autmate the files creation.

I figured out I could just hack the file by unzipping it, but I hope there is a better way to do it, or imitate this, either directly to the file or through web.

thanks

Answer 1

Hello

We are facing the same problem. We created a PP report with 5 different data sources (2 SQL & 3 MDX queries). For each of these queries we would like to update one or more parameters. The report would be used as template to create monthly reports.

Thanks

Eddy

 

---

Eddy N.

Answer 2

I'm trying to work arround it by modifing the XLSX file. right now it is not working, but I'll let you knwo if it does.

Answer 3

 

Hello t_ras and Eddy,

 

We currently do not support changes on what data will the user see based on who the user is. And personally I don't think it's possible to code a workaround right now. What I can suggest you is to have different workbooks for each (class of) user, and trim the data accordingly.

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 4

Thanks for the answer. The issue is I need to automate the creation of the connection to filter the data for each users, because this are different customers, I can have them seeing all each others data.

As far as I could see - I sow the query in the XML file, but changing it had no effect on the PP, when I opened it in PP it looked the same....Any help on how to hack it will be helpfull, thanks.

Answer 5

Is there some way to edit \xl\customData\item1.data? or replace it with ABF file?

Answer 6

Thanks for the quick reply. I could manage to change the queries in the XML but got some strange errors on concatenated fields which were used to make a relationship (PP for MS Excel).

As far as I could read PP for Sharepoint does not support parameterized queries. If previous statement is correct, it is regrettable and a bit strange because the PP query designer is build on/make use of the component MS SQL Server 2008 R2 Report Builder 3.0 which is supporting parameters.

The company I'm currently working for, is obliged to have perform a cross check of their invoices in an alternative system, in this case PP (which is also the best choice). For reason the time needed for importing data and performing the calculations is time consuming a good option would be to schedule this process by night. This way no experienced MS Excel users are required who have to chance the variable values in multiple DS which is also an error prone process.

I don't want to be negative. My experience with powerPivot until is very satisfying. It is a very powerful tool with its DAX functions, which avoids a lot of coding.

Sample query:  

SELECT
  NON EMPTY
    {
      [Measures].[CAPACITY]
     ,[Measures].[PALLIATIVE_CAPACITY]
     ,[Measures].[Activation Treshold]
    } ON COLUMNS
 ,NON EMPTY
    {
        [Access Point Subscription].[Access Point EAN Code].[Access Point EAN Code].ALLMEMBERS*
        [Access Point Subscription].[Access Point Display Name].[Access Point Display Name].ALLMEMBERS*
        [Date Time].[Date And Time].[Date And Time].ALLMEMBERS
    }
  DIMENSION PROPERTIES
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM
(
  SELECT
    {
      [Access Point Subscription].[Access Point Display Name].&[Aquiris _ Buda]
    } ON COLUMNS
  FROM
  (
    SELECT
      {[Date Time].[By Year].[Month].&[2009]&[6]} ON COLUMNS
    FROM [PROTO_SUBSCRIPTION]
  )
)
WHERE
  [Date Time].[By Year].[Month].&[2009]&[6]
CELL PROPERTIES
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

---

Eddy N.

Answer 7

Hi t_ras,

 

 

That item1.data is an analysis services database in vertipaq mode. It's possible for you to replace with another backup of an AS database (abf) if you may, as long as it is in Vertipaq mode (the classic molap abd will not work) and it has the same structure of the original one... otherwise the objects in Excel won't know how to interact with it as it will be looking for certain data source names, certain tables, etc...

The xmls that goes along the xlsx are backup of what is in the database itself, in case it gets currupted PowerPivot will try to rebuild using those.

 

Hope that helps.

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 8

 

Hello Eddy,

 

 

This sample query is a query used to retrieve data from a data source, correct? What do you want to parameterize?

Could you import all the data that you need and build a model that the user would see whatever he wants to see by clickin in a slicer or something? From what I can see you are importing data from June 2009. What if you import everything and use a slicer to browse through the years and months?

The data could be refreshed with new data nightly using by scheduling data refresh in PowerPoint for SharePoint...

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 9

Ok, here is what I did:

1) turned the item1.data into item1.abf

2) loaded it into SSAS and creted DB

3) changed parameters (both in partitions and in sandbox!)

4) deleted item.data from the package (all uging System.IO.Package of course)

5) created a new one

6) loaded it through stream (using byte buffer) to the new one

That did it.

Unfortunatly I gave up on this question and had to find out a long my self, which cost me some time Mariano could ahve save me :(

Any way Mariano -Thanks for the answer, now I know what I do should work and it is not a coinsidence.

What about item13.xml inside /CustomXML follder? this conatins the query also, should I change also this one?

 

Here is some code, and thanks for the help:

 

      //opan as package
      pack = Package.Open(@"C:\Users\eternity2\Documents\Writer3.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
      //this is the infamous PP file
      Uri uri = new Uri("/xl/customData/item1.data", UriKind.Relative);
      //the modified abf file
      FileStream fs = new FileStream("new.abf", FileMode.Open);
      byte[] b = new byte[fs.Length];
      fs.Read(b, 0, b.Length);
      PackagePart part = pack.GetPart(uri);
      string type = part.ContentType;
      pack.DeletePart(uri);
      pack.CreatePart(uri, type);
      part = pack.GetPart(uri);
      Stream stw = part.GetStream(FileMode.Open, FileAccess.Write);
      stw.Write(b, 0, b.Length);
      stw.Flush();
      stw.Close();
      //this holds bavkup info, you should change this too or if something happens the PP will be retored to this 
      uri = new Uri("/customXml/item13.xml", UriKind.Relative);
      part = pack.GetPart(uri);
      StreamReader sr = new StreamReader(part.GetStream(FileMode.Open, FileAccess.Read));
      string st = sr.ReadToEnd();
      type = part.ContentType;
      pack.DeletePart(uri);
      pack.CreatePart(uri, type);
      part = pack.GetPart(uri);
      //just some parameters replace I did
      st = st.Replace("1,4390", "3,4390");
      b = new byte[st.Length];
      b = Encoding.Unicode.GetBytes(st);
      Stream sw = part.GetStream(FileMode.Open, FileAccess.Write);
      sw.Write(b,0,b.Length);
      fs.Close();
      sr.Close();
      sw.Flush();
      sw.Close();
      pack.Flush();
      pack.Close();

 

Answer 10

Mariano

The attached query is just an example where the period (year/month) should be parameterized. But point of discussion here is, and I think I'm also speaking for t_ras (and probably XASD http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/thread/b579e218-e4f0-4153-81d1-eac96692d423), to have a more accessible way to manipulate our data sources. PP under MS Excel has also its limitations i.e. RAM memory. Most laptops and PC's only have 4GB RAM and enterprises with a thousand and more employees will not update the hardware that easily.

We are not always interested in to replicate an almost complete database in memory, we are only looking for flexible way access to our datasources, which can be automated, download only these data we are interested in, make use of the power of PowerPivot and its statistical formulas. Lesser data means also means lesser CPU processing (calculations).      

The workaround of t_ras is smartb but it would save us, developers, if there would an interface. PowerPivot is a great tool and it be more powerful if it is more open/accessible.  

Best,

---

Eddy N.

Answer 11

I agree with Eddy. PP has an incredible potential to revolotionise BI, but if it has no API for atumation it might not make it.

 

Also -added comments to the code.

Answer 12

 

Hi Eddy,

A great deal of the time users are interested to have all the data in memory to do analysis without having to do complex pre-calculations to build aggregates and things alike. The idea is to do it as needed and fast, very fast.

 

I see you point, though. PowerPivot is in its first version, and as such we focused in the user experience (and not the developer experience) since we are building up our self-service BI story. The next wave will focus more on the BI developer, as disclosed in the PASS conference last month...

Thanks,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 13

Mariano

Your statement is true and for this we have OLAP Pivottables. But cubes have to be built too which takes weeks and more likely months to have it deployed. It requires expert knowledge of MDX too.

I'm currently working as consultant for a company where I had to find a alternative solution for the cross check of their invoices. They were astonished I had the results in one week time without a single line of code, nor in VBA or .NET nor in MDX. Today they are more convinced of what PowerPivot can do. Because I only had 3GB RAM memory available I had to take into account the volume of data I imported.     

With PowerPivot you don't get a cluttering of pivottables and formulas in worksheets. It overcomes the limitations of multiranged pivottables and the shortcomings of OLAP pivottables (building super cubes to access multiple cubes at a time), and lot of other stuff. You also get a better distinction between data and report results.

Next step is to automate processes to save time and to avoid errors with bringing up to date of selection criteria.

I'm aware PP is only in its first version, but I may say it is already very good start. Keep up the good job. 

  Thanks

 

---

Eddy N.

Answer 14

t_ras

Many thanks for sharing your knowledge. I'm going to perform some experiments with your code too. 

---

Eddy N.

Answer 15

My plesure!

here is the full mounty: 

 //open the server
      Microsoft.AnalysisServices.Server srv = new Server();
      srv.Connect("sp2010-poc\\powerpivot");
      //load xml to with alter query of the databaeviw
      XmlDocument xd = new XmlDocument();
      xd.Load("alter.xml");
      //update parameters and execute
      srv.Execute(xd.OuterXml.Replace("2,4390", "1,4390")); 
      srv.Update(); 
      Microsoft.AnalysisServices.Database db = srv.Databases.GetByName("new");      
      db.Process();
      db.Refresh();
      //load xml to with alter query of the partition
      xd.Load("alter1.xml");
      //update parameters and execute
      srv.Execute(xd.OuterXml.Replace("2,4390", "1,4390"));
      //do all updates
      srv.Update();
      db = srv.Databases.GetByName("new");
      db.Process();
      db.Refresh();
      //get the backup file
      db.Backup(@"C:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup\new.abf",true);
      //opan as package
      pack = Package.Open(@"C:\Users\eternity2\Documents\Writer3.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
      //this is the infamous PP file
      Uri uri = new Uri("/xl/customData/item1.data", UriKind.Relative);
      //the modified abf file
      FileStream fs = new FileStream(@"\\sp2010-poc\Backup\new.abf", FileMode.Open,FileAccess.Read);
      byte[] b = new byte[fs.Length];
      fs.Read(b, 0, b.Length);
      //recreate item1.data
      PackagePart part = pack.GetPart(uri);
      string type = part.ContentType;
      pack.DeletePart(uri);
      pack.CreatePart(uri, type);
      part = pack.GetPart(uri);
      Stream stw = part.GetStream(FileMode.Open, FileAccess.Write);
      stw.Write(b, 0, b.Length);
      stw.Flush();
      stw.Close();
      //this holds backup info, you should change this too or if something happens the PP will be retored to this 
      uri = new Uri("/customXml/item13.xml", UriKind.Relative);
      part = pack.GetPart(uri);
      StreamReader sr = new StreamReader(part.GetStream(FileMode.Open, FileAccess.Read));
      string st = sr.ReadToEnd();
      type = part.ContentType;
      pack.DeletePart(uri);
      pack.CreatePart(uri, type);
      part = pack.GetPart(uri);
      //just some parameters replace I did
      st = st.Replace("2,4390", "1,4390");
      st = st.Replace("3,4390", "1,4390");
      b = new byte[st.Length];
      b = Encoding.Unicode.GetBytes(st);
      Stream sw = part.GetStream(FileMode.Open, FileAccess.Write);
      sw.Write(b,0,b.Length);
      fs.Close();
      sr.Close();
      sw.Flush();
      sw.Close();
      pack.Flush();
      pack.Close();