We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
I'm having an issue running the clustering algorithm in the data mining view of Visual Studio. The databases connect properly and the data subsequently loads. However, upon clicking on the "Mining Model Viewer" tab, I receive the following error message:
Errors in the metadata manager. The D Msample ~MC cube has no measure groups. Errors in the metadata manager. An error occurred when loading the D Msample ~MC cube, from the file, '\?C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataewDM_sample.0.dbD Msample ~MC.2.cub.xml'.
We are not using a data cube, so I am assuming that this file is being called through the clustering algorithm. Furthermore, I have run the same process on different systems successfully. The only difference I can detect is that this error resulted on a 64bit system.
The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
I created SSAS cube in VS 2008 and have been able to deploy it successfully to the server. While creating the cube I was able to browse dimensions and all underlying tables just to make sure it has data. After deploying successfully when I drag and drop any measure group to browser it does not display anything.
The only thing I did different from straightforward cube building process was that when I created those measure groups the partitions that were created by default were giving me some unknown errors so I had to delete them in order for cube to process successfully.
Did that made any difference because I thought partitions are for improving query performance and has nothing to do with cube processing errors.
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'.
I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube
I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
we are having an existing cube in that we need to update with new measures . The Measure groups are added to the cube as linked object. so when we are updating the measure group it is throwing the exceptions as follows..“Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'SalesActual', cannot be verified against the source object.”
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.
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
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.
I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?
I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.
Would appreciate any suggestion for my enlightenment. Thanks
I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach
how can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.
[measures].[test]' select [measures].[abc] on 0, [xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1 from ( select (tail([month].[month].[month].members,6))on 0 from [cube])
I know where the data files are etc, but what is the physical structure of a cube like on disk?
Logically we see the data as a star. Is the physical file akin to a star also? Or is a single file a measure group containing all the required member and measure data - thereby eliminating the need for physical join operators.
I am planning to implement the security in my cube..
Total sales is 1000..
Customer Sales A 100 B 200 C 300 D 400
Total Sales 1000
When i want to give access to the customer A, he can capabule of viewing only A value 100... But the problem is A is able to see total sales 1000.. How can i restrict the sales a value to 100 in the cube.. Where i have to set the property to fix this issue.....
Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
I have a cube. Its xml is different at some point than its design view. Suppose for some dimension and its attributes, source table is different than what it showing in the properties window for them..
Is this possible? How to read cube xml because there are repeating tags in it. There are two type if dimension tags.. one has only attributed and other has all properties.
Is it possible to have more than one cube under one SSAS database? For example I have a database "Test" and in this the cube exist is "TestCube", is iit possible to deploy another cube "TestCube2" under the Test databse?
If yes then what is the process to do that, the reason I am asing is there are some common dimensions used n both the cubes and I am not sure what is the best way so that I can use the shared dimension?
How can we select translated text of cube translations, like measures, dimensions, dimension attributes. I have below query which return translations of dimension attribute's memebers
WITH MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082") MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036") SELECT { Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0 ,[Product].[Category].MEMBERS ON 1 FROM [Adventure Works]
I am getting translations of product category members but I want to get translated text of "Category"?
1.Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.
2.Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements: a.IS NULL- b.GROUPING- c.AS-
3.Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
4.Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows. a.A CUBE query with Grouping used to distinguish Null values. b.A CUBE query showing a multidimensional cube. c.A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.
5.Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.
Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.
I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.
Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.
I have designed a cube. It has two fact tables and some dimensions. Fact table to fact table is many to many relationship.
For example
FactMain DataKey(PK), StartDateKey, PostCodeKey, TotalCost FactBridge DataKey(FK), ProductKey(FK), Position - PrimaryKey on DataKey + ProductKey + Position DimProduct ProductKey(PK), ProductCode
Cube is built successfully, processed successfully.When I try to process the cube from agent job, I am getting error "Attribute key not found: tablename, value..." I have added a job step to run AnalysisServices Command. I have taken the command from cube process script(taken from manually process the cube and take script generated). I used ProcessAffectedObjects = "true" in the script. When I checked the tables, the key does exist. Why am I getting this error?
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
I have been trying to get the ValueR column of the following query through the MDX but instead getting ValueW as output of the MDX
select exp(Log(sum(MTMROR)+ 1 ))-1 as ValueW, exp(sum(Log(MTMROR + 1)))-1 as ValueR from Temp_Performance where Rundate in ('2015-03-01','2015-03-02')
MDX written for the above query is
With Member [Measures].[LogValuePre] as ([Measures].[MTMROR] + 1) Member [Measures].[LogValuePre1] as VBA
[Code] ...
[MTMROR] measure has the aggregate function Sum. What i can get from this behavior is MDX is first aggregating the result and default aggregation function is Sum. When i try to see the value with more granular data by having the date dimensions on the row (un-commenting the date dimension) i get the correct log and exp log values. Its showing the correct value as date dimension is most granular level in the fact table. While trying to get the data at less granular level(Fund level), getting the sum function applied automatically.
If i set AggregateFunction to none in the cube structure, i get null as the output.
How could i apply the log function before the sum function in the [MTMROR] measure?
I am in process of automating a cube migration from SSMS 2008 to SSMS 2012.
In this process iam deleting the existing cube databases and restoring them on a different location on the same server. When i try to execute the restore command or restore using UI i get a wierd error message like this below:
"TITLE: Microsoft SQL Server Management Studio ------------------------------ File system error: The following error occurred while opening the file 'DrivePath3F9D4D128D5E417FA6F2[CUBEDBNamepath].fact.map'. Server: The current operation was cancelled because another operation in the transaction failed. (Microsoft.AnalysisServices)
