none
OLE DB Data Source, Data Set Issue - Tips needed RRS feed

  • Question

  • Cannot use the below code in MDX Query designer because it screams at me saying that I have different dimensionality on columns. 
    I am assuming that I cannot use measures dimensions and dimensions together in query designer. 

    So I created an OLE DB data source connection. But the issue with that is the column  fields i specified gets clubbed together. Like I get 
    Questionnaire.Questionnaire Question Order_1_Questionnaire.Question Code.CHILDREN. But I need individual fields to use in the report designer (tablix) 

    Please give me some ideas to solve this with either through MDX query designer or through using OLE DB source. I will return the favor someday. Thanks.


    SELECT 
     {(
       ([Questionnaire].[Questionnaire Question Order].CHILDREN) * 
       ([Questionnaire].[Question Code].CHILDREN )* 
       ([Questionnaire].[Question Code Suffix].CHILDREN) * 
       ([Questionnaire].[Question Response Code].CHILDREN),
      [Measures].[Survey Count] 
      )} ON COLUMNS, 
      
     NON EMPTY { ([Demographic].[Gender].CHILDREN * 
     [Customer].[First Name].CHILDREN  *   
     [Customer].[Last Name].CHILDREN * 
     [Customer].[Last Name2].CHILDREN  * 
     [Customer].[Address1].CHILDREN * 
     [Customer].[Address2].CHILDREN  * 
     [Customer].[Address3].CHILDREN  * 
     [Customer].[Address4].CHILDREN  * 
     [Customer].[Postal Code].CHILDREN  * 
     [Customer].[Phone Number1].CHILDREN * 
     [Customer].[Phone Number2].CHILDREN * 
     [Customer].[Phone Number3].CHILDREN  *
     [Customer].[Email].CHILDREN * 
     [Customer].[Language Code].CHILDREN  * 
     [Customer].[Iso Country Code].CHILDREN  * 
     [Transaction Date].[Date].CHILDREN  * 
     [Dealer].[Dealer Code].CHILDREN * 
     [Vehicle].[Vin].CHILDREN * 
     [Sample].[Registration Number].CHILDREN  * 
     [Receipt Date].[Date].CHILDREN  * 
     [Cati Start Time].[Time].CHILDREN * 
     [Cati End Time].[Time].CHILDREN 
     )}  ON ROWS
      FROM ( SELECT ( { [Report Period].[Report Period Type].&[Month] } ) 
      ON COLUMNS FROM ( SELECT ( { [Report Period].[Report Period Name].&[Apr-10] } ) 
      ON COLUMNS 
      FROM [PET CO]))
      WHERE [Questionnaire].[Questionnaire Type].&[Sales]
    Monday, June 14, 2010 3:11 PM

Answers

  • I think the problem isn't the query - I suspect that it just returns a lot of data, and it means your instance of BIDS runs out of memory when it tries to preview the report. What you need to do is deploy the report and see if the report renders when you run it on the server, where presumably there's more memory.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Friday, June 18, 2010 8:33 AM
    Moderator

