placement of Analysis service? on cluster or lone server?

Hi, we have an active/passive cluster which hosts our production database.
Transactional replication is being made from the production to a 'reporting'
database residing on a lone server. We will need to install Analysis service
for the construction of cubes.
I was wondering where Analysis service should be installed?
Is is ok to install it on the lone server and have it use the data from the
reporting database (which is filled via transactional replication)? Will
Analysis service have a problem with this based on the fact that it is
reading a read only database which doesnt have primary keys in the tables?
OR, should analysis service be installed on the cluster? Is it cluster
aware?
At this stage we are thinking of only running the cubes at night to generate
the data for export to reporting service.
Would it then be a good idea to install reporting service on this same lone
server?
Any insight would be most appreciated!
Cheers, john
If that is the case, you can't possibly be running transactional
replication.
The "reporting" database is LOGICALLY read-only, not physically read-only.
This database also MUST have all of the primary keys in place on the tables,
since it is impossible to replicate a table with transactional replication
if the table does not have a primary key. This primary key exists at both
the publisher and the subscriber.
Analysis Services is not going to write to your tables when building cubes,
so it does not care if the tables are physically read-only, logically
read-only, or read-write.
Where you place AS is entirely up to you. You can install it on the
stand-alone server and you can install it in the cluster. The question you
need to ask is what level of availability do you need for AS. If it needs
to be available as much as possible, even through a hardware failure, then
it should be installed in the cluster. If not, it should be installed on
the stand-alone machine.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23QyI71JDGHA.688@.TK2MSFTNGP11.phx.gbl...
> Hi, we have an active/passive cluster which hosts our production database.
> Transactional replication is being made from the production to a
> 'reporting'
> database residing on a lone server. We will need to install Analysis
> service
> for the construction of cubes.
> I was wondering where Analysis service should be installed?
> Is is ok to install it on the lone server and have it use the data from
> the
> reporting database (which is filled via transactional replication)? Will
> Analysis service have a problem with this based on the fact that it is
> reading a read only database which doesnt have primary keys in the tables?
> OR, should analysis service be installed on the cluster? Is it cluster
> aware?
> At this stage we are thinking of only running the cubes at night to
> generate
> the data for export to reporting service.
> Would it then be a good idea to install reporting service on this same
> lone
> server?
> Any insight would be most appreciated!
> Cheers, john
>
>

PL/SQL versus stored procedures

I want to know what's the differences between PL/SQL and stored
procedures,
the followings are my analysis, please comment and advise.
1) PL/SQL is Oracle specific, stored procedures are supported in
Oracle, MS-SQL Server, or other databases.
2) PL/SQL has 2 types: procedures and functions
3) PL/SQL procedure = stored procedure '
4) Oracle stored procedure and MS-SQL stored procedure have
different syntax. I think they are slightly different, I could
find the syntax for Oracle stored procedure, but not MS-SQL stored
procedure. In other words, can we put MS-SQL stored procedure
and put in Oracle without any changes?
Please advise. thanks!!On 13 Oct 2005 22:15:44 -0700, apngss@.yahoo.com wrote:

