Another Reporting System Stored Procedure Issue

Thx to all who helped me for Stored Procedure previously

Here is what I have:
3 Drop Down Boxes:
1) List of property
2) Ticket Status
3) Tech Name

All 3 Drop boxes have default value of "All"

So, if all 3 drop boxes are "All" ie.

list of property = All
Ticket status = All
Tech Name = All

Query pulls up all records from database and displays it.

Lets say if I select Tech Name is XYZ then query should pull out all property, all ticket status by Tech XYZ.

Now my previous developer has if else case and he has total 9 query for doing all this. He has used SQL along with C# code.

I am trying to modify this if-else and convert it into Stored Procedure. Is there a way I can handle all with 1 stored procedure ?

Previous Reporting works like charm......Assuming Property and TicketStatus are ints and TechName is character and assuming that -1 for @.Property and @.TicketStatus means "ALL" and that an empty string for @.TechName means "ALL" then:

CREATE PROCEDURE SprocName
@.Property int = -1,
@.TicketStatus int = -1
@.TechName varchar(20) = ''
Select x,y,x
From SomeTable
WHERE
 (Property= CASE WHEN @.Property != -1 THEN @.Property ELSE Property END) AND
 (TicketStatus = CASE WHEN @.TicketStatus != -1 THEN @.TicketStatus ELSE TicketStatus) END) AND
 (TechName = CASE WHEN @.TechName != '' THEN @.TechName ELSE TechName END)

The where clause resolves to Property = Property if @.Property is -1 which selects all values.
If @.Property is some value like 10 then the where clause resolves to Property = 10.
And so on for the other conditions.|||The poster asked this same question on another thread (view post 421457). My suggestion was similar to yours, but used COALESCE instead:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL
AS
SELECT
 column1,
 column2,
 <etc>
FROM
 myTable
WHERE
 ListOfProperty = COALESCE(@.ListOfProperty,ListOfProperty) AND
 TicketStatus = COALESCE(@.TicketStatus,TicketStatus) AND
 TechName = COALESCE(@.TechName,TechName)

Do you have any idea which approach would be better?

Terri|||Cool. I'll have to try that next time. It makes the code a lot cleaner.|||COALESCE doesn't seem to work. I will try to work with the quesry with COALESCE one more time...

Other solution idea worked perfectly...

One more thing.. do I have to pass -1 and ' ' from my ASP.NET Page which is calling this stored procedure or have to pass null ?|||When there are default parameters, like

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL
AS -- and so on...

If you do not want to pass in a specific value, do not specify a parameter. The default (in this example, NULL) is then used as the value. If you passed in '' and -1, then I expect COALESCE would NOT work as expected.|||CREATE PROCEDURE myTest

@.ListOfProperty varchar(100) = NULL,

@.TicketStatus varchar(100) = NULL,

@.TechName varchar(100) = NULL

In the same procedure can I set up

@.dateofsub smalldatetime = NULL,

so if no dateofsub is not provided, "all dates" are consider else user supplied date is used ?|||With my previous quesion I mean,

How can I setup
@.dateofsub datetime = -1 or NULL or ?

so that either I can have all dates or only user supplied date ?|||Yes, you can add as many conditions as you like. The below should work:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL,
@.dateofsub smalldatetime = NULL
AS
Select*
FromYourTable
WHERE
 ListOfProperty = COALESCE(@.ListOfProperty,ListOfProperty) AND
 TicketStatus = COALESCE(@.TicketStatus,TicketStatus) AND
 TechName = COALESCE(@.TechName,TechName) AND
 dateofsub = COALESCE(@.dateofsub,dateofsub)
GO

|||my problem is some how COALESCE is not working for me and I am trying to use if else example given ...

When I use SQL Query Analyzer and to test my stored procedure, it doesn't work with datetime I supply.

Here is what I am doing for eg.

List of property = null ( for all property)
TicketStatus = Open
TechName = XYZ
dateofsub = 10/14/2003

SO when I call stored procedure from SQL Query Analyzer

exec myTest null, 'Open', 'XYZ', '10/14/2003'

and it gives me all dates in stead of only 10/14/2003...

Any idea ?|||Here it is in the alternative syntax:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = '',
@.TicketStatus varchar(100) = '',
@.TechName varchar(100) = '',
@.dateofsub smalldatetime = '1/1/1970'
AS
Select*
FromYourTable
WHERE
 ListOfProperty = CASE WHEN @.ListOfProperty != '' THEN @.ListOfProperty ELSE ListOfProperty END AND
 TicketStatus= CASE WHEN @.TicketStatus != '' THEN @.TicketStatus ELSE TicketStatus END AND
 TechName = CASE WHEN @.TechName != '' THEN @.TechName ELSE TechName END AND
 dateofsub= CASE WHEN @.dateofsub!= '1/1/1970' THEN @.dateofsub ELSE dateofsub END

But the COALESCE should have worked too. If you'd like, post the code and we .can see if it's missing something.|||I am calling this stored procedure from Web Service.

Lets say if I pass null for Date from Web Service it gives me error...

What should I do ?

Here is how call stored procedure from Web Service

public DataSet GetHelpDeskReports(int pid, string status, System.DateTime dateofsub, string techName) {

SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("sp_trial", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameterPID = new SqlParameter("@.pid", SqlDbType.Int, 4);
parameterPID.Value = pid;
myCommand.Parameters.Add(parameterPID);

SqlParameter parameterStatus = new SqlParameter("@.status", SqlDbType.VarChar, 100);
parameterStatus.Value = status;
myCommand.Parameters.Add(parameterStatus);

SqlParameter parameterDateOfSub = new SqlParameter("@.dateofsub", SqlDbType.DateTime, 8);
myCommand.Parameters.Add(parameterDateOfSub);

SqlParameter parameterTechName = new SqlParameter("@.techName", SqlDbType.VarChar, 100);
parameterTechName.Value = techName;
myCommand.Parameters.Add(parameterTechName);

SqlDataAdapter myDataAdapter = new SqlDataAdapter();
DataSet myDataSet = new DataSet();

// Open the connection and execute the Command
try {
myConnection.Open();
myDataAdapter.SelectCommand = myCommand;
myDataAdapter.Fill(myDataSet);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message.ToString());
}
finally
{
myConnection.Close();
}

return myDataSet;
}