All replies

  • You're designing a SSRS report, right?

    Is there any reason why you can't rewrite your query so you have the attributes from the Questionnaire dimension on rows, and then pivot the data in the tablix? Here's what the query would look like:

    SELECT 
     {[Measures].[Survey Count] } ON COLUMNS, 
      
     NON EMPTY { ([Demographic].[Gender].CHILDREN * 
     [Customer].[First Name].CHILDREN  *   
     [Customer].[Last Name].CHILDREN * 
     [Customer].[Last Name2].CHILDREN  * 
     [Customer].[Address1].CHILDREN * 
     [Customer].[Address2].CHILDREN  * 
     [Customer].[Address3].CHILDREN  * 
     [Customer].[Address4].CHILDREN  * 
     [Customer].[Postal Code].CHILDREN  * 
     [Customer].[Phone Number1].CHILDREN * 
     [Customer].[Phone Number2].CHILDREN * 
     [Customer].[Phone Number3].CHILDREN  *
     [Customer].[Email].CHILDREN * 
     [Customer].[Language Code].CHILDREN  * 
     [Customer].[Iso Country Code].CHILDREN  * 
     [Transaction Date].[Date].CHILDREN  * 
     [Dealer].[Dealer Code].CHILDREN * 
     [Vehicle].[Vin].CHILDREN * 
     [Sample].[Registration Number].CHILDREN  * 
     [Receipt Date].[Date].CHILDREN  * 
     [Cati Start Time].[Time].CHILDREN * 
     [Cati End Time].[Time].CHILDREN *
     [Questionnaire].[Questionnaire Question Order].CHILDREN * 
     [Questionnaire].[Question Code].CHILDREN * 
     [Questionnaire].[Question Code Suffix].CHILDREN * 
     [Questionnaire].[Question Response Code].CHILDREN
     )}  ON ROWS
      FROM ( SELECT ( { [Report Period].[Report Period Type].&[Month] } ) 
      ON COLUMNS FROM ( SELECT ( { [Report Period].[Report Period Name].&[Apr-10] } ) 
      ON COLUMNS 
      FROM [PET CO]))
      WHERE [Questionnaire].[Questionnaire Type].&[Sales]

    This would work with the regular SSAS data source type too. 

    Re your problem with the OLEDB data source, no, there's no way of stopping all of the column names being merged into one unfortunately.

    Chris

     


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Tuesday, June 15, 2010 2:28 PM
    Moderator
  • I appreciate your reply. 

    I did try it on rows. SSRS crashes if I put the questionnaire on rows, it has to cross join too many fields(8 dimensions cross joins). The cube has lot of data for this dimensions. 

    But when I put the questionnaire dimension on columns(like above). It does take a little time(1 minute) to execute, and as well as it gives me all the questions cross joined with the measure group(survey count). 

     

     

     

    Tuesday, June 15, 2010 3:46 PM
  • Yet to  fix the broken code. Ideas please
    Wednesday, June 16, 2010 12:32 PM
  • What error do you get when SSRS crashes?

    Another alternative you could look at is the third-party addin for SSRS, Intelligencia Query (disclosure: I have a financial interest in this tool). It will allow you to build reports with any MDX query you want, including the one above. You can find out more here:

    http://www.it-workplace.co.uk/IQ.aspx

    http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1797.entry

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Wednesday, June 16, 2010 2:08 PM
    Moderator
  • My company would have to fire me to get the licence for a third party tool. 

    System out of memory error in SSRS.

    Wednesday, June 16, 2010 2:52 PM
  • OK, so the next question is - why do you need to display so much data in a single report all at once? Some parameterisation would allow users to return just the data they want to see and improve performance; if you wanted to drill down from Country to Customer to Question, you could use an approach similar to the one I describe here to only retrieve the data that's actually needed:

    http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!4194.entry

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Wednesday, June 16, 2010 3:26 PM
    Moderator
  • This is a raw data report. One full dump into excel from the report designer and then it goes to the client. 

    So I cannot limit any fields with parameters. Is there any other work around.

    Wednesday, June 16, 2010 5:02 PM
  • If the final goal is a file dump to excel, why not invoke the data from excel directly?  connect form Excel to SSAS and dump the data there, would that work?


    Javier Guillen
    Wednesday, June 16, 2010 6:56 PM
  • It should work. Very good point. 

    But my team would need the query as well. This will go into the web report soon. 

    Wednesday, June 16, 2010 7:47 PM
  • This blog post shows two techniques for dumping the results of an MDX query into Excel:

    http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!6009.entry

    ...although both would suffer from the same flattening of the columns as SSRS does.

    Going back to SSRS, do you get the error in BIDS when you test the report or do you get it when you run the report on the server?

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    • Proposed as answer by Deepak Biswal Thursday, June 17, 2010 9:40 AM
    Thursday, June 17, 2010 8:40 AM
    Moderator
  • I run the report in BIDS and export the results into an Excel. But BIDS crashes when I run the query I posted.

    I am not deploying it to the server. Its all BIDS.

     

    Thursday, June 17, 2010 1:54 PM
  • Can you try deploying the report to the server and then running the report? I'm assuming that you've got a lot more memory available on your SSRS server than on whatever machine you're developing on.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Thursday, June 17, 2010 2:10 PM
    Moderator
  • Let me give it a try Chris. Thanks
    Thursday, June 17, 2010 2:57 PM
  • Well, I could not access my report server. help desk is fixing my SQL instance. Everything going against me. 

    So Chris,   is there any way to change or optimize the query? Just to make it work. 

    Thursday, June 17, 2010 6:52 PM
  • I think the problem isn't the query - I suspect that it just returns a lot of data, and it means your instance of BIDS runs out of memory when it tries to preview the report. What you need to do is deploy the report and see if the report renders when you run it on the server, where presumably there's more memory.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Friday, June 18, 2010 8:33 AM
    Moderator
  • That worked. You are the man Chris. 

    My local report server had enough memory to spit out the report. All these while I was using the company server.

    I owe you dawg.

    Friday, June 18, 2010 6:05 PM
  • No problem, glad to hear we got to the bottom of it.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Saturday, June 19, 2010 10:24 PM
    Moderator