>I want to know what's the differences between PL/SQL and stored
>procedures,
>the followings are my analysis, please comment and advise.
>1) PL/SQL is Oracle specific, stored procedures are supported in
>Oracle, MS-SQL Server, or other databases.
>2) PL/SQL has 2 types: procedures and functions
>3) PL/SQL procedure = stored procedure '
>4) Oracle stored procedure and MS-SQL stored procedure have
>different syntax. I think they are slightly different, I could
>find the syntax for Oracle stored procedure, but not MS-SQL stored
>procedure. In other words, can we put MS-SQL stored procedure
>and put in Oracle without any changes?
>Please advise. thanks!!
1 PL/SQL is Oracle's language to implement stored procedures, T-SQL is
MS language to implement stored procedures. T-SQL is only supported in
MS and maybe Sybase. 'Stored procedure' is a *concept*, not a
*language*
2 Like any proper procedural language
3 PL/SQL can be used for anonymous blocks : ie code which is not
stored, and for procedures/functions/packages which are stored
4 the syntax for T-SQL and Pl/SQL is akin, but dissimilar.
T-SQL procedures won't work in Oracle and vice versa, moreover Oracle
works completely different, and too many people mistakenly think
Oracle is sqlserver sold by a different vendor. It is not, it works
completely different.
Sybrand Bakker, Senior Oracle DBA|||PL/SQL is to Oracle as T-SQL is to SQL Server. Both have their programming
constructs and data structures. You can "implement" stored procedures using
PL/SQL much like you can do the same with T-SQL.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<apngss@.yahoo.com> wrote in message
news:1129266944.512419.283400@.o13g2000cwo.googlegroups.com...
>I want to know what's the differences between PL/SQL and stored
> procedures,
> the followings are my analysis, please comment and advise.
> 1) PL/SQL is Oracle specific, stored procedures are supported in
> Oracle, MS-SQL Server, or other databases.
> 2) PL/SQL has 2 types: procedures and functions
> 3) PL/SQL procedure = stored procedure '
> 4) Oracle stored procedure and MS-SQL stored procedure have
> different syntax. I think they are slightly different, I could
> find the syntax for Oracle stored procedure, but not MS-SQL stored
> procedure. In other words, can we put MS-SQL stored procedure
> and put in Oracle without any changes?
> Please advise. thanks!!
>|||apngss@.yahoo.com wrote:
> I want to know what's the differences between PL/SQL and stored
> procedures,
> the followings are my analysis, please comment and advise.
> 1) PL/SQL is Oracle specific, stored procedures are supported in
> Oracle, MS-SQL Server, or other databases.
> 2) PL/SQL has 2 types: procedures and functions
> 3) PL/SQL procedure = stored procedure '
> 4) Oracle stored procedure and MS-SQL stored procedure have
> different syntax. I think they are slightly different, I could
> find the syntax for Oracle stored procedure, but not MS-SQL stored
> procedure. In other words, can we put MS-SQL stored procedure
> and put in Oracle without any changes?
PL/SQL = name of a Oracle's embedded programming language (based on
another programming language called ADA). PL/SQL is also very similar
in syntax and structure to Pascal.
PL/SQL programs can be :
- procedures
- functions
- packages (program units containing both procedures and packages,
similar to a Pascal unit)
- anonymous code block
As procedures, functions and packages are stored (in source code and
pre-compiled format) inside the database, these are often collectively
refered to as "stored procedures".
Anonymous blocks are PL/SQL code blocks constructed by the client and
transmitted to Oracle. Oracle parses and compiles these and then
execute them. These are obviously not stored in the database and
therefore not considers as "stored procedures".
Other databases implements their own embedded programming languages.
SQL-Server's is called Transact-SQL (or simply T-SQL).
Why not simple use SQL? SQL is not Turing Complete and despite its
power and flexibility, lacks at doing complex structural processing.
Thus most databases implement an embedded programming language, which
they tightly integrate with their SQL engine - providing seamless SQL
access from within this embedded language. Note that not all databases
have embedded programming language - some only recently started doing
this (like mySQL).
Embedded programming language look similar. So yes, there are
similarities between T-SQL and PL/SQL. But these are very superficial.
PL/SQL is a formal declarative procedural language with
object-orientated features. It is capable of doing what you can do in
other languages, like Java, C/C++, Delphi/Pascal, Visual Basic, etc.
(besides, Oracle and SQL-Sever work conceptually very different and
what is "good practise" in one database, is a performance killer in the
other)
There are limitations in PL/SQL however. It is a server-side language.
It thus lacks I/O devices such as screen, keyboard, mouse, printer and
so on. It is not an interactive language (it cannot interact with the
end-user). As it is embedded in a database it cannot natively access
the operating system kernel API. Etc.
However, these "limitations" are common to embedded languages. The
advantage is that PL/SQL can be run on any Oracle database on any
platform. Thus you can develop a PL/SQL application on Windows in
Oracle and have a customer use your application in their Oracle
database on an IBM mainframe. Similar to Java, it is fully portable
across Oracle platforms. Unlike Java, it is not an issue of
write-once-and-debug-everywhere as the PL/SQL engine is consistant.
Oracle's Replicator is written in PL/SQL. Oracle Applications (a
commercial product suite) consists of over 1 million lines of PL/SQL
source code. I myself, has written numerous server-side systems
(including a custom replicator and web applicatiom tiers) in PL/SQL. It
is a very capable language - and, as a statement of fact, the *best*
programming language to use when dealing with Oracle data.
Billy|||Good Explanation Billy
Madhivanan|||That was a very good explanation. Thank you.
Ivy

