Analysis server cube Performance through Excel 2007

Hello Everybody

I am accessing Analysis server cube -2005 through Excel 2007 for building reports. When ever i select a row or column right click and select Filter > Hide Selected Items, it will show "running query on the OLAP server" at bottom and will not respond after that.

Ran the MDX generated by Excel 2007 directly on analysis server, even then also it takes lot of time and finally it doesnt respond. I read in the forum that the sql generated by Excel 2007 is different. how can i tweak in so that cube performance improves ?

Thanks
Kiran

Hi Kiran,

Do you have old AS 2000 to compare performance? The reason I am asking this because you can check if you have same aggregation setup in AS 2005 cube. If you don't have old cube to compare try creating aggregation on AS 2005 cube. Also you can check profiler, performance counter etc.

-Ashok

sql

An Amazing Errorrrrrrrrrrrrrrrr

Hi All Friends

i have an amazing error in my project

i wrote a project in ASP.NET 2

a part of this project change a value in a row in a table in sql 2005 to another value

i used it in my computer (With Local host and my own iis) and it works properly and without any problem

but when i upload it in internet it have this error:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. "

can anyone help me?

i think the error is from my table in sql 2005 but i dont know what is wrong in it

thanks for your cares friends and im waiting for your usefull answers

It means that someone changed the row between the time you initially retrieved it, and the time you did the update. Or you are pointing at the wrong database for the update.

|||

No it cannot happen

because this form has a password that no one have it and no one know about this form

it cant be

but it's a Notice here :When i delete my table and recreate it again it work probably but sometimes it has this error again

|||

Well, if you're absolutely sure that there is no way that the row could have been changed--there could be other forms or other apps that are updating the table--then there is something wrong in the concurrency code. Did a wizard generate this for you, or did you write some custom code?

Don

Am I close or way off the mark?

I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.

I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?

// stored procedure to write externally generated random number value into database

PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE Links SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

// Part One select links that need random number inserted.

public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}

// Part Two I need two write a random number back into each row

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
int LinkID; // this generates unassigned local variable "LinkID' error
int LRank; // this generates unassigned local variable "LRank' error

SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@.L_ID", LinkID);
cmd2.Parameters.AddWithValue("@.L_Rank", LRank);
SqlDataAdapter da2 = new SqlDataAdapter();

int RowIncrement;
RowIncrement = 0;
DataTable dt = GetRandLinks();
foreach (DataRow row in dt.Rows)
{
System.Random myRandom = new System.Random();
int LinkRank = myRandom.Next(25, 250);
LRank = LinkRank;
da2.UpdateCommand = cmd2;
RowIncrement++;
}

}

Question - instead of looping back to the database server for every row, can I pull the data into a DataTable, and then on the .Net web server update each row with it random number using system.Random and then finally read the updated DataTable back to update the SQL Database table? And if so is any of the code I have written useful to that end?

|||

Most of the T-SQL limitations can be gotten around by feeding the rand function the right 4 digits of the newid function.

|||

How would I incorporate that into this stored procedure? I am working on a SQL 2000 server, company hasn't got the money for SQL2007. And is there a way to limit the random number range to > 25 and < 250?

PROCEDURE spA_Random001
AS
DECLARE @.L_ID int
DECLARE @.L_Rank int
DECLARE cur CURSOR FOR
SELECT L_ID , L_Rank
FROM tblLinkInfo_OLD2
OPEN cur;
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.L_Rank > 10 AND @.L_Rank < 300)
UPDATE tblLinkInfo_OLD2 SET L_Rank = Convert(int, (L_ID)*RAND()) WHERE (L_ID = @.L_ID)
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
END
CLOSE cur;
DEALLOCATE cur;

|||

Thank you for the suggestion, it is an excellent solution for most applications however in my cash I need a strictly limit the random value to integers between 25 and 250. So far - pulling the table data into a DataSet, using the webserver resident System.Random to generate the random number and then writing the changes up to the SQL server seems to be the solution. I have been spoiled by the drag and drop GridViews and DataLists no I have to learn what is really going one. I am going to try to get th above solution working but I am beginning to think I am way of track.