Now System.DateTime dateofsub in Web service can be null... If I keep it null I get error when I try to run web service.

How am I suppose to check in Web Service that parameter I am passing to SQL Stored Procedure is NULL or not...

As per discussion................................ I don't have to check in my Web Serivce coz SQL Stored Procedure handles null by means of If.. else or COALESCE...

Why I am getting Error!!!!

I tired '', "", null and ever not inputing anything in dateofsub.. in all cases I get error...|||I tried COALESCE and If Else...

My query works fine till I don't have dateofsub in my Stored Procedure.

as soon as I put
@.dateofsub datetime = null

and then call my Stored Procedure from SQL Query Analyzer

exec sp_trial null, null, '3/9/2003'

I should be getting few rows as I have data, but I don't get any rows.... if I pass null for dateofsub query runs fine and returns all rows....

Whats wrong ??|||With dates it's always something...

The problem may be that the dates in the table for 3/9/2003 have a time component and the query is implicitely asking for 3/9/2003 00:00:00.0.

I've gotten around that problem by creating a function to truncate dates to midnight.

CREATE FUNCTION TruncateDate(@.DATE1 datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.TruncatedDate datetime
Select @.TruncatedDate =null
if @.Date1 != null
BEGIN
Select @.TruncatedDate =Convert( varchar, @.DATE1, 101)
END
RETURN(@.TruncatedDate)
END

and modify your code in the sproc to use the rounded date field instead of the original:

dbo.TruncateDate(dateofsub) = COALESCE(dbo.TruncateDate(@.dateofsub ),dbo.TruncateDate(dateofsub ))

|||Got it.. You are right, I have to check time part also.. I will try to modify my Database, as I really don't need time part...

I will try to use smalldatetime as DataType...

ANother thing is, I am calling this stored procedure from C# code. At different time, I have different drop down box selected.

If I select "ALL" in drop down box, I pass "-1". Will -1 will work with dates ? or I need to have null or '' ?

Another Major Flaw with Reporting Services!

Where is the text (.txt) export functionality? I don't see any ascii text export mechanism without the .csv extension. Where is actual .TXT file of the reports. How do I get a .txt conversion out of the reports without messing with the .csv files. I would've wanted direct .txt export from the generation mechanism.

This seems extremely basic form of reporting generation. I can't believe you can't get txt report out of reporting services.

Enkh.

Text is universal format so I dont see it as a big deal that it is not available. Any of the formats exported are simply text...|||Well the main problem and is a huge problem is that when you are generating huge reports that neede to be in .txt format and when it is automated to be run every certain while, you would have to convert the .csv to .txt manually everytime. This is huge problem, I mean come on. What do you do when you have to send out thousand and hundred thousand page reports for instance to someone if he only expected to accept the reports in .txt file format. Would you go and convert each file from .csv to .txt. What about formatting? This is really absurd that they don't have .txt conversion and I hope they put out a patch or something that would make this work. Because it's basic format and stuff, doesn't make it less important in anyway. Please Microsoft do something about this, especially the Microsoft reporting services team, at least a patch come on. Please... It's about the quantity of the reports that needs to be in .txt format, and it's not just because I'm being lazy or whatever at all.

Please....

Enkh.|||

What do you envision the txt export format to look like? Even with CSV, there are so many tradeoffs. XML export (perhaps with XSLT) will give you the maximum flexibility to tailor the export to meet your requirements.

|||

Yes I'm pretty aware with the tradeoffs with the .txt generation of the report. I don't expect the formatting to be so perfect and detailed. Just some basic export function that has everything on the same line and some basic export functionality. In other words, text exporting isn't meant to be really nice report, since they are expecting something decent readable as a text so that it takes less space on their hard drive. If they want perfect report, they can do the pdf or the excel version. I would say just basic decent .txt dump of the report is extremely useful to me and other I think. The reason why I say this is because, when thousands of pages of reports are automated and generated, person doesn't have to manually open and save the .csv or .xsl or whatever to .txt format.

Please feedback. Come on automatic .txt dump of the report with decent layout, not glamorious and exact.

Enkh.

|||

So, if you abstract yourself from the fact that a csv extension suggests comma-separated output and customize the CSV renderer in rsreportserver.config as shown here, do you think that your requrements are met?

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>Whatever delimiter my users want</FieldDelimiter>

<!--more device info params if needed-->
</DeviceInfo></Configuration>
</Extension>

|||So can I just replace the "<Extension Name=" to "TXT" and it would export as a .txt file. Is that .txt format even supported at all in reporting Services? I mean, does it know how to export to .TXT format at all? This seems little more promising.|||I don't think renaming the extension will change the file extension. This is probably what the extension sets by default.|||Well in that case my requirement is not met at all. All I want out of rs is the report in .txt format and nothing more and nothing else. If I get .csv file out of it, it doesn't meet my requirement at all. All I want is <report name>.txt, please download your report now popup and saving feature. .txt format is all I need, no .csv, no .xsl, no .pdf, just only .txt file report.|||

If you choose export to a file share instead of e-mail in your subscription, you can choose the name of the file by not checking the check box underneath the file name.

So you can call your file MyFile.txt if you wish, it doesn't have to be called MyFile.csv

|||

Ok that's great, but the bottom line is that there should be another feature in the export dropdown for text, and text generation with some decent layout except the coma delimited text export. I mean something close to the actual report. I think text generation feature is fundamental to reporting services more than anything that I know now.

Enkh.

|||If it bothers you that much then write your own renderer. Then maybe we could all benefit from you fixing this "fundamental" issue.|||I'm still not sure what you mean about "text" generation. Are you talking about formatted ASCII? In this case, we would take a definition and map it to a 132 column output and replace the object positioning with tabs or spaces? If this is the case, the only scenarios that I would think this is useful for is either sending to a dot matrix printer (most printers are PostScript or PCL based these days) or embed the text in an e-mail for e-mail clients that can't display HTML output. If this is the scenario, I can't say that we have gotten lots of requests for this.|||

The thing I'm saying about "text" generation is this

1. The generated file should be in ".txt" format like "Some report.txt". So it should be downloadable ".txt" file. That's why I suggested that there should be another field in the export dropdown that says like "text" just like pdf, excel etc. Basically another format added to the export mechanism for ASCII .txt exporting.

2. In terms of layout, the layout should be different from every field placed in each column. So that means basically, if the data for the report is pulled like this from the query

columna columnb columnc columnd

a asdf df sdf

should come out like this on the report for instance (just like its design layout)

columna columnc

columnb columnd

a df

asdf sdf

So in other words, it would be exact .pdf or excel dump layout in .txt with no coma delimited list. It should be as readable as possible. So the exact design layout dumped just like that as .txt. Basically no technical and basically unreadable layout.

3. The format of the report will be plain ASCII, so that means simple and basic ".txt" file. If it's viewable and ok showing up in notepad, it's ok with me. Just basic .txt file of the report with the exact same design layout as the report (like how it shows up as in the pdf version) is what I'm saying with no special rtf, no .csv or any special formatting, techniques or languages at all.

|||

I definitely agree with ENKHT.

I have the same issue. One of our external partners expect a txt file with specfic columns and associated data.

In other words, they read each line and know exactly where the start and end point of each column. And having a txt extension is very basic. When I tell others in my team, they can't believe there is no txt extension. That's a big problem for us. We were looking forward to using Reporting Services to replace Cognos, but now I am not to sure. Its a case by case project.

Can someone please explain the exact steps to take to acquire a txt extension file ?

Another Major Flaw with Reporting Services!

Where is the text (.txt) export functionality? I don't see any ascii text export mechanism without the .csv extension. Where is actual .TXT file of the reports. How do I get a .txt conversion out of the reports without messing with the .csv files. I would've wanted direct .txt export from the generation mechanism.

This seems extremely basic form of reporting generation. I can't believe you can't get txt report out of reporting services.

Enkh.

Text is universal format so I dont see it as a big deal that it is not available. Any of the formats exported are simply text...|||Well the main problem and is a huge problem is that when you are generating huge reports that neede to be in .txt format and when it is automated to be run every certain while, you would have to convert the .csv to .txt manually everytime. This is huge problem, I mean come on. What do you do when you have to send out thousand and hundred thousand page reports for instance to someone if he only expected to accept the reports in .txt file format. Would you go and convert each file from .csv to .txt. What about formatting? This is really absurd that they don't have .txt conversion and I hope they put out a patch or something that would make this work. Because it's basic format and stuff, doesn't make it less important in anyway. Please Microsoft do something about this, especially the Microsoft reporting services team, at least a patch come on. Please... It's about the quantity of the reports that needs to be in .txt format, and it's not just because I'm being lazy or whatever at all.

Please....

Enkh.|||

What do you envision the txt export format to look like? Even with CSV, there are so many tradeoffs. XML export (perhaps with XSLT) will give you the maximum flexibility to tailor the export to meet your requirements.

|||

Yes I'm pretty aware with the tradeoffs with the .txt generation of the report. I don't expect the formatting to be so perfect and detailed. Just some basic export function that has everything on the same line and some basic export functionality. In other words, text exporting isn't meant to be really nice report, since they are expecting something decent readable as a text so that it takes less space on their hard drive. If they want perfect report, they can do the pdf or the excel version. I would say just basic decent .txt dump of the report is extremely useful to me and other I think. The reason why I say this is because, when thousands of pages of reports are automated and generated, person doesn't have to manually open and save the .csv or .xsl or whatever to .txt format.

Please feedback. Come on automatic .txt dump of the report with decent layout, not glamorious and exact.

Enkh.

|||

So, if you abstract yourself from the fact that a csv extension suggests comma-separated output and customize the CSV renderer in rsreportserver.config as shown here, do you think that your requrements are met?

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>Whatever delimiter my users want</FieldDelimiter>

<!--more device info params if needed-->
</DeviceInfo></Configuration>
</Extension>

|||So can I just replace the "<Extension Name=" to "TXT" and it would export as a .txt file. Is that .txt format even supported at all in reporting Services? I mean, does it know how to export to .TXT format at all? This seems little more promising.|||I don't think renaming the extension will change the file extension. This is probably what the extension sets by default.|||Well in that case my requirement is not met at all. All I want out of rs is the report in .txt format and nothing more and nothing else. If I get .csv file out of it, it doesn't meet my requirement at all. All I want is <report name>.txt, please download your report now popup and saving feature. .txt format is all I need, no .csv, no .xsl, no .pdf, just only .txt file report.|||

If you choose export to a file share instead of e-mail in your subscription, you can choose the name of the file by not checking the check box underneath the file name.

So you can call your file MyFile.txt if you wish, it doesn't have to be called MyFile.csv

|||

Ok that's great, but the bottom line is that there should be another feature in the export dropdown for text, and text generation with some decent layout except the coma delimited text export. I mean something close to the actual report. I think text generation feature is fundamental to reporting services more than anything that I know now.

Enkh.

|||If it bothers you that much then write your own renderer. Then maybe we could all benefit from you fixing this "fundamental" issue.|||I'm still not sure what you mean about "text" generation. Are you talking about formatted ASCII? In this case, we would take a definition and map it to a 132 column output and replace the object positioning with tabs or spaces? If this is the case, the only scenarios that I would think this is useful for is either sending to a dot matrix printer (most printers are PostScript or PCL based these days) or embed the text in an e-mail for e-mail clients that can't display HTML output. If this is the scenario, I can't say that we have gotten lots of requests for this.|||

The thing I'm saying about "text" generation is this

1. The generated file should be in ".txt" format like "Some report.txt". So it should be downloadable ".txt" file. That's why I suggested that there should be another field in the export dropdown that says like "text" just like pdf, excel etc. Basically another format added to the export mechanism for ASCII .txt exporting.

2. In terms of layout, the layout should be different from every field placed in each column. So that means basically, if the data for the report is pulled like this from the query

columna columnb columnc columnd

a asdf df sdf

should come out like this on the report for instance (just like its design layout)

columna columnc

columnb columnd

a df

asdf sdf

So in other words, it would be exact .pdf or excel dump layout in .txt with no coma delimited list. It should be as readable as possible. So the exact design layout dumped just like that as .txt. Basically no technical and basically unreadable layout.

3. The format of the report will be plain ASCII, so that means simple and basic ".txt" file. If it's viewable and ok showing up in notepad, it's ok with me. Just basic .txt file of the report with the exact same design layout as the report (like how it shows up as in the pdf version) is what I'm saying with no special rtf, no .csv or any special formatting, techniques or languages at all.

|||

I definitely agree with ENKHT.

I have the same issue. One of our external partners expect a txt file with specfic columns and associated data.

In other words, they read each line and know exactly where the start and end point of each column. And having a txt extension is very basic. When I tell others in my team, they can't believe there is no txt extension. That's a big problem for us. We were looking forward to using Reporting Services to replace Cognos, but now I am not to sure. Its a case by case project.

Can someone please explain the exact steps to take to acquire a txt extension file ?

Another installation question

I am trying to reinstall Reporting Services. I installed SQL Server and uninstalled/reinstalled IIS. When I get to the Reporting Services install page I get the message:

The prerequisite check failed for a default report server installation:

The Report Server virtual directory (ReportServer) can't be used for installation. It is already in use by another application. Setup is defaulted to "Files Only" install.

Since I've removed any program from my computer that could possible be using I don't know what could be using ReportServer. The reason I reinstalled was because my virtual directory was misplaced by the system.

How can I do a clean install of RS?

It sounds like the Virtual Directory you used before is still in use. Try installing SQL 2005 with a instance name instead of just the default. The Virtual Directory will then be ReportServer$<instance_name> and Reports$<instance_name>

Hope this helps.

Eduard
|||I tried that and got a new error message (see new thread above).|||delete those virtual directory and then create them freshly|||Been there, done that....I deleted every reference on my computer, uninstalled SQL Server, IIS, & VS, searched Registry keys, took everything off I could find.....still get error messages|||only thing left is that , the virtual directory might be of an earlier instance. so need to look out to delete those things

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb of
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also, I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in each
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MG
I don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG
|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG
|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across two
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives ??
MG
"Andrew J. Kelly" wrote:

> I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes on
> separate arrays but probably not as much overall gain from adding more disks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>
>
|||Mardy,
I am confused now as to what you actually have. You originally stated:
[vbcol=seagreen]
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives ??
> MG
> "Andrew J. Kelly" wrote:
|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the most
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drives
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as well.
Thanks again MG
"Andrew J. Kelly" wrote:

> Mardy,
> I am confused now as to what you actually have. You originally stated:
>
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you will
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due to
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
> 4 disks. That will give you twice the disk space but you will have to place
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>
>
|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...[vbcol=seagreen]
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.[vbcol=seagreen]
> Thanks again MG
> "Andrew J. Kelly" wrote:
Raid[vbcol=seagreen]
will[vbcol=seagreen]
disk[vbcol=seagreen]
to[vbcol=seagreen]
all[vbcol=seagreen]
place[vbcol=seagreen]
the[vbcol=seagreen]
very[vbcol=seagreen]
space[vbcol=seagreen]
9[vbcol=seagreen]
across[vbcol=seagreen]
load[vbcol=seagreen]
idea[vbcol=seagreen]
files[vbcol=seagreen]
files[vbcol=seagreen]
indexes[vbcol=seagreen]
more[vbcol=seagreen]
2[vbcol=seagreen]
with 2[vbcol=seagreen]
little[vbcol=seagreen]
indexes.[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
will[vbcol=seagreen]

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb o
f
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also,
I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in eac
h
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MGI don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across tw
o
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives '?
MG
"Andrew J. Kelly" wrote:

> I agree with Michael. I would use 2 disks in a RAID 1 for the log files a
nd
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes o
n
> separate arrays but probably not as much overall gain from adding more dis
ks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>
>|||Mardy,
I am confused now as to what you actually have. You originally stated:
[vbcol=seagreen]
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives '?
> MG
> "Andrew J. Kelly" wrote:
>|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the mos
t
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drive
s
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as wel
l.
Thanks again MG
"Andrew J. Kelly" wrote:

> Mardy,
> I am confused now as to what you actually have. You originally stated:
>
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you wi
ll
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due t
o
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with al
l
> 4 disks. That will give you twice the disk space but you will have to pla
ce
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>
>|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...[vbcol=seagreen]
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
>|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.[vbcol=seagreen]
> Thanks again MG
> "Andrew J. Kelly" wrote:
>
Raid[vbcol=seagreen]
will[vbcol=seagreen]
disk[vbcol=seagreen]
to[vbcol=seagreen]
all[vbcol=seagreen]
place[vbcol=seagreen]
the[vbcol=seagreen]
very[vbcol=seagreen]
space[vbcol=seagreen]
9[vbcol=seagreen]
across[vbcol=seagreen]
load[vbcol=seagreen]
idea[vbcol=seagreen]
files[vbcol=seagreen]
files[vbcol=seagreen]
indexes[vbcol=seagreen]
more[vbcol=seagreen]
2[vbcol=seagreen]
with 2[vbcol=seagreen]
little[vbcol=seagreen]
indexes.[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
will[vbcol=seagreen]

Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb of
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also, I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in each
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MGI don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across two
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives '?
MG
"Andrew J. Kelly" wrote:
> I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes on
> separate arrays but probably not as much overall gain from adding more disks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> > I'm building a reporting server. I have a server with 2 processors, 2
> > drive
> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> > of
> > memory (negotiating for more). I have about 130 gb of data and a little
> > more
> > than half is indexes. One table makes up 50% of the data and indexes.
> > Also, I
> > plan to use transactional replication.
> >
> > I'm considering creating 2 file groups, one on each drive/array and
> > separating the indexes from the data. I'm assuming this will yield a
> > performance benefit and I'm wondering if I should also create 2 files in
> > each
> > filegroup to futher enhance performance but am not sure if this will
> > result
> > in further gains.
> >
> > Thanks
> >
> > MG
>
>|||Mardy,
I am confused now as to what you actually have. You originally stated:
>> I have a server with 2 processors, 2 drive
>> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives '?
> MG
> "Andrew J. Kelly" wrote:
>> I agree with Michael. I would use 2 disks in a RAID 1 for the log files
>> and
>> add those other two disks to the other Raid 1+0 and place all the files
>> there. Yes you can get increased performance by placing data and indexes
>> on
>> separate arrays but probably not as much overall gain from adding more
>> disks
>> to the Raid 1+0 and separating the logs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>> > I'm building a reporting server. I have a server with 2 processors, 2
>> > drive
>> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2
>> > gb
>> > of
>> > memory (negotiating for more). I have about 130 gb of data and a little
>> > more
>> > than half is indexes. One table makes up 50% of the data and indexes.
>> > Also, I
>> > plan to use transactional replication.
>> >
>> > I'm considering creating 2 file groups, one on each drive/array and
>> > separating the indexes from the data. I'm assuming this will yield a
>> > performance benefit and I'm wondering if I should also create 2 files
>> > in
>> > each
>> > filegroup to futher enhance performance but am not sure if this will
>> > result
>> > in further gains.
>> >
>> > Thanks
>> >
>> > MG
>>|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the most
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drives
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as well.
Thanks again MG
"Andrew J. Kelly" wrote:
> Mardy,
> I am confused now as to what you actually have. You originally stated:
> >> I have a server with 2 processors, 2 drive
> >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you will
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due to
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
> 4 disks. That will give you twice the disk space but you will have to place
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> > Thanks for the suggestions. I should have mentioned that the size of the
> > db
> > will grow quickly to accomodate maintaining more history on the report
> > server. The size of the data will surpass the 146 gb of the one array very
> > quickly so I can't put the t-log on a separate array. I only have space
> > for
> > 4 physical drives on this box and I'll have to live with it for about 9
> > months. Consequently I see my options as 1. creating one filegroup across
> > two
> > arrays and possibly creating multiple files to improve threading and
> > performance or 2. create two filegroups and look at distributing the load
> > somehow.
> >
> > Now, considering that this is a reporting server I'm toying with the idea
> > of
> > breaking the mirrors to free up 2 drives '?
> >
> > MG
> >
> > "Andrew J. Kelly" wrote:
> >
> >> I agree with Michael. I would use 2 disks in a RAID 1 for the log files
> >> and
> >> add those other two disks to the other Raid 1+0 and place all the files
> >> there. Yes you can get increased performance by placing data and indexes
> >> on
> >> separate arrays but probably not as much overall gain from adding more
> >> disks
> >> to the Raid 1+0 and separating the logs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> >> > I'm building a reporting server. I have a server with 2 processors, 2
> >> > drive
> >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2
> >> > gb
> >> > of
> >> > memory (negotiating for more). I have about 130 gb of data and a little
> >> > more
> >> > than half is indexes. One table makes up 50% of the data and indexes.
> >> > Also, I
> >> > plan to use transactional replication.
> >> >
> >> > I'm considering creating 2 file groups, one on each drive/array and
> >> > separating the indexes from the data. I'm assuming this will yield a
> >> > performance benefit and I'm wondering if I should also create 2 files
> >> > in
> >> > each
> >> > filegroup to futher enhance performance but am not sure if this will
> >> > result
> >> > in further gains.
> >> >
> >> > Thanks
> >> >
> >> > MG
> >>
> >>
> >>
>
>|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
--
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
>> Mardy,
>> I am confused now as to what you actually have. You originally stated:
>> >> I have a server with 2 processors, 2 drive
>> >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
>> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk
>> Raid
>> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
>> drive arrays. You must have two Raid 1 drive arrays. That is a big
>> difference. If this is indeed a reporting server that usually means you
>> will
>> have lots of table or index scans and will most likely have a lot of disk
>> access. This configuration will probably not meet your expectations due
>> to
>> the drive configurations. If you are going to need more disk space than
>> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with
>> all
>> 4 disks. That will give you twice the disk space but you will have to
>> place
>> everything on one array.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>> > Thanks for the suggestions. I should have mentioned that the size of
>> > the
>> > db
>> > will grow quickly to accomodate maintaining more history on the report
>> > server. The size of the data will surpass the 146 gb of the one array
>> > very
>> > quickly so I can't put the t-log on a separate array. I only have
>> > space
>> > for
>> > 4 physical drives on this box and I'll have to live with it for about 9
>> > months. Consequently I see my options as 1. creating one filegroup
>> > across
>> > two
>> > arrays and possibly creating multiple files to improve threading and
>> > performance or 2. create two filegroups and look at distributing the
>> > load
>> > somehow.
>> >
>> > Now, considering that this is a reporting server I'm toying with the
>> > idea
>> > of
>> > breaking the mirrors to free up 2 drives '?
>> >
>> > MG
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> I agree with Michael. I would use 2 disks in a RAID 1 for the log
>> >> files
>> >> and
>> >> add those other two disks to the other Raid 1+0 and place all the
>> >> files
>> >> there. Yes you can get increased performance by placing data and
>> >> indexes
>> >> on
>> >> separate arrays but probably not as much overall gain from adding more
>> >> disks
>> >> to the Raid 1+0 and separating the logs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
>> >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>> >> > I'm building a reporting server. I have a server with 2 processors,
>> >> > 2
>> >> > drive
>> >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server
>> >> > with 2
>> >> > gb
>> >> > of
>> >> > memory (negotiating for more). I have about 130 gb of data and a
>> >> > little
>> >> > more
>> >> > than half is indexes. One table makes up 50% of the data and
>> >> > indexes.
>> >> > Also, I
>> >> > plan to use transactional replication.
>> >> >
>> >> > I'm considering creating 2 file groups, one on each drive/array and
>> >> > separating the indexes from the data. I'm assuming this will yield a
>> >> > performance benefit and I'm wondering if I should also create 2
>> >> > files
>> >> > in
>> >> > each
>> >> > filegroup to futher enhance performance but am not sure if this will
>> >> > result
>> >> > in further gains.
>> >> >
>> >> > Thanks
>> >> >
>> >> > MG
>> >>
>> >>
>> >>
>>|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
> > Mardy,
> >
> > I am confused now as to what you actually have. You originally stated:
> >
> > >> I have a server with 2 processors, 2 drive
> > >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each)
> >
> > A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk
Raid
> > 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> > drive arrays. You must have two Raid 1 drive arrays. That is a big
> > difference. If this is indeed a reporting server that usually means you
will
> > have lots of table or index scans and will most likely have a lot of
disk
> > access. This configuration will probably not meet your expectations due
to
> > the drive configurations. If you are going to need more disk space than
> > 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with
all
> > 4 disks. That will give you twice the disk space but you will have to
place
> > everything on one array.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> > news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
> > > Thanks for the suggestions. I should have mentioned that the size of
the
> > > db
> > > will grow quickly to accomodate maintaining more history on the report
> > > server. The size of the data will surpass the 146 gb of the one array
very
> > > quickly so I can't put the t-log on a separate array. I only have
space
> > > for
> > > 4 physical drives on this box and I'll have to live with it for about
9
> > > months. Consequently I see my options as 1. creating one filegroup
across
> > > two
> > > arrays and possibly creating multiple files to improve threading and
> > > performance or 2. create two filegroups and look at distributing the
load
> > > somehow.
> > >
> > > Now, considering that this is a reporting server I'm toying with the
idea
> > > of
> > > breaking the mirrors to free up 2 drives '?
> > >
> > > MG
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > >> I agree with Michael. I would use 2 disks in a RAID 1 for the log
files
> > >> and
> > >> add those other two disks to the other Raid 1+0 and place all the
files
> > >> there. Yes you can get increased performance by placing data and
indexes
> > >> on
> > >> separate arrays but probably not as much overall gain from adding
more
> > >> disks
> > >> to the Raid 1+0 and separating the logs.
> > >>
> > >> --
> > >> Andrew J. Kelly SQL MVP
> > >>
> > >>
> > >> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> > >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> > >> > I'm building a reporting server. I have a server with 2 processors,
2
> > >> > drive
> > >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server
with 2
> > >> > gb
> > >> > of
> > >> > memory (negotiating for more). I have about 130 gb of data and a
little
> > >> > more
> > >> > than half is indexes. One table makes up 50% of the data and
indexes.
> > >> > Also, I
> > >> > plan to use transactional replication.
> > >> >
> > >> > I'm considering creating 2 file groups, one on each drive/array and
> > >> > separating the indexes from the data. I'm assuming this will yield
a
> > >> > performance benefit and I'm wondering if I should also create 2
files
> > >> > in
> > >> > each
> > >> > filegroup to futher enhance performance but am not sure if this
will
> > >> > result
> > >> > in further gains.
> > >> >
> > >> > Thanks
> > >> >
> > >> > MG
> > >>
> > >>
> > >>
> >
> >
> >

Anonymous access in Reporting Services

Trying to set up a Reporting Services / Windows Sharepoint services demo
site and I am a little confused about the best way to allow anonymous
access. I would like to allow annonymous users to run reports but not set
properties, create subscriptions, or upload reports. I have seen some posts
that indicate that this is difficult to do correctly and that I should build
a custom authentication module. What is the state of the art here? Does
using SP1 or waiting for SP2 help?
Thanks,
SteveYou can assign a user to the anonymous account. Give this user just enough
rights to do what you want.
--
| From: "Stephen Walch" <swalch@.online.nospam>
| Subject: Anonymous access in Reporting Services
| Date: Mon, 14 Feb 2005 11:35:35 -0500
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OoCR3MrEFHA.2568@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69-164-66-20.lndnnh.adelphia.net 69.164.66.20
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35873
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Trying to set up a Reporting Services / Windows Sharepoint services demo
| site and I am a little confused about the best way to allow anonymous
| access. I would like to allow annonymous users to run reports but not
set
| properties, create subscriptions, or upload reports. I have seen some
posts
| that indicate that this is difficult to do correctly and that I should
build
| a custom authentication module. What is the state of the art here? Does
| using SP1 or waiting for SP2 help?
|
| Thanks,
|
| Steve
|
|
|

Announcement: MSDN Article "Integrating Analysis Services with Reporting Services" Availab

The following whitepaper is now available on MSDN:
"Integrating Analysis Services with Reporting Services" at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp.
Summary: Create a compelling solution for your customer that defines and
manages great-looking Analysis Services reports, and quickly answers
analytical questions to improve traditional reporting scenarios. (33 printed
pages)
The following topics are covered:
Introduction
Developing OLAP Reports Using Analysis Services 2000 and Reporting Services
Datasets and Data Regions in SQL Server 2000 Reporting Services
Defining a Data Source
Building a Static Report with Analysis Services Data
Adding Parameters to an OLAP Report
Adding Additional Interactivity to Reports
Analysis Services Actions
Conclusion
--
Sean Boon
Microsoft Office BI
This posting is provided "AS IS" with no warranties, and confers no rights.In news:OLSM2s7VEHA.1888@.TK2MSFTNGP11.phx.gbl,
Sean Boon [MS] <seanboon@.online.microsoft.com> typed:
> The following whitepaper is now available on MSDN:
> "Integrating Analysis Services with Reporting Services" at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/olapasandrs.asp.
> Summary: Create a compelling solution for your customer
> that defines and
> manages great-looking Analysis Services reports, and
> quickly answers
> analytical questions to improve traditional reporting
> scenarios. (33 printed
> pages)
> The following topics are covered:
> Introduction
> Developing OLAP Reports Using Analysis Services 2000 and
> Reporting Services
> Datasets and Data Regions in SQL Server 2000 Reporting
> Services
> Defining a Data Source
> Building a Static Report with Analysis Services Data
> Adding Parameters to an OLAP Report
> Adding Additional Interactivity to Reports
> Analysis Services Actions
> Conclusion
Am I missing something or does the sample code require anything other than
VS 2003 and RS? I wonder about xxx.RDL.XML, which is not recognized to be a
valid report file by ReportDesigner.
Sorry if I'm too stupid to catch the things.
r.

Angry about Reporting Services

I have seen the odd thread on here that just mentions in passing that data-driven subscriptions are only available on the SQL 2005 Enterprise edition. Does no-one else but me think that this is absolutely disgraceful? We bought SQL Server Standard for our 2 processor server and it cost just under ￡8,000. The only reason we would have to buy Enterprise is for the data-driven subscriptions in RS and that would cost us ￡33,000. When the beta of RS came out, it had data-driven subscriptions at all levels. When RS 2000 came out, we could easily get a legitimate copy of RS 2000 Enterprise from Microsoft and have the same functionality. Now we have to pay ￡25,000 for the privilege or hand-code the whole thing. I repeat; this is an absolutely disgraceful piece of profiteering from Microsoft. Do they really think that data-driven subscriptions are only of value to people using the Enterprise edition?

This forum is intended for assistance and bug reporting with RS. Call customer support if you're that upset about the product. What do you expect them to do? Give it to you for free now because you're angry?

I think it is disgraceful that you would use an open forum to vent about a product that you purchased which is documented as to what it includes and what it does not include.

|||

I think it's disgraceful that a product which was in all versions of the beta and freely available in SQL 2000, now costs me ￡25,000 in SQL 2005. Don't you?

As I believe this forum is read by Microsoft developers, maybe I thought they could give me a rational response. If you know of a place where I can get a better response, or where I ought to post this, please let me know. It is not my intention to upset anyone on the forum, just to let Microsoft know how I feel about their product.

|||

Leaving strong words aside, I think your complaint is not unreasonble and it is good to provide this kind of feedback. Crossing the enterprise hefty price tag is not an easy sell especially for vendors. I tend to agree with you we are suffering from an "enterprise" identity crisis and thus the meaning of enerprise should be better scoped out. IMO, enterprise editions should differ only in the areas of scalability and to some extend extensibility in order to meet high loads and more involved integration requirements of large companies. Following this line of thought, I'd say that web farm deployment and partitioning are definately enterprise-level features while features like data-driven subscriptions and semi-additive measures (SSAS) are probably not.

From BOL: "Enterprise Edition scales to the performance levels required to support the largest enterprise online transaction processing (OLTP), highly complex data analysis, data warehousing systems, and Web sites. Enterprise Edition’s comprehensive business intelligence and analytics capabilities and its high availability features such as failover clustering allow it to handle the most mission critical enterprise workloads. Enterprise Edition is the most comprehensive edition of SQL Server and is ideal for the largest organizations and the most complex requirements."

Analysis services?

Hi!
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...

So Windows XP Service Pack 2 is already installed.
And I need to download Windows server 2000 or 2003 R2, but where could I find a free trial version?

Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.

Since you're wanting RS, you'll need to install SQL Server Express with Advanced Services. Here's the link:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

This product is supported on XP SP2, so you won't need to do any OS upgrades.

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
I get the following answer though "Your OS does nor support the Service Pack required for this SQL server release."

So my OS is 2002...
How do I check which SP required?
I did not find any .net Framework 2.0 SP.|||I managed to download SQL Server 2005 Express Edition with Advanced

Services SP1, and noticed Reporting services are available but how about "Analysis services" ?
|||

Analysis Services does not ship with any of the Express SKUs. It is part of the other SKUs (Enterprise, Standard, etc). If you want to play around with it, you can download the evaluation version found here:

http://www.microsoft.com/sql/downloads/trial-software.mspx

Thanks,
Sam Lester (MSFT)

Analysis Services Connection problem.

I am trying to test a connection in Reporting Services to an Analysis Services 2000 database. I get a Test Connection Succeeded from one machine and I can't even select the databases in the connection properties on another machine. What can I look at that might be preventing one machine from seeing the Analysis Services Server?

Thanks.

Security. If you hardcode the Windows credentials in the connection string does it work? This whitepaper may help.

Analysis services and traditional reporting

The idea of defining cubes directly on any relational database is very nice, especially with the possibility of giving "friendly names" to facts and dimensions.

I've read in a few places that analysis services provides "... a unified and integrated view of all your business data as the foundation for your traditional reporting, OLAP analysis and data mining."

I've tested the OLAP and the data mining aspects but I have yet to see how I can use a data source view defined in analysis services (without any cubes associated with it) to do traditional reporting.

I would like to use the "abstraction layer" that the analysis services data source view provides but in a traditional report such as a list of customers with names and addresses but no aggregations, so no cubes.

Is it possible to do "traditional reporting" through a data source view in analysis services (with its friendly names and regional attributes) without defining a cube? If so, how?

Thanks in advance.

Hi Gilles,

You could create a Report Model based on your DSV.

HTH,

Eric

|||

Thanks for the quick response.

I had noticed that a model would help and succeeded in creating a report from report services that uses the model. However, would I be able to use that same model has a data source from something else other than reporting services such as from within Excel?

What I would like to do is use the data source views in analysis services (again because of the friendly names and so forth) as my one source for all my reporting needs: OLAP, data mining AND traditional reporting, to be used by reporting services AND other reporting tools.

|||

Hi,

Report Model will help you with Reporting Services and Report Builder but I doubt you will be able to use the Report Model from Excel...

When working with Excel, I usually create a datasource to the cube itself.

Eric

Analysis Services and Reporting Services

Hello !
I am currently evaluating ability of Reporting Services (RS) to work
with output of MDX queries. RS looks very appealing when working with
relational output. But working with multidimensional output is somewhat
cumbersome. I haven't found any examples so far running against Analysis
Services.
Any thoughts\sources of information on this subject would be greatly
appreciated. We have to decide whether go with RS as UI to display output
from the cube .
Thank you in advance,
Igor.There's no real integration for AS currently in RS - just the OLAP provider
for OLE DB. This means you need to re-format the data you retrieve from cube
s and there's no query builder type implementation. What you could do is wri
te a reporting tool in .NET
to create the rdl (examples in the RS books online) and supply the MDX via a
client tool - such as a web page or desktop app. That way you can handle th
e formatting etc in the reporting tool, rather than doing every time in the
Report Designer.
HTH
Phil.|||Thanks a lot,
Igor.
"Phil Austin" <anonymous@.discussions.microsoft.com> wrote in message
news:E99A5AFD-CB71-4878-BD98-4380C528CBFC@.microsoft.com...
> There's no real integration for AS currently in RS - just the OLAP
provider for OLE DB. This means you need to re-format the data you retrieve
from cubes and there's no query builder type implementation. What you could
do is write a reporting tool in .NET to create the rdl (examples in the RS
books online) and supply the MDX via a client tool - such as a web page or
desktop app. That way you can handle the formatting etc in the reporting
tool, rather than doing every time in the Report Designer.
> HTH
> Phil.|||There are some examples in the samples folder of Reportin service to
fetch from OLAP. Try it
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I would like to suggest u to use the MS Reporting Services addin of
Panorama NovaView to have MS Analysis Services views in MS Reporting
Services with a simple wizard. On our website www.gmsbv.nl you will
find some screenshots.
Marco
"imarchenko" <imarchenko@.hotmail.com> wrote in message news:<#$1yPagBEHA.1600@.tk2msftngp13.
phx.gbl>...
> Hello !
> I am currently evaluating ability of Reporting Services (RS) to work
> with output of MDX queries. RS looks very appealing when working with
> relational output. But working with multidimensional output is somewhat
> cumbersome. I haven't found any examples so far running against Analysis
> Services.
> Any thoughts\sources of information on this subject would be greatly
> appreciated. We have to decide whether go with RS as UI to display output
> from the cube .
>
> Thank you in advance,
>
> Igor.

Analysis Services 2005 Deployment Wizard

I am working with SQL server 2005 analysis and reporting services. I am

instructed to create a cube for a database using analysis services and
then replicate it so as to produce reports online by reporting services

when requested by clients. I am able to create the cube and also deploy

the report made, in HTTP separately. But the following doubts arise
during the Cube deployment:

The Cube was created as per the requirements by my team lead. Then I
used the deployment wizard in the analysis services 2005 to convert it
into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query and executed it. What has to be done after this in
order to use it as a production server?

Once the production server is setup and the connection is made with the

staging server, How can we set the timings to when the updating of the
production server has to be set in terms of hours, days or weeks?

Hoping these doubts would be clarified as early as possible.

Take a look at the Synchronization functionality in Analysis Services. It allows you to synchronize on database to another using single command.

In your case I can imagine, you having updates done to a single master server, and then running sycnhronization commands against multiple servers telling them to synchronize the changes happend on the master.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services 2005

Hi,

For Reporting I am using reporting services using the Analysis Services Cube and i am using SSIS to process the cube.

My doubt is if the cube is processing at the same time the report is running the report is not running.

Can you please let me know what to do to runt he same process at the same time.

Thanks * Regards

Dinesh

You could always process it on another server and then sync it across if the database is not too big, otherwise backup and restore.

Matt

|||

Thanks for reply.

This is a huge database, i cannot take backup and restore.

In SSAS there is an option for doing this task is selecting Automatic MOLAP in partitions tab in the cube.

Thanks

Dinesh