Everything you need ot know is in SQL Server 2000 Books on Lline. Of courseHow do you create a new cube, that only exists of linked objects. As i see it, you can only create a cube by selecting source, fact table etc. - and then afterwards add linked objects to the cube....
Hmm seems like the easiest way is to create a cube, is to select at fact and dimension - finish and the delete those again. Then you have e "blank" cube and can add the linked objects....Thanks
SelvaHi Selva,
This is nothing to do with your question. But I was wondering if you have been able to drill down hierarchy levels from your cube in your excel pivot table? Because I can't. Have you tried that. Also, I will say, that I had similar issue browsing a cube from the pivot table, but I had to filter my pivot table, so I wouldn't be pulling so many rows (3 million). Filter it down and see how it responds.
I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.
My client is Excel 2003 Pivot table.
From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.
If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.
Where is the problem any idea?
Thank you - Ashok
Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.
Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
In other words how to cancel/kill these "runaway query" without restarting server?
I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?
|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.|||
you can use the MSAS Activity Viewer tool and you can kill sessions.
|||What is MSAS Activity Viewer tool ?
I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.
My client is Excel 2003 Pivot table.
From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.
If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.
Where is the problem any idea?
Thank you - Ashok
Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.
Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
In other words how to cancel/kill these "runaway query" without restarting server?
I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?
|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.|||
you can use the MSAS Activity Viewer tool and you can kill sessions.
|||What is MSAS Activity Viewer tool ?
Hello,
I try to maximize cube performance with partitioning. As a test, I have created a very thight partition for one customer only and compare the cube performance between this customer and other customers in larger partitions.
I am a little confused because I do not see a difference.
I also note that when I include calculated measures in the result set, it really knock-down the performance. example, this calculated measure:
iif([Margin Type].CurrentMember.name = "Historical",
iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost])/[Measures].[AMT])
, iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost Inception])/[Measures].[AMT]))
Really slow-down the cube even for the customer that has a dedicated partition.
The expected result is a matrix of 10,000 rows for 4-5 attributes and 4 columns of measures plus 4 calcuted measures times 8 quarters. unfortunately, I need that much data.
I tried to set the non-empty behavior of this calculated measure to be both types of cost and removed the isempty or 0 test but did not see any change.
I also did both a 30% aggregation plus the user queries optimization.
The fact table is 6M rows, 2 of the dim tables are 300K rows. I run SQL2005 enterprise RTM (no sp1 yet, I did not test it). I use Excel 2003 pivot as front-end.
So is there any benefit in creating lot of small partitions to boost most likely users queries?
I am learning, reading and trying but I lack the practical experience.
Any insights?
Thanks
Philippe
Does this perform any faster?
Create Member Measures.yourcalcmeasure = 1 - [Measures].[Margin1 Cost Inception] / [Measures].[AMT];
(Measures.yourcalcmeasure, [Margin Type].Historical) = 1 - [Measures].[Margin1 Cost]/[Measures].[AMT];
(and try to deal with "divide by zero" errors in the client application)
Thank you
1. How can I change the report parameter from a Date type into a member of the Date dimension? I need to do this because it's easier for business user to select the date and I can set a sensible default to it.
2. How can I create a hourly sales analysis report for all branches? If I add a chart into SSRS, I will get all shop data summing up right now into a single chart. Instead, I would like to have a chart for each branch. My business user will not accept to manually select different branch and generate the required chart.
3. How can I add a percentage to the corresponding value? My business user may want to view the sales performance during 1:00pm ~ 2:00pm comparing to the whole day. Moreover, they may also want to compare the sales on Monday to the sales for the whole week in percentage sense.
Thanks for any advise.
Regards,
AlexI've solved the third question. In order to do this, I need to add a new column to the matrix. After that, I put the following into expression of the new cell to calculate the percentage.
=Count(Fields!Sales_Count.Value)/Count(Fields!Sales_Count.Value, "matrix1_Day")
This solution is available in the book Microsoft SQL Server 2005 Reporting Services by McGrawHill pp.327-331.
Hope this can help someone later on.
Regards,
Alex
