Different Reportparameters For Same Dimension In Different Cube
May 24, 2007
Hi,
I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.
Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .
So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.
How can i solve this?
Thanks,
Dennis
View 3 Replies
ADVERTISEMENT
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
Apr 16, 2004
I have a fact table with a simple integer lookup key into a basic dimension table. However, some of the fact lookup key fields are NULL. I would like the Analaysis Services reports to show this NULL category. Instead, Analysis Services discards any NULL entries and the records are completely absent from the reports. What is the best way to achieve this?
Thank you!
View 2 Replies
View Related
Sep 11, 2006
Hi,
I am working with several tables, but for now I just mention 4 : one is fact table (named Usage), and 3 dimensional tables Periods, Products, and Regions. The fact table contains references to the dimensional tables. Table Periods contain two other columns month and year.
I created a cube containing columns from those 4 tables. Deployment was successful. Trouble comes when I want to create a mining structure using Time Series containing these columns :
- Period
- Amount (of money)
- Product name
- Region name
When I choose to use cube (instead of table) as source for mining structure, I'm forced to choose only one dimension (among the Periods, Products, and Regions). Whatever dimension I choose I end up being unable to use the column period as the Time-Key column. Effectively I cannot use Time Series method since I cannot use the column period.
(1) Why is this so [why Visual Studio forced us to use only one dimension from the cube] ?
(2) Why Visual Studio eliminates the column period, column that has relationship with the
time dimension?
(3) What is the use of Cube anyway to the mining? Is there still any use for it?
(4) What is the solution to that kind of problem I face?
Thank you,
Bernaridho
View 6 Replies
View Related
Apr 11, 2008
Hi guys,
I made a cube with time dimension with hieracly year/month/date/hour
the problem is that dimension is growin to fast. In older version of MSSQL (2000) the same dimension doesn't grew so much.
Any ideas? The table is big (may be around 1 500 000 rows per month) now it contains around 4 500 000 rows.
View 19 Replies
View Related
May 5, 2015
Best way to print Dimension Usages with Measure Group for any CUBE.
This actually facilitate business people to understand which dimensions mapped to which measure group.
View 2 Replies
View Related
Feb 28, 2007
Hello,
I have an Analysis Services Cube that I would like to report on. However, the Time Dimension currently only has four columns, Day of Month, Month(name) , Year, and DateKey (DateTime representation at midnight for every day). Thus when I drag the month attribute onto the report, it is sorted April - August - December - etc. instead of Jan - Feb - Mar. How do I fix this? I remember reading something in the MSDN Library about it but I can't find it again now.
Thomas
View 5 Replies
View Related
Nov 21, 2006
We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.
Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?
View 5 Replies
View Related
Sep 1, 2015
I'm using SQL-Server 2008, Visual Studio 2013. I've got created Linked Object (Linked Measure) in Cube2 from Cube1. Everything was fine, but I edited Measure in Cube1, as I found documentation there is no ability to refresh Linked Objects so I deleted and recreated Linked Measure on Cube2. After It I can't process Cube2, receiving following errors:
MdxScript(Cube2) (10, 24) The dimension '[Dim]' was not found in the cube when the string, [Dim], was parsed.The END SCOPE statement does not match the opening SCOPE statement.
View 3 Replies
View Related
Apr 27, 2007
Hi all
sorry im new with using Reporting Services and even more inexperienced with using cubes.
My situation is as follows. I perform dynamic grouping (user selects the view via a parameter) Depending on the view selected, I need to change the dimension filter in the dataset.. Is this possible ?
Regards,
Neil
View 5 Replies
View Related
Jul 22, 2015
For Example: I have one dimension named as "Name", Under this I have "FirstName" and "LastName" Attributes are there.But when i drag "Name" dimension, By default "First Name" dragged. But i Want "Last Name" should drag.
View 6 Replies
View Related
Jul 22, 2015
I have a dimension like Districts, Under that 2 Attributes are there i.e,District ID and Districts. When i drag Dimension "Districts", in OLAP grid it come District ID first. But i want Districts to drag first. How can we sort Attributes(District ID and Districts) for a dimension.
View 6 Replies
View Related
Jul 23, 2005
Hi,This is probably a classic scenario with a shared dimension that weneed to use in different cubes, where all fact tables do not offer thesame level of detail. Dimension is snow-flaked.The cube that's causing me troubles was designed by marking the lowestdimension level Diabled and not Visible. This allows me to get rid ofone of the snow-flake tables (the one with the lowest level), thusallowing an INNER JOIN with the remaining table which has a level ofdetail corresponding to the fact table.When processing the cube, I get a 'member with key '[blah]' was foundin the fact table but was not found in the level '[blah]' of thedimension '[blah]'' that seems to indicate that none of my fact foreignkeys exist as primary keys in the dimension table. However if I thenattempt to query the cube, all data seems to be there.Would anybody be in a position (and willing ;-)) to share his/her ownexperience working around a similar issue?Thanks,SRL
View 2 Replies
View Related
May 15, 2015
When I want to create a dimension i always end showing up errors below:
COPY
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
[Code] ...
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
The following system error occurred:
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'DB LAB2', Name of 'DB LAB2'.
[Code] .....
View 2 Replies
View Related
Aug 3, 2015
I have built a fact table and few dimension views in Datamart with the aim of creating a Cube.
On the Fact table I have added a CASE Statement with the following threshold for Premium due amounts:
CASE WHEN....
'Due_0-1_Month'
'Due_1-2_Month'
'Due_2-3_Month'
'Due_Over_3_Months'
'Overdue_0-1_Month'
'Overdue_1-3_Month'
'Overdue_3-6_Month'
'Overdue_Over_6_Months'
...END
I then created a Dimension to link this to:
CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'
[Code] ....
I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.
somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?
how I should structure my tables in SQL or any MDX queries to resolve this.
View 0 Replies
View Related
Jul 31, 2015
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).
View 2 Replies
View Related
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
May 27, 2008
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies
View Related
May 19, 2008
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
View 3 Replies
View Related
Apr 13, 2005
I ask the question again more clear!
I need to have the same calculeted member for diferentes dimensions.. can i do one calculated member only, that include the diferent dimensions.
Thanks
Paul
View 2 Replies
View Related
Jan 29, 2008
I have table with many rows, and I want to change the structure of base. It is possible to create 3-dimensions table?
View 14 Replies
View Related
Feb 6, 2005
As promised, question #2... ;)
As I mentioned in my previous post, my "real" case is a cube with 79 dimensions, most of which virtual, have been added for convenience.
Think for instance about a time dimension... Wouldn't it be nice to get a matrix with years horizontally, months vertically and displaying say the number of order you had for each cell in the resulting grid.
Ok, maybe you can do this with MDX but not in Excel, unless you create virtual dimensions for the Year and Month levels.
That's all good, but as it is, in my real case, I end up with four date dimensions for which I have to provide:
YQMD (Year, Quarter, Month & Day) hirarchized dimension
YWD (Year, Week, Day)
YQMD (Fiscal calendar)
Year
Quarter
Month
Week
Day (as 1, .. 31)
Week Day (for periodicity analysis over a week's time)
Date (as individual day - this is the backbone for the virtual dimensions)
It turns out this makes 40 dimensions by itself. For the sake of it, I grouped them by 4 hierarchies, although I've seen no specific functionality off of this in the data browser or Excel, so it really seems to be only for "show".
Now in my previous post I explained how I "spread" my session count to calculate a conversion rate. Given the number of dimensions I have (very high segmentation at the order level, very limited segmentation at the session/visit level), this means my calculated cell formula looks like this (hold your breath, it's ugly):
SubCube: {[Measures].[Session Count]}, DESCENDANTS([Business Unit].[All]), DESCENDANTS([Channel Source].[All]), DESCENDANTS([Currency].[All]), DESCENDANTS([FO Product Segment].[All]), DESCENDANTS([FO Quarter].[All]), DESCENDANTS([FO Value Segment].[All]), DESCENDANTS([Is Cancelled].[All]), DESCENDANTS([Is Customer Care].[All]), DESCENDANTS([Is Design Services].[All]), DESCENDANTS([Is Direct].[All]), DESCENDANTS([Is Estimated].[All]), DESCENDANTS([Is Holiday Order].[All]), DESCENDANTS([Is New].[All]), DESCENDANTS([Is Sales Services].[All]), DESCENDANTS([Language].[All]), DESCENDANTS([Payment].[All]), DESCENDANTS([Product Segment].[All]), DESCENDANTS([Value Segment].[All]), DESCENDANTS([FO Channel].[All]), DESCENDANTS([Reg Channel].[All]), DESCENDANTS([FCD].[Date].[All]), DESCENDANTS([FCD].[Day].[All]), DESCENDANTS([FCD].[Mth].[All]), DESCENDANTS([FCD].[Qtr].[All]), DESCENDANTS([FCD].[Wk].[All]), DESCENDANTS([FCD].[WkDay].[All]), DESCENDANTS([FCD].[Year].[All]), DESCENDANTS([FCD].[Fiscal].[All]), DESCENDANTS([FCD].[YMQD].[All]), DESCENDANTS([FCD].[YWD].[All]), DESCENDANTS([FRD].[Date].[All]), DESCENDANTS([FRD].[Day].[All]), DESCENDANTS([FRD].[Mth].[All]), DESCENDANTS([FRD].[Qtr].[All]), DESCENDANTS([FRD].[Wk].[All]), DESCENDANTS([FRD].[WkDay].[All]), DESCENDANTS([FRD].[Year].[All]), DESCENDANTS([FRD].[Fiscal].[All]), DESCENDANTS([FRD].[YQMD].[All]), DESCENDANTS([FRD].[YWD].[All]), DESCENDANTS([FSD].[Date].[All]), DESCENDANTS([FSD].[Day].[All]), DESCENDANTS([FSD].[Mth].[All]), DESCENDANTS([FSD].[Qtr].[All]), DESCENDANTS([FSD].[Wk].[All]), DESCENDANTS([FSD].[WkDay].[All]), DESCENDANTS([FSD].[Year].[All]), DESCENDANTS([FSD].[Fiscal].[All]), DESCENDANTS([FSD].[YQMD].[All]), DESCENDANTS([FSD].[YWD].[All])
Caculation Value: CalculationPassValue(([Business Unit].[All],[Channel Source].[All],[Currency].[All],[FO Product Segment].[All],[FO Quarter].[All],[FO Value Segment].[All],[Is Cancelled].[All],[Is Customer Care].[All],[Is Design Services].[All],[Is Direct].[All],[Is Estimated].[All],[Is Holiday Order].[All],[Is New].[All],[Is Sales Services].[All],[Language].[All],[Payment].[All],[Product Segment].[All],[Value Segment].[All],[FO Channel].[All],[Reg Channel].[All],[FCD].[Date].[All],[FCD].[Day].[All],[FCD].[Mth].[All],[FCD].[Qtr].[All],[FCD].[Wk].[All],[FCD].[WkDay].[All],[FCD].[Year].[All],[FCD].[Fiscal].[All],[FCD].[YMQD].[All],[FCD].[YWD].[All],[FRD].[Date].[All],[FRD].[Day].[All],[FRD].[Mth].[All],[FRD].[Qtr].[All],[FRD].[Wk].[All],[FRD].[WkDay].[All],[FRD].[Year].[All],[FRD].[Fiscal].[All],[FRD].[YQMD].[All],[FRD].[YWD].[All],[FSD].[Date].[All],[FSD].[Day].[All],[FSD].[Mth].[All],[FSD].[Qtr].[All],[FSD].[Wk].[All],[FSD].[WkDay].[All],[FSD].[Year].[All],[FSD].[Fiscal].[All],[FSD].[YQMD].[All],[FSD].[YWD].[All],[Measures].[Session Count]), 0)
If you read all this, you can see already the cryptic dimension names like "FRD", "FSD" and so forth... that's because with the real names ("First Refund Date", "First Ship Date", the query processor errored out... visibly there is a limit in the size of the formulas you can post!
Is there no other way to achieve this result? Basically I mean to say: if the session count is not defined at your level along this dimension, go to the root of the dimension to get the value there, this along a slew of dimensions, many of which are inherently dependent because of the usage of virtual dimensions (therefore if I wish to go to the root of my "First Refund Date" for instance, I wish to do so along all sub-dimensions... Heck, as far as I'm concerned this is conceptually only ONE dimension, just with various views upon it...
Using hierachies I was sort of hoping for the ability to have something like:
[FRD].[All Hierachies].[All]
Am I just asking for too much or do I just not know (quite probable) the magic keyword that can do this?
Where this is becoming quite critical is that I actually have a calculated cell that goes as follows (abridged):
SubCube: similar as before
Calculation: [Measures].[Order Count]*CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Distributed Marketing Cost])/CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Order Count])
Now the purpose of this is to distribute external costs at the order level. In short, say that you know you spent $10,000 globally promoting a specific group of websites in commission money (you pay $1 for each order). This formula allows me to get that a specific website, with 20 orders incurred an additional marketing cost of $20. That's actually the object of my next thread's question (spreading a multiplication through the aggregations)
For the purpose of this thread I am just concerned about the size of my formulas. Renaming the dimensions seemed to "buy" me some margin and I was actually quite surprised to find that the formula still fitted in, and works, but it is only a matter of time until I have to add more dimensions and the whole thing blows up in my face. Adittionally this is obviously not pleasant to look at and maintain.
Any better solution?
Thanks,
Seb
View 1 Replies
View Related
Jan 13, 2006
hello all,
I'm learning SSAS2005 I've installed and currently are running throught he
tutorials.
I've run into a problem where i'm able to process the cube with no errors.
i'm able to browse the cube with no probems.
however when I open a dimension and try to browse the dimension I get the
following error.
"Input String was not in a correct format"
I was able to save the project to another users machine and run without
problems. So we know it has to do with a set up on my machine.
any ideas are appreciated.
thanks
View 1 Replies
View Related
Sep 4, 2007
Hello,
I am trying to make a time dimension in analysis services. Is it possible to include the hours of a day in the dimension ? Is necessarily the day level the lowest of the hierarchy?
Thank you
ST
View 3 Replies
View Related
Jan 25, 2007
We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.
All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?
1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.
2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.
Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.
View 12 Replies
View Related
Jul 24, 2007
Hi,
I have Created Dimension Security by restricting the user to see only his information whoever login to the system by creating a new role. It is working fine by checking the cubes by changing the role through change user option but when the cube was integrate it to any Analysis services reporting tool(Excel, Proclarity) inspite of the roles mentioned it displays the records of all the user.
Can anyone please help me out in this situation???
View 2 Replies
View Related
Nov 20, 2006
I am new to data mining and have a question about OLAP dimensions built from models. Do you know if you can use a dimension that was created by the mining model wizard in the same cube that is being used as the source for mining model it self? I keep getting an error about a dependency loop and just want to make sure that I am not trying to do something impossible.
If this is illegal, do you know of a way of doing this without essentially having one cube dependant on another via a mining model and dimension? I tried to use the wizard to create a mining off of a table but it did not give me the option of creating a dimension from it.
View 5 Replies
View Related
May 29, 2008
how do i join a dimension on itself? i'm pretty sure this isnt really how MDX is supposed to work, but I've run into a case where i'm hoping this can be done in MDX. i'll try to simplify as much as possible. if my Customer dimension contains these attributes:
[Customer]:
- [FullName]
- [ID]
- [ParentID]
Is it possible to have a query return a customer's FullName, ID, and FullName of the ParentID.
Example with values:
FullName, ID, ParentID
Jim, 1, 1
Jane, 2, 1
A query on ID=2 getting (FullName, ID, FullName of ParentID) should return: (Jane, 2, Jim)
Thanks in advance
View 3 Replies
View Related
Aug 14, 2007
(This is the Twilight Zone......not so much)
What are the pros and cons to using SSAS to create time dimensions based on a date field in the fact table as opposed to a stand alone time dimension table.
I can see many problems with loading a time dimension table. The date is from the same table as most of my fact data. I have a column in my OLTP that sets last change date so I can tell if my fact data is an insert or update but it wouldn't tell me if the date column had been changed, just some column in the table. I'm going to have several thousand sales on any given day so I'll be reading a lot of rows just to put one row into the dimension.
From a SSIS point of view I'd think leaving the date in the fact table would be better.
View 5 Replies
View Related
Apr 4, 2007
Hi!
How do I first load data into dimension tables and then Fact?
View 3 Replies
View Related
Feb 12, 2006
I hope to pass a dimension such as int A[5] to a stored procedure
A[0]=10
A[1]=20
...
Can I do that in sql server 2005? if so , please give me a sample, thanks!
View 1 Replies
View Related
Dec 16, 2005
Hello
I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)
Any ideas how I could implement that ?? This would help a lot, thanks for your help,
Aurore Bui.
View 2 Replies
View Related
