One more question regarding Reporting action

All the reporting action samples I read from AdventureWorks, when pass link and parameter to the report in reporting service, is linked to the report as Analysis Service data source based report.

I am wondering if reporting action can pass parameter to regular relational db based reporting service report not Analysis Service data source based report.

If yes, could you share a simple sample?

I can't see why the data source of a report would matter, when using a reporting action. The Adventure Works examples may all be using an Analysis Services data source just for convenience. Have you tried editing an existing Adventure Works reporting action, to point to a relational report instead?|||

Hi Deepak, thank you so much for your reply! Yes, I did try the following using AW AS and AW sample reports RS projects:

Here is my reporting action:

Name: Sales Order Detail

Target Type: Dimension members

Target object: Internet Sales Order Detail

ReportServer:localhost

ReportPath:ReportServer?/AdventureWorks Sample Reports/Sales Order Detail

Parameters NamealesOrderNumber (Match the one at Sales Order Detail report

Parameter Value: [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Name

And I make sure Sales Order Detail Report is running correctly and parameter is matching the parameter name I defined at the cube reporting action. Then I deploy the cube, however when drop the Sale Order Number dimension and right click, there is no Reporting Action showing up.

Could you give me some pointers?

Thanks

|||

Did you configure the "Caption", under "Additional Properties" - if not, you can refer to the Adventure Works "Sales Reason Comparisons" action, whose Caption is defined as an MDX expression:

"Sales Reason Comparisons for " +

[Product].[Category].CurrentMember.Member_Caption + "..."

|||

Thanks again. Just did that and deployed.

"Sales Reason Comparisons for " + [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Member_Caption + "..."

However reporting action is still not showing. Deepak, anything else I miss?

Thanks

|||Can't think of anything else - are you able to see the standard Reporting action which comes with Adventure Works, ie. "Sales Reason Comparisons"? If so, I would suggest making a test copy of Adventure Works, and progressively modifying this action to incorporate your report in small steps, till it breaks - or maybe it doesn't break!

One database item per page Reporting Services

I'm creating Reporting Services reports in Visual Studio. I create a new
report, and see the Data, Layout, and Preview tabs.
In the data tab, I say: "select name from mytable". Four names are returned.
I'd like to display one name per page.
I posted this question a few days ago. The folks that responded (thanks)
suggested that in the Layout tab, I could place a table or list on the form,
then drag the "name" from DataSet1 onto the table or list. The problem with
this approach is that all 4 names are grouped on the first page. I'l like to
see one name per page.
In reality, mytable contains dozens of fields which will go on the report
form. I want Reporting Services to print out a one page report for Mr. Able,
Mr. Baker. Mr. Charles, and so on.
--
RandyI think the best way to handle this is with the List control.
In the layout tab drag the list from the toolbox.
You can then drag the name (and any other field) into the list control.
You can then right click on the List control and select properties.
In the General tab, click "Edit details group..."
In the top line of the expression list, choose the name field from the drop
down.
Check the "Page break at end" checkbox.
Each instance of the list will now show up on it's own page. You can drag
whatever fields you want into the list box.
Hope this helps.
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Hi,
Hey Its simple. Suppose "select name from mytable" is your query.
After dragging table and placing fields as per your wish. Create a group
with "name" field, so whenever name changes it goes to the next page (dont
forget to click on "page break at end".
Amarnath
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Got it! Many thanks...
--
Randy
"Amarnath" wrote:
> Hi,
> Hey Its simple. Suppose "select name from mytable" is your query.
> After dragging table and placing fields as per your wish. Create a group
> with "name" field, so whenever name changes it goes to the next page (dont
> forget to click on "page break at end".
> Amarnath
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy|||That's exactly what I needed. Many thanks...
--
Randy
"Limey" wrote:
> I think the best way to handle this is with the List control.
> In the layout tab drag the list from the toolbox.
> You can then drag the name (and any other field) into the list control.
> You can then right click on the List control and select properties.
> In the General tab, click "Edit details group..."
> In the top line of the expression list, choose the name field from the drop
> down.
> Check the "Page break at end" checkbox.
> Each instance of the list will now show up on it's own page. You can drag
> whatever fields you want into the list box.
> Hope this helps.
>
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy

on reporting services 2005(to dsiplay zeroes if data is not there in database)plss helppp

I am working on reporting services 2005 and i need to display zeroes in the report preview for the data which is not there in database,So pls help me if u can

Use the IsNull() or COALESCE functions in your sql. Check the Books Online (BOL) for instructions on how to use them.

On different server?

HI All,
Currently we have two servers in our poduction farm. One runs
sharepoint and the other is the database server. Can i install
reporting services o nthe sharepoint server and tell it point to the
database server? Right now I have it installed the db server, but
would like to move it to the other server.
Any ideas
Thanks!
-SJOn Jul 5, 9:25 am, SJ <sjour...@.gmail.com> wrote:
> HI All,
> Currently we have two servers in our poduction farm. One runs
> sharepoint and the other is the database server. Can i install
> reporting services o nthe sharepoint server and tell it point to the
> database server? Right now I have it installed the db server, but
> would like to move it to the other server.
> Any ideas
> Thanks!
> -SJ
SJ,
Yes, you can install it on the sharepoint server and point it to the
DB server. During installation it will ask you the location of the DB
server.|||Yes, you can install Reporting Services on a computer without SQL Server
installed (as long as it has access to a SQL Server to store its meta data).
However, you need a SQL Server license for the computer where Reporting
Service runs.
"SJ" <sjourdan@.gmail.com> wrote in message
news:1183652738.671128.102340@.n60g2000hse.googlegroups.com...
> HI All,
> Currently we have two servers in our poduction farm. One runs
> sharepoint and the other is the database server. Can i install
> reporting services o nthe sharepoint server and tell it point to the
> database server? Right now I have it installed the db server, but
> would like to move it to the other server.
> Any ideas
> Thanks!
> -SJ
>|||On Jul 5, 1:38 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> Yes, you can install Reporting Services on a computer without SQL Server
> installed (as long as it has access to a SQL Server to store its meta data).
> However, you need a SQL Server license for the computer where Reporting
> Service runs.
> "SJ" <sjour...@.gmail.com> wrote in message
> news:1183652738.671128.102340@.n60g2000hse.googlegroups.com...
>
> > HI All,
> > Currently we have two servers in our poduction farm. One runs
> > sharepoint and the other is the database server. Can i install
> > reporting services o nthe sharepoint server and tell it point to the
> > database server? Right now I have it installed the db server, but
> > would like to move it to the other server.
> > Any ideas
> > Thanks!
> > -SJ- Hide quoted text -
> - Show quoted text -
Thank you both for the reply's I reall appreciate it!
-SJ

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?bump
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?HTML Viewer Commands (rc:)
You use HTML Viewer commands to tell SSRS 2005 how to render the report. You
can use the commands to control how the viewer appears to the user, as well
as to control certain aspects of how the report appears in the viewer.
-- Shows or hides the report document map.
rc:DocMap=true
Reeves
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lie
Depends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie
|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lie
if you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie
|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so you
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use the
> same database for RS + OLAP. but, generally, this database in not real-time
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 100
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:
|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new features
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
sql

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert LieDepends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert LieDepends on the oad on the reporting server. I would install it on th same
machine with gernerating the cubes in a time where Reports are not often
queried. If the workloads gonna to heavy you even have the possibility to
scale it out.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
>
Whether you use 2 dbs for reporting or one depends on the reporting needs...
The one database for Reporting Purposes , when it is independent like you
have it might be called an ODS (Operational Data Store) . ODS look very much
like their OLTP counterparts, offload OLTP reporting from the OLTP database,
and are a location to aggregate OLTP information from across the many
locations in the enterprise to a single global report location. The data in
these databases is generally for a shorter specific period ( like quarterly
for instance.)
The OLAP database is intended for aggregated reporting, across longer
historical periods. The schema will have been changed to reflect OLAP
sensibilities as well..
hope this helps.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ulBjQ7gSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lieif you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so y
ou
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use t
he
> same database for RS + OLAP. but, generally, this database in not real-tim
e
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 10
0
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
>|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new featur
es
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
>

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.

OLEDB versus ODBC

HI,

I'm using a reporting tool for retrieving information from a sqlsrv 2005 env. using OLE DB for my connection.
When i've created a metric and whilst refreshing it i get the message 'object was open'. Changing the connection into ODBC everything went well.

By looking at the log file it's trying to write the metric record in the DB but it simply cannot (OLEDB) but using ODBC is all went fine...

Is something not properly installed on the server side?

anyone?

E10

Are you using a 3rd-party reporting tools? It looks like they do not use OleDb in a proper way. Better check with the reporting tools' vendor.

OLE DB2 driver for Reporting Services

Question: When will the OLEDB driver be available for Reporting Services? In the Marketing Material I noted that Reporting Services can indeed connect to AS/400 through OLEDB. I have tried with all supplied OLEDB drivers and get only errors. The only drivers which work are ODBC and they are dog-slow. I have reviewed most posts on OLEDB drivers for AS/400 or DB2 and most everything points to SSIS. I want to be able to connect to the AS/400 through Reporting Services (SSRS) and report off it via an OLEDB driver instead of ODBC as I am doing it right now. Are there ANY implementable alternatives out there anybody?According to the SQL Server Feature Pack, the Microsoft OLEDB for DB2 driver can be used with Reporting Services.

http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en