Dimension Calculated Members Not Visible In Perspective
May 14, 2008
Hi,
I created some calculated members for a dimension. They are set to be visible and when i connect to the cube itself I can see them with both Excel and ProClarity as clients. I added them to a couple perspectives (by checking their boxes down at the bottom of the Calculations list) and deployed. There were no errors or issues. However from both Excel and ProClarity I do not see the calculated members of the dimension.
Any ideas?
View 9 Replies
ADVERTISEMENT
May 28, 2008
Hi
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
Thanks,
Julia.
View 9 Replies
View Related
May 9, 2005
This is correct
store.currentmember.properties("Store_Manager")
I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR
Do you can help me?
View 1 Replies
View Related
Jun 21, 2006
Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????
View 1 Replies
View Related
Oct 20, 2005
I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
View 3 Replies
View Related
Mar 8, 2004
I have created a few calculated members under one dimension (meaning the parent dimension is not Measures, but other dimensions). It can be showed in the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Office 2k, xp, even 2003). Is there any solution to display the calculated members (as with the dimension) in Excel PivotTable? vba code needed? service pack needed?
View 4 Replies
View Related
Nov 29, 2005
Hi,
I created a cube with 4 calculated members. Data is ok within cube browser.
When building report in Excel with Pivot Table or with Analysis Services Excel AddIn, I can't have access to these 4 calculated members. I'm using a olap connexion.
What did I do wrong ?
Thanks a lot for ur suggestions.
Vincent
View 1 Replies
View Related
Sep 27, 2004
Hi
I've read the MSDN documentation about creating calculated members and states that we can only create session scope or query scope members.
I need to create a static calculated member, one that stays there until it's deletion... That is possibel using Analysis Services, but what about with MDX or DSO?
Thanks in advance
View 1 Replies
View Related
Oct 3, 2006
Hi all,
I have an analysis services 2005 cube with a lot of Calculated Members (for example I have a "Sales Volume in kg" and a "Sales Volume in squaremeters (m²)" and a calculated member "Sales Volume in g/m²" with (kg * 1000 / m²), and so on. This Measures should be shown with a hierarchical Product Dimension in which I can drilldown from a level1 to a level 4.
Sales Volume (g/m²)
--------------------------------------------------------------------------------
Level1
Level2
Level3
Level4
Level4
Level2
Level3
Level2
I can select them all with a MDX Query in the Data-part of reporting services. In the layout-part, I have a matrix-control which should show the values in the above form. The value-part of the matrix has the form "sum(field)". I know, it's not correct, I believe I should use "AVG(field)".
When I use non-calculated fields, all works ok. With calculated fields, I get the error
[rsAggregateOfMixedDataTypes] The Value expression for the textbox €˜textbox3€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
It's the same with sum and avg (as the error message tells me). But how can I use such fields in reporting services? My users wants a report with the product hierarchy vertical and the salews volume g/m² horizontal so that the values are correct on every level the drill in. In Excel, OWC or other analysis clients, it's no problem to do this, but I can't find a way, doing this in reporting services.
Thanks for any help
Hans
View 3 Replies
View Related
Feb 6, 2008
Hi,
It's a long story i'll try to make short, I am running a MDX query through Integration Services, this query is very very memory intensive, so intensive I get a System.outofmemory error. The workaround for this was getting the data in smaller ranges. this works fine in Management Studio.
select { many measures and calculated members} on columns,
[Cuenta].[Cuenta].&[1]: [Cuenta].[Cuenta].&[10000] on rows
from DB
WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00] : [Tiempo].[Mes].&[2007-11-01T00:00:00]
Now, in Integration services the optimal way of running a MDX query should be through an OLEDB source, but as it's stated in many sites around the web there is an error that hasn't been fixed. So I moved to a Datareder which is much slower, but works... kind of... to get the whole data out of the cube I am getting it in ranges which I should change with parameters, but MDX doesn't support parameters so I have to change the whole query using an expression.
Finally the problem with this is that the query I am running is longer than 4000 characters which is the limit for an expression on integration services. Here is when someone told me to shrink my query by using named sets, and this is what i don't know how to do, is there any way of grouping all the calculated members I have created in one named set or something alike.
thanks!
View 4 Replies
View Related
May 22, 2007
Hello!
I'm developing a report with Samsung products sales compared to other producers' sales.
I have the following dimensions:
Dim Period (date-time)
Dim Region (region where the products are sold)
Dim Product Group (notebooks, CRT,...)
Dim Producer (Samsung, LG, ..., Other producers)
Report should contain Samsung sales on the first row, other companies (LG, Dell,...) sales on subsequent rows and "Other producers" sales on the last row, i.e.:
Week 1 - SomeRegion - Notebooks - Samsung - 2350
Week 1 - SomeRegion - Notebooks - LG - 1100
Week 1 - SomeRegion - Notebooks - Dell - 3000
Week 1 - SomeRegion - Notebooks - Other Producers - 10000
How can I position Producer values in such way?
Please note, Dim Producers may be enalrged, so I can't just enumerate all Dim Producer members in the query.
I tried to use field ProducerType, which I set to 1 for Samsung, 2 for other companies (LG, Dell, ...) and 3 for "Other producers", after that I ordered by this field, however it didn't position producers as I expected.
Please see the query I used below:
Code Snippet
SELECT
NON EMPTY
{
[Measures].[Quantity]
} ON COLUMNS,
NON EMPTY
Order(
{
(
[Dim Period].[Period Week No].[Period Week No].ALLMEMBERS *
[Dim Region].[Region Name].[Region Name].ALLMEMBERS *
[Dim Product Group].[Product Group Name].[Product Group Name].ALLMEMBERS *
[Dim Producer].[Producer Name].[Producer Name].ALLMEMBERS
)
}, [Dim Producer].[Producer Type], ASC)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [ShelfShare]
WHERE ( [Dim Period].[Period Year].&[2007] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any help is greatly appreciated!
View 3 Replies
View Related
Jul 6, 2015
I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count
This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week
Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan)Â SalesVolume
1Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000
Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.
If calculated member for each week, how do I do this, any example code?
View 2 Replies
View Related
Jul 8, 2015
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])Â returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
View 4 Replies
View Related
Jul 14, 2005
greetings,
if I create a dimension from fact table, then it only has members which appeared in fact table. but if I create dimension from dimension table, then it shows all members, even those with no data. how can I show only those with existing data in member list?
thank you
View 1 Replies
View Related
Apr 4, 2006
Argent
Need to split olap dimension members into three different dimension members
example
account dimension member = 11111200900
account_num.member = 11111
item_num.member = 200
sub_item = 900
Please help me
Thanks
View 9 Replies
View Related
Mar 19, 2008
Hi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
View 3 Replies
View Related
Mar 19, 2008
Hi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
View 1 Replies
View Related
Sep 17, 2015
The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
Is there any way i can get the Member_key in a set in rows section.
Like
With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube
I want the value as just like
Value
Employees, UniqueName
View 2 Replies
View Related
Mar 2, 2006
Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?
There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.
I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .
Thanks!
View 1 Replies
View Related
Dec 29, 2006
There are some 55 members in the arrival year level of the time dimension (1995 - 2055).
I am trying to find a way to restrict the number of years returned by this mdx query to the current year - 5. Any help will be appreciated.
WITH MEMBER [Measures].[ParameterCaption]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS,
[TIME DIMENSION].[ARRIVAL YEAR].allmembers ON ROWS
FROM [TOURISM CUBE]
Thanks
View 4 Replies
View Related
Dec 2, 2015
I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:
The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.
View 5 Replies
View Related
Jul 7, 2015
We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
View 4 Replies
View Related
Jul 12, 2006
Hi,
Anybody who knows how inferred members update function in slowly changing dimension? I came across this when I started using some of the functions of the Slowly changing dimension object.
Thanks!
cherrie
View 4 Replies
View Related
Aug 8, 2007
I am in the process of develping a MSRS report using an MSAS 2005 OLAP cube as my data source. In the MSRS Query Builder, I am using an MDX query which successfully executes in Management Studio. Something like the following:
SELECT
NON EMPTY { [Measures].[Fact Count] }
ON COLUMNS,
NON EMPTY{ ( [Dim Attribute].[Hierarchy Not Visible].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [MyCube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The twist is that the AttributeHierarchyVisible property of the [Dim Attribute].[Hierarchy Not Visible] is set to False.
As mentioned previously, the query successfully executes in Management Studio. However when it is executed in the MSRS Query Builder, the following error message is displayed:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Is there a way to successfully query dimension attributes whose hierarchies are not visible?
Thanks.
View 2 Replies
View Related
Jun 2, 2015
I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.
I want to set more than one default members for dimension report.
I've read all about how dimensions can have only one default member, but I need to set more than 1.
View 2 Replies
View Related
May 21, 2008
hi all,
I was wondering if it is possible in SSAS 2005 that a calculated member is based off of an (integer) dimension attribute and another (integer) measure (let's say a multiplication operation) ?
If there a trick on doing so? other than stuffing the (integer) dimension attribute back in the fact table, as an measure?
thx much,
Cos
View 6 Replies
View Related
May 20, 2008
I want an MDX calculated dimension member, (with no measure specified in expression, so that any related measure can be used in a query/browse), and that accomplishes either one of the following:
Ideal: To calculate Median values on every level of a user hierarchy
If "Ideal" is not possible, then "Acceptable" is: To calculates Median values on one or more levels OTHER than the highest level.
View 3 Replies
View Related
Jun 25, 2015
i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.
View 7 Replies
View Related
Dec 3, 2006
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]
Any ideas?
Thanks,
Arjun
View 8 Replies
View Related
Oct 26, 2015
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
View 4 Replies
View Related
Jun 4, 2015
Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008.Â
Ex.:Â
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.Â
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
View 5 Replies
View Related
Dec 19, 2007
Hello,
My 3D bar charts don't look as good as they could because the bars are too thick when displayed in 3D (depth-wise). If I have many bars, the bars are very thin on the front, and then go back far too much. Has anyone played with the settings to see how to optimize this? Even with 5% rotations, it is still too thick for my taste and doesn't look like the previews in the designer.
Mike
View 3 Replies
View Related
Aug 31, 2006
Hi, im new to asp but have experiance with php/mysql.
Im bulding an application which connets to my Web Service.
Does anyone know of any great tutorial explaining fully how to connect a Web Service to a microsoft 2005 sql server?
I wish my Web Service to send string and arrayLists, not dataSets(or whatever) as i wish to manipulate this data before it is displayed. Thanks for your time.
View 6 Replies
View Related
