June 14, 2010 at 9:35 am
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]
June 14, 2010 at 10:03 am
My first piece of advice would be to start off small and add to it until you get the desired results
Also, your assumption is correct regarding dimensionality.
I found this tool and blog/forum useful when I first started out with MDX:
http://www.mosha.com/msolap/mdxstudio.htm
gsc_dba
June 14, 2010 at 11:25 am
Thanks for the reply. I started off with a little. I need a solution for this though before I could jump into the link you have given.
June 15, 2010 at 2:31 am
Does it work if you remove the Questionnaire details from the "On Columns":
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
} 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];
gsc_dba
June 15, 2010 at 6:28 am
Yes it does, I dont want to put it on rows either. It throws system out of memory exception. Because it has to cross join so many fields.
June 15, 2010 at 6:42 am
danielagger98 (6/15/2010)
Yes it does, I dont want to put it on rows either. It throws system out of memory exception. Because it has to cross join so many fields.
If you review the dimension usage, consider adding Many-to-many or similar relationships to avoid the cross joins.
Let me elaborate:
Your measures need to have associated dimensionality - therefore you "cannot" have a questionnaire survey count [Measure] without some association to your questionnaire dimension.
Albeit via your dimension usage associations, you must have some reference to questionnaire from your MDX query.
Hope this helps.
gsc_dba
June 15, 2010 at 6:51 am
Can you please explain a little more?
June 15, 2010 at 6:55 am
danielagger98 (6/15/2010)
Can you please elaborate?
Your fact tables relating to Questionnaires - do they contain IDs or references to other dimensions?
[[Sorry - of course they do :discuss: - they should have references to Dates (or calendar hierachies) and Customers ids]]
gsc_dba
June 15, 2010 at 6:58 am
They do.
June 15, 2010 at 7:04 am
So if you review your dimension usage in the cube, there should be a relationship type between your Measure group [Questionnaires] and [Customer] dimension.
This should be a regular relationship.
The information you need to display surrounding [Questionnaires] needs to be on rows, the ordering in your MDX query is important.
Try again, without any info other than [Customers] and [Questionnaires].
SELECT
{[Measures].[Survey Count]} ON COLUMNS
,NON EMPTY
{
[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*
[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];
gsc_dba
June 15, 2010 at 7:15 am
Okay. I used the questionnaire fields on rows with customer.(below), No luck, it throws system out of memory exception. There is too many fields for the server to cross join. It wont take it.
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 *
([Questionnaire].[Questionnaire Question Order].CHILDREN) *
([Questionnaire].[Question Code].CHILDREN )*
([Questionnaire].[Question Code Suffix].CHILDREN) *
([Questionnaire].[Question Response 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 [MR_ARG_NISSAN_CUBE]))
WHERE [Questionnaire].[Questionnaire Type].&[Sales]
June 15, 2010 at 7:24 am
SELECT
NON EMPTY
{[Measures].[Survey Count]} ON COLUMNS
,NON EMPTY
{
[Customer].[First Name].[First Name].MEMBERS*
[Customer].[Last Name].[Last Name].MEMBERS*
[Questionnaire].[Questionnaire Question Order].[Questionnaire Question Order].MEMBERS*
[Questionnaire].[Question Code].[Question Code].MEMBERS
} ON ROWS
FROM [PET CO]
WHERE
[Questionnaire].[Questionnaire Type].&[Sales];
Can you please try this?
gsc_dba
June 15, 2010 at 8:12 am
This takes 18 secs to give me the results. (I have tried this before)
Think about when I join all the fields on rows. And moreover the above code will not give me the survey counts for all the questions. Because we specify NOT EMPTY. If I remove NON EMPTY it will get so many records(thats how the cube is deigned) and it will crash.
June 15, 2010 at 8:23 am
SELECT
{[Measures].[Survey Count]} ON COLUMNS
,NON EMPTY
{
[Customer].[First Name].[First Name].MEMBERS*
[Customer].[Last Name].[Last Name].MEMBERS*
[Questionnaire].[Questionnaire Question Order].[Questionnaire Question Order].MEMBERS*
[Questionnaire].[Question Code].[Question Code].MEMBERS*
Exists
(
[Customer].[ID].[ID].MEMBERS
,[Measures].[Survey Count]
)
} ON ROWS
FROM [PET CO]
WHERE
[Questionnaire].[Questionnaire Type].&[Sales];
Replace [Customer].[ID].[ID].MEMBERS with the unique reference to [Customers]
Are the query results to be used in a report?
gsc_dba
June 15, 2010 at 8:28 am
Yes, have to use it in the report. SSRS wont take it. It crashes with this big code.
I appreciate your time though.
Either manipulate the code for the query designer to take, or use it in the OLE DB data source without making the columns club together or try to optimize the query so that SSRS will take this to spit out the report. any of this will work.
Some brilliant mind should come to my rescue.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply