This is my problem: I have a report with 100 records. Every record was assigned a different {country.code}. I was given a hard copy with the list of 45 countries and their respective code (1 represents USA, 2 Mexico, 3 Spain etc). My report doesn't have the name of the country, only the code. Is there something where I say something like: if {country.code} = "3" then "Spain" else...............Do I have to do this for all 45 codes with its country name? That means 45 if-then-else statements.
Is there a quick way of Having my 100 records in my report spell the country name?
Thanks for your help.
JavPut the code / name mapping into a new table and join to it.
If you can't do that then use a select case statement in a formula, e.g.
Select {table.field}
Case 1 : "USA"
Case 2 : "Mexico"
...
Default : "Unknown!"
See the help on "select expression" in the search tab, not the index tab.
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Thursday, February 16, 2012
Monday, February 13, 2012
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
Hi prakal,
the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.
Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.
You would have your query done on the 32bit box as well.
|||I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Or, if you had all of these attributes as part of a hierarchy you could just do
|||WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
A recent thread on this forum linked to a Microsoft Support article that might be relevant:
Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"
SYMPTOMS
When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
This problem may occur if the following conditions are true:
Back to the top
CAUSE
This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.
|||Darren,
Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".
Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.
|||Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.
Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {
NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])
* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])
} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
This post may help. You may want to follow up on the Analysis Services forum.
|||Thanks for your reply Teo. I have already refered to this post and unfortunately I'm unable to use the Exists function in this context due to certain limitations in our application.
I have also posted the same issue on the Analysis services forum.
Thanks again for your help.
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
Hi prakal,
the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.
Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.
You would have your query done on the 32bit box as well.
|||I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Or, if you had all of these attributes as part of a hierarchy you could just do
|||WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
A recent thread on this forum linked to a Microsoft Support article that might be relevant:
Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"
SYMPTOMS
When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
This problem may occur if the following conditions are true:
Back to the top
CAUSE
This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.
|||Darren,
Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".
Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.
|||Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.
Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {
NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])
* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])
} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Saturday, February 11, 2012
3rd-party report editors
Does anyone know of any products out there that allow end users to control the layout and fields of a report without having to install any special software on their machine?SQL 2005 has a web-based Report Builder which may do the trick for you.
SQL 2000 + Panorama Enterprise Report Builder is another option.|||You could also try ActiveReports by Data Dynamics - http://www.datadynamics.com/Products/ProductOverview.aspx?Product=ARNET2. It integrates very smoothly with .NET programs, and all the DLLs can be redistributed for free (vs. Crystal where design components require a per-seat license). Not quite as slick as Crystal or Reporting Services, but the cost and flexibility make it worth looking into.
SQL 2000 + Panorama Enterprise Report Builder is another option.|||You could also try ActiveReports by Data Dynamics - http://www.datadynamics.com/Products/ProductOverview.aspx?Product=ARNET2. It integrates very smoothly with .NET programs, and all the DLLs can be redistributed for free (vs. Crystal where design components require a per-seat license). Not quite as slick as Crystal or Reporting Services, but the cost and flexibility make it worth looking into.
josh|||Hi Josh,
I looked into ActiveReports and while it did look promising it seems the end-user designer only applies to WinForms.
Thanks for the suggestion, though.
Subscribe to:
Posts (Atom)