Always selecting at least 10 rows?

Hi,
I use an Identity column to create row numbers displayed in a DataGrid. The
datagrid typically displays row 1-10, 11-20 etc. The datagrid is filled us
ing
SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End.
If rows 1-10 is removed from the table, there are no rows displayed since th
e select statement returns none for this range.
What I am looking for is an elegant way of retrieving at least n rows with r
ow numbers from x or above. Something along the line of
SELECT 10 FROM Tabel WHERE RowID >= 1
Can this be done, or do I need to use cursors? SqlDataReader and loop?
MortenHo Morten,
If you already have an identity column use this to order againt it.
SELECT TOP 10 * FROM Table Order by YouridentColumn
You should also consider to sue the paging machanisam in the datagrid
(if you are using .NET).
HTH, Jens Suessmeyer.|||SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
?
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Morten Wennevik" <Morten.Wennevik@.email.adr> wrote in message
news:op.sz2fvlfdg1d8xu@.tr023.bouvet.no...
> Hi,
> I use an Identity column to create row numbers displayed in a DataGrid.
> The datagrid typically displays row 1-10, 11-20 etc. The datagrid is
> filled using
> SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End.
> If rows 1-10 is removed from the table, there are no rows displayed since
> the select statement returns none for this range.
> What I am looking for is an elegant way of retrieving at least n rows with
> row numbers from x or above. Something along the line of
> SELECT 10 FROM Tabel WHERE RowID >= 1
> Can this be done, or do I need to use cursors? SqlDataReader and loop?
> Morten|||Ah, I thought that Top 10 would retrieve the top 10 RowIds, meaning the ten
largest ids. Works like a charm :)
Oh, and the paging mechanism is my own since the datagrid paging is too limi
ted.
Morten
On Fri, 11 Nov 2005 08:53:19 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Ho Morten,
> If you already have an identity column use this to order againt it.
> SELECT TOP 10 * FROM Table Order by YouridentColumn
> You should also consider to sue the paging machanisam in the datagrid
> (if you are using .NET).
>
> HTH, Jens Suessmeyer.
>|||Exactly what I was looking for. I mistook the TOP keyword for largest inste
ad of first.
Thanks,
Morten
On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas <thomasroji@.gmail.com> w
rote:

> SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
> ?|||Morten Wennevik wrote:
> Exactly what I was looking for. I mistook the TOP keyword for
> largest instead of first.
> Thanks,
> Morten
> On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas
> <thomasroji@.gmail.com> wrote:
TOP requires an ORDER BY clause to guarantee consistent results.
Otherwise, you might get somewhat random data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, forgot to add ORDER BY RowID, works fine now :)
On Fri, 11 Nov 2005 15:38:32 +0100, David Gugick <david.gugick-nospam@.quest.
com> wrote:

> Morten Wennevik wrote:
> TOP requires an ORDER BY clause to guarantee consistent results.
> Otherwise, you might get somewhat random data.
>

Alternative Color

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..Hi Zean,
You can alternate the color row of your tables putting the following
expression in the BackgroundColor property of the table or part of the table
you want :
=iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:BfSdnRTl74eizsPenZ2dnUVZ_tCdnZ2d@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
I
> can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
>|||And in case, you don't have a simple table report item, but a matrix report
item you should check the following blog article:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Maria Isabel Henao" <dev@.hotmail.com> wrote in message
news:eVUEN%23Y2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> Hi Zean,
> You can alternate the color row of your tables putting the following
> expression in the BackgroundColor property of the table or part of the
> table
> you want :
>
> =iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
> "Zean Smith" <nospam@.nospamaaamail.com> wrote in message
> news:BfSdnRTl74eizsPenZ2dnUVZ_tCdnZ2d@.rogers.com...
>> Does anyone know how to do alternative color row dynamically on reports?
> I
>> can do that in ASP.NET DataGrid.. but can't seem to find a property on
>> reporting server..
>>
>