TOPCOUNT In MDX Cube GUI
Sep 17, 2007The GUI in RS does not let me hand edit the underlining query, so how do I define MDX functions like TOPCOUNT?
View 5 Replies
ADVERTISEMENT
MDX - TopCount
May 25, 2007Necessary to demonstrate Sales for the 10 bigger Customers and the addition of the remain.
For example:
Customer 1 ..... 10000
Customer 2 ..... 11000
Customer 3 ..... 12000
Customer 4 ..... 13000
Customer 10 ..... 14000
Others ..... 9000
I obtained through of the TopCount to search the 10 greaters, but I do not have idea of as to obtain "Others" in my MDX.
Thanks,
Silvana
Using MDX To Get The TopCount
Mar 27, 2007I am using a simple MDX to get the TopCount and it is working. The issue is that there are typos and miss spelled words in the primary attribute of my selection.
Therefore, I am trying to use a calculated member to get the intersection name (see code below). I am getting "#error" for the [Measures].[Intersection] in my results.
if I add the [Crash].[Intersection Streets].[Intersection Streets] in my topcount expression then I get the wrong topcount result (typo's).
Any ideas how to accomplish this.
Thanks,
with
Member [Measures].[Intersection] as
' [Crash].[Intersection Streets].[Intersection Streets].currentmember.uniquename'
select
{[Measures].[Crash Count],
[Measures].[Injury Crashes],
[Measures].[Fatal Crashes],
[Measures].[Fatalities],
[Measures].[Intersection]}
on columns,
Topcount( CrossJoin([Crash].[County].[County],
[Crash].[City].[City],
[Crash].[TS Crash Route].[TS Crash Route],
[Crash].[TS Crash Mile Station].[TS Crash Mile Station] ),
10, [Measures].[Crash Count])
on rows from
(select ([Crash].[County].[County],
[Crash].[City].[City],
[Crash].[TS Crash Route].[TS Crash Route],
[Crash].[Intersection Streets].[Intersection Streets],
[Crash].[TS Crash Mile Station].[TS Crash Mile Station])
on columns from [SDM_Reports]
where ([Crash].[City].&[Springfield]))
where ([Reportability].[Is Reportable].&[Yes],
[Crash].[Is Intersection].&[Yes],
[Crash].[Statistical Year].&[
Power Pivot :: Aggregating Time Periods In Cube-member / Cube-value Formulas?
Aug 23, 2015I 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.
Analysis :: Creating Cube With AMO - Cube Has No Measure Groups?
May 19, 2015I 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]....
GetSchemaDataset For Cube Restriction With Space In The Cube Name
May 27, 2008When 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 RelatedWhat Is CUBE ?
Oct 20, 2006Can any body help me to know about cube in dataminig?
How is the cube represented in database?
How to querry the cube?
Cube - Browsing
Feb 4, 2005unable to browse the cube - unspecified error - ms sql server
--- even the sps are up to date..
what is the missing link...
thank u
Restore Cube
Mar 3, 2005I have an archieve cube as .cab file and i have restored my cube into analysis server , but i am not able to browse the datain the cube. My doubt is- Does the cube contains data with it to browse or not? or we have to explicitly enter data at the sql query analyser.
Thanks in advance.
Cube Problem ....
Sep 8, 2005Hi ALL,
Just started researching Analysis Services product, and am playing w/ foodmart db (located on my C: drive) and creating cubes and dimensions, etc.
My problem is that I cannot browse any dimensions once they are created. For exampe, for a dimension called product I am getting an error message, "Unable to browse the dimension 'product'. Unspecified error"
Can anyone offer help?
thanks so much,
Alan
Creating A Cube...and Some ?'s
Jan 26, 20041.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.
Levels In A Cube
Feb 24, 2004I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.
What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)
Cube History
Mar 25, 2004I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.
Cannot Browse Cube, Please Help
Aug 21, 2007I have installed SQL Server 2005 on a server which runs Windows Server 2003 with SP2. And I have two client machines, both running Windows XP with SP2. Machine A has a full version of SQL server 2005 installed, including database engine, analysis service server, reporting service server. Machine B only has workstation components installed, BI development studio, SQL Server management studio.
I have developed a couple of analysis service projects and deployed to the server. On machine A, I can connect to the server and browse cubes no problem. On manchine B, I still can connect to the server and see the projects I deployed, even browse dimensions data. But when I browse cubes, management studio will be hanging there, not doing anything. Eventually I will have to kill it. I also opened the projects on machine B from BI management studio and try to browse data from there, it shows 'loading data' and doesn't do anything either.
Does anyone experience this type of issue? It's really frustrating as it doesn't show me any error message.
Thanks for any help.
Process Cube Changes
Sep 11, 2007We are using SQL 2005, Visual Studio 2005, SSIS, and SSAS. We have built our Dimensional model in SQL 2005, we have build our packages to complete full refresh of Dimensional model using SSIS. We have built SSAS cube using VS 2005. We built source, data source, cube and dimensions using auto build. We processed cube in VS 2005 by right clicking on solution and click process. Cube was built in Analysis Services. We made some schema changes to model then data changes in SSIS packages. We then pulled up cube in VS 2005 right click on solution and process. Cube is being re-built. After completion we check cube using Proclarity and Excel 2007 and notice the schema changes and data changes did not take. We dropped cube, then deleted data source, dimensions, and cube then re-created data source view and cube auto build then process and now have new schema changes and data changes.
Why is process not working to re-build schema and data changes when we have process FULL selected, Changes only. We even tried rebuild, deploy, and process. What is it we are missing or not doing correctly?????????
Topic
Generating A Cube ..
Feb 19, 2008Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.
Thank You
How To Create Cube
Jul 20, 2005Hi All,I want to create a cube using MSSQL2000 i dont know any thing how tocreate and what is parameter required so if any body help me i will bethankfull to him/her.For information : i have 4 tablescustdetailtable which contain all about a customer as per examplecustomer ID,name,add,CUSTOMERID IS PRIMARYKEY,Proddetail table which contain all about a product meanproductcode,productname,productvalue,dman (dateof manufacture),dexpdate of expire,etc etc PRODUCTCODE IS PRIMARY.Sales this table contain data aboutsale mean sales detailcusomerid,productcode,noofpieces,priceperunit,prod uct name,date ofsale. etc etc SALEID IS PRIMARYKEY,PRODUCTCODE AND CUSTOMERID ISFOREIGNKEY AND PRODUCTCODE IS PRIMARYKEY,and atlast i have a table name stok detail which contain productid,DTOFman and DTofexpire and amount of producthere product id,DTOFman and DTofexpire are foreign key.
View 3 Replies View RelatedCognos Cube
Oct 25, 2007Is it possible for RS to connect to a Cognos cube?
If so, where can I find information on it especially how security within cognos is evaluated/verified.
What Is CUBE And ROLLUP???
Sep 16, 2007
Hi,
Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???
Are they usefull in any point of view from ADO.NET???
How Do You Set Parameters On A Cube?
Oct 30, 2006I had no problem setting parameters with a relational database. But with a cube its so much more difficult. When i check the parameter box in the dataset it gives me way too many options..when i only want 1999, 2000, 2001, 2002, 2003, 2004, 2005 and 2006. Instead, its displaying all the quarters in each of those years, so i get this long list. Is there any way i can fix that. And actually get the parameter to work. Because when i click one, it doesnt look like it queries the report. Whats the process for putting a parameter in the report? Am i missing something?
View 4 Replies View RelatedError That Has Nothing To Do With My Cube
Nov 5, 2005I right click on my AS cube and click "Process". It tells me the server content is out of date would I like to build and deploy first. i say yes.
View 31 Replies View RelatedCan You Have A Drillthrough If Your Using An AS Cube
Jan 11, 2007Trying to drillthrough one report to another. But im not getting the correct values. I dont know why its not working. I created the parameters and linked them in the properties of the clickable field. What am i missing? or is it more complex in a cube to drillthrough then relational tables?
View 3 Replies View RelatedNo Cube Selected
Oct 8, 2007Hello,
I've gotton both the sprocs in these tutorials to work in the C# app:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx
But when I try to call them in the Managment Studio I get the following error:
"No cube specified. A cube must be specified for this command to work."
What am I missing here?
Thanks in advance,
Adam
Cube Processing
May 22, 2008
Hi All,
I have 3 cubes in a single SSAS database and these cubes should be processed using the following schedule
Cube 1 - Every Day
Cube 2 - Every Week
Cube 3 - Every Month
Cube 4 - Every Day
The issue that I face is that these cubes share the dimensions and so I cant do a FULL process of these SHARED Dimensions as it will affec other cubes.
I can expect additions and deletions to my dimension data , but the structure remains the same. It would be great if someone can suggest how to go about processing the dimensions. I am confused with the number of options(Process Incremental, Process Update etc.,) available for processing the dimensions.
I will creating a SSIS package to automate the processing. One more question is say, if Cube 2 fails during a day and Cube 1 has succesfully processed on the same day earlier, how do I revert back to the old state of Cube 2? Does this mean that I need to do a back up of the SSAS database before processing each cube?
Apologies if I had missed anything.
Thanks!
Can I Create A Cube By VB.net
May 24, 2007can i create a cube by VB.net?
I wanna a dynamic Cube, so I can not define it in the Analysis Services Project.
So the problems come.
I must deploy and process and compile the cube.
That can be done?
thank you.
Create A Cube
Dec 5, 2007
Hi All,
i got a errors while creating a cubes. error comes like
Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Fact_Trng, Column: fct_trng_per_from_key, Value: 5/25/2006. 0 0
could u please solve thisa problem
SSAS05 Cube Administration?
Jul 25, 2006Hi all!
We would like to create an Intranet based cube administration tool.
Power users should be able to process cubes and dimensions. This works
and all the cubes, dimensions and even databases from the SSAS can be
displayed in the web! ASP.NET@C# combined with
Microsoft.AnalysisServices offer a lot of very good possibilities!
But we still have a problem:
How ist it possible to show some kind of progress bar (maybe in a self refreshing iframe or a new window??)??
When a cube/dimension is processed the user does not get ANY feedback
(of course the webserver waits to finish the process() method.
I tried to start a different thread an this even works!
I want to show our powerusers at least some kind of progress (%, events, what ever).
can someone help?? we are totally lost with that topic!
I already had a look at the example from:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1
But it did not lead me to a propper solution :)
TIA and regards
JohnnyR
How To Create Cube In Sql2005?
Aug 17, 2007Hi experts,
I want to create a cube using sql2005. I have read some articles which told me to create it through Visual Studio and still i cannot find where i should do that. I cannot find an "Analysis Manager" UI in sql2005 or Visual Studio. If any body could help i will really appreciate. Thanks
Cube As Stored Proc?
Apr 21, 2004Hi
This may not be possible, but still i would like to know if any of you have suggestions:
Is it possible to implement the functionality of a cube in a stored proc.
I have a table called:
Scores
Columns:
StudentID,YearID,ClassID,TestID,SubjectID,Score
Primary Key:
StudentID,YearID,ClassID,TestID,SubjectID
My requirements are:
i). For a given StudentID,YearID,ClassID,TestID,SubjectID i need to get top 5 scorers, top 10, top 15... and so on.
ii). For a given StudentID,YearID,ClassID,TestID i need to get top 5 scorers, top 10, top 15... and so on. In this case i need to get all SubjectIDs for each (StudentID,YearID,ClassID,TestID ) and sum all of them up and then get the top 5 or 10... etc...
iii). Also imagine we need to get the similar info just given the StudentID,YearID,ClassID or
StudentID,YearID
If we write Stored procedure we think will affect the performance severly, this is why we are thinking we need a cube... but i don't think we can get anylysis services with our budget... if you have any suggestions how to achieve/simplify the scenarios please let us know... It will be really appreciated.
Thanks.
Processing Cube Error 211
Aug 14, 2000When I process a new cube I recieve an error "Error (211): Unknown dimension member ' 8'; Time: 8/11/00 1:09:45 PM". Any ideas about what this error is about ?
View 2 Replies View RelatedHow To Publish OLAP Cube
May 6, 2003Hi,
I am new to SQLServer 2000.I have built a cube.I was wondering how to acess this from a client(say via excel or a browser).
Thanks,
copericus
How To Build The Warehouse Cube In The Example
May 7, 2003Hi,
I am trying to learn the MDX tutorial(http://www.databasejournal.com/features/mssql/article.php/1550061) by william Pearson.It uses the example of "warehouse" cube.I am unable to proceed with the tutorial as I don't have this cube.Any links or articles on how to build this "warehouse" cube being used in the tutorial.
Thanks,
Copernicus
Can't See Any Data When I Browse The Cube
Aug 9, 2004Hi,
I have made sure that I am a member of OLSP Adminstrator group on the server and everything seems to work al the way till design storgae and cube processing but then when I browse the cube, I do not get any data :confused:
Can someone kindly help and guide please.
Many thanks.