Pivot table very slow

We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
We have 3 facts tables (less than 100 rows each) ,
8 dimensions (from 10 to 1000 members),
3 regular cubes and 1 virtual cube (with 20 calculated members).
The pivot table is produced from the virtual cube.
In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
it's very very slow (2-3 minutes).
When I remove 1 dimension as row field, it's fast (5 sec).
Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
it's fast too !
I have tried different storage design or adding "default isolation mode=1" to Excel
connection string but it's still very slow.
Your help/suggestions will be very appreciated.
Thanks.
Create aggregation based on those two dimensions, or create an extra
dimension (dimension1 -> dimension 2), see if they help
Eric Li
SQL DBA
MCDBA
T.Huynh wrote:

> We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
> We have 3 facts tables (less than 100 rows each) ,
> 8 dimensions (from 10 to 1000 members),
> 3 regular cubes and 1 virtual cube (with 20 calculated members).
> The pivot table is produced from the virtual cube.
> In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
> it's very very slow (2-3 minutes).
> When I remove 1 dimension as row field, it's fast (5 sec).
> Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
> it's fast too !
> I have tried different storage design or adding "default isolation mode=1" to Excel
> connection string but it's still very slow.
> Your help/suggestions will be very appreciated.
> Thanks.
>
|||How do you create an extra
dimension (dimension1 -> dimension 2) in Analysis Services ?
I have dim 1 (20 members) and dim 2 (1000 members).
Thanks for your advice.
|||Take a look to SQL Server Accelerator for BI. it includes an utility to
create your own Aggregations
ALEJANDRO LEGUIZAMO
MVP SQL - Colombia
"T Huynh" <anonymous@.discussions.microsoft.com> wrote in message
news:3D6120F1-228E-4E52-8BBA-E9F548970030@.microsoft.com...
> How do you create an extra
> dimension (dimension1 -> dimension 2) in Analysis Services ?
> I have dim 1 (20 members) and dim 2 (1000 members).
> Thanks for your advice.

Pivot table very slow

We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
We have 3 facts tables (less than 100 rows each) ,
8 dimensions (from 10 to 1000 members),
3 regular cubes and 1 virtual cube (with 20 calculated members).
The pivot table is produced from the virtual cube.
In Excel, when I put 2 dimensions side by side as row fields with all the ca
lculated members,
it's very very slow (2-3 minutes).
When I remove 1 dimension as row field, it's fast (5 sec).
Or when I replaced all calculated members by regular measures (with 2 dimens
ions as row fields),
it's fast too !
I have tried different storage design or adding "default isolation mode=1" t
o Excel
connection string but it's still very slow.
Your help/suggestions will be very appreciated.
Thanks.Create aggregation based on those two dimensions, or create an extra
dimension (dimension1 -> dimension 2), see if they help
Eric Li
SQL DBA
MCDBA
T.Huynh wrote:

> We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
> We have 3 facts tables (less than 100 rows each) ,
> 8 dimensions (from 10 to 1000 members),
> 3 regular cubes and 1 virtual cube (with 20 calculated members).
> The pivot table is produced from the virtual cube.
> In Excel, when I put 2 dimensions side by side as row fields with all the
calculated members,
> it's very very slow (2-3 minutes).
> When I remove 1 dimension as row field, it's fast (5 sec).
> Or when I replaced all calculated members by regular measures (with 2 dime
nsions as row fields),
> it's fast too !
> I have tried different storage design or adding "default isolation mode=1"
to Excel
> connection string but it's still very slow.
> Your help/suggestions will be very appreciated.
> Thanks.
>|||How do you create an extra
dimension (dimension1 -> dimension 2) in Analysis Services ?
I have dim 1 (20 members) and dim 2 (1000 members).
Thanks for your advice.|||Take a look to SQL Server Accelerator for BI. it includes an utility to
create your own Aggregations
ALEJANDRO LEGUIZAMO
MVP SQL - Colombia
"T Huynh" <anonymous@.discussions.microsoft.com> wrote in message
news:3D6120F1-228E-4E52-8BBA-E9F548970030@.microsoft.com...
> How do you create an extra
> dimension (dimension1 -> dimension 2) in Analysis Services ?
> I have dim 1 (20 members) and dim 2 (1000 members).
> Thanks for your advice.