SAS Datawarehouse..?

Hi all,
Anyone with experience with SAS-datawarehouse.
Good or bad? Why?
They have a lot of front end tools.
/Kent J.Hello
The main difference between SAS and other mainstream DW environments is that
SAS is not a relational database. You can use SQL with it but it has it's ow
n
very efficient language for manipulating data. It is very fast and good at
handling large data volumes.
If you are used to relational databases it's not an intuitive environment
but is comparable with SQLServer when you get used to it and the latest
version brings a lot of functionality in the ETL and system management areas
that you take for granted in SQLServer.
The environment it based on procedures and there are procedures for just
about everything from reporting and data mining to statistics and data
manipulation, etc. Part of the learning curve is becoming familiar with
procedures and how to use them. The various front ends such as the Admin and
data mining modules use the procedures behind the scenes
Licensing is done in modules and it can be very expensive.
So it has it's advnatages and disadvantages compared with SQLServer. We
currently run a Terabyte size DW in SAS and I think it would be very hard to
port it to SQLServer without a lot of investment in very powerful servers.
Having said that for smaller scale DWs I think SQLServer would be better due
to it's more mainstream position in this area, it's ease of use and
availability of skills
Thanks
P
"Kent Johnson" wrote:

> Hi all,
> Anyone with experience with SAS-datawarehouse.
> Good or bad? Why?
> They have a lot of front end tools.
> /Kent J.
>
>|||Thanks for your reply!
We have SAS, SAP BW and SQL-server but we are now looking to use only SAS.
I'm running the SQL database with aprox 200 GB and we are using Cognos
PowerPlay web as an frontend.
We have also two ASP application connected to the database with ADO.
Some users uses MS-Access to query the database with linked tables via ODBC.
What applications can be converted to SAS or can we just use SAS
applications?
Is there a ODBC driver for SAS?
<...SAS is not a relational database>'!
How can you work with different tables?
Other issues to consider, except the price?
If we descide to focus on SAS how different is the SQL-server syntax
compared with SAS?
Can you give an example?
/Kent J.

"P" <P@.discussions.microsoft.com> wrote in message
news:09BFDA51-9183-4FBB-9370-8A471C0AD436@.microsoft.com...
> Hello
> The main difference between SAS and other mainstream DW environments is
that
> SAS is not a relational database. You can use SQL with it but it has it's
own
> very efficient language for manipulating data. It is very fast and good at
> handling large data volumes.
> If you are used to relational databases it's not an intuitive environment
> but is comparable with SQLServer when you get used to it and the latest
> version brings a lot of functionality in the ETL and system management
areas
> that you take for granted in SQLServer.
> The environment it based on procedures and there are procedures for just
> about everything from reporting and data mining to statistics and data
> manipulation, etc. Part of the learning curve is becoming familiar with
> procedures and how to use them. The various front ends such as the Admin
and
> data mining modules use the procedures behind the scenes
> Licensing is done in modules and it can be very expensive.
> So it has it's advnatages and disadvantages compared with SQLServer. We
> currently run a Terabyte size DW in SAS and I think it would be very hard
to
> port it to SQLServer without a lot of investment in very powerful servers.
> Having said that for smaller scale DWs I think SQLServer would be better
due[vbcol=seagreen]
> to it's more mainstream position in this area, it's ease of use and
> availability of skills
> Thanks
> P
> "Kent Johnson" wrote:
>|||Hi Kent
Interesting that you have SAS, SAP BW and SQL-Server. We too have all these
and are considering which way to go stragically but we are currently using
SAS mainly.
To answer your questions:
You could just use SAS or convert applications to SAS. SAS database can be
front ended by VB (and other MS tools I guess). It also has its own GUI for
building applications but we don't use it and not sure if it is widely used.
There is an ODBC for SAS.
SAS is a database but it's not relational in that there isn't an
intermediate 'table layer' in between you and the files in the backgorund.
You work on the files directly rather than on tables with files behind them
at a lower level.
In effect you can still treat the files as tables so you have all the
functionality of an RDB (except for the GUI type things that you have with
SQLServer).
SQL Server syntax is slightly different e.g. you can't do an UPDATE ... FROM
SQL is also less efficient than SAS' own language so you wouldn't use it in
a lot of circumstances, particularly for ETL processing of large amounts of
data.
Thinking strategically about what we will do between SQLServer and SAS: for
the large volume, number crunching applications that we have I think these
are best run in SAS (we currently extract most of our data from SAP using a
SAS tool called ACCESS to R/3). Although these are held in the data warehous
e
they are effectively applications in themselves.
For our 'smaller' data warehouse applications where we want to get data out
to our users quickly and in a friendly way we may move to SQLServer.
Gotta go now but if you have any more questions or tips for me I'd be glad
to hear them
Thanks
Pete
"Kent Johnson" wrote:

> Thanks for your reply!
> We have SAS, SAP BW and SQL-server but we are now looking to use only SAS.
> I'm running the SQL database with aprox 200 GB and we are using Cognos
> PowerPlay web as an frontend.
> We have also two ASP application connected to the database with ADO.
> Some users uses MS-Access to query the database with linked tables via ODB
C.
> What applications can be converted to SAS or can we just use SAS
> applications?
> Is there a ODBC driver for SAS?
> <...SAS is not a relational database>'!
> How can you work with different tables?
> Other issues to consider, except the price?
> If we descide to focus on SAS how different is the SQL-server syntax
> compared with SAS?
> Can you give an example?
> /Kent J.
>
>
>
>
>
>
> "P" <P@.discussions.microsoft.com> wrote in message
> news:09BFDA51-9183-4FBB-9370-8A471C0AD436@.microsoft.com...
> that
> own
> areas
> and
> to
> due
>
>|||Hi,
I'm working with SAS as a DW + MDDB and SQL Server + AS.
SQL server ETL is really poor and you will not have a lot func and
performance on the same config. However, maybe SQL Server is cheaper ... TO
do the same that I'm having in SAS, I'll buy 4 servers to split my DW under
SQL, where I'm woking with a 4CPU under Ux.
In term of cubes, if you are using SAS8, it's ok but too much propertary
format but manageable. With SAS9, it uses MDX the same query language as AS.
but today did not succeed to access SAS Cubes thrue a .net webapp. (with SAS
OLE DB for OLAP provider). Unfortunately, SAS started to implemenet MDX only
since 1 tear and you dn't have yet all the func available in thier cubes.
Globally, SAS is a good ETL and can be easyly interfaced with SS2K via a SAS
module ($$$ ;) ) calles "SAS/Access to SQL server".I manage an entreprise DW
about 700 Gb under Unix, with only SAS Table and SAS cubes. (Have a
relational system is not mandatory for BI, have a strong DSS engine is
better).
In term of cubes, the advantage is for MS, because SAS doesn't cover
eveything today (but cover regular MD navigation , but no write back +
someMDX functions)
In ter of front end MS has partner ship with other comp.SAS is providing
free front end (Visual Data Explorer).
Both are providing API : .net & com for MS / Java for SAS + com + IOM (for
integration into IT System).
_______________________
For tech questions, SAS supports regular SQL by proc SQL.
Look after the data step wich is very efficient to manage , clean ,
transform (transpose / format ...) datas
The best to see, is to have a training on SQL Server and Base SAS. The
criteria : how each system is managing null values on your keys, because in
the real life, even your system are strong, you'll get some case where you
cannot control everything.
Hope it helps
"P" <P@.discussions.microsoft.com> a crit dans le message de news:
05442E5A-4385-4426-816E-4F876EC4587B@.microsoft.com...[vbcol=seagreen]
> Hi Kent
> Interesting that you have SAS, SAP BW and SQL-Server. We too have all
> these
> and are considering which way to go stragically but we are currently using
> SAS mainly.
> To answer your questions:
> You could just use SAS or convert applications to SAS. SAS database can be
> front ended by VB (and other MS tools I guess). It also has its own GUI
> for
> building applications but we don't use it and not sure if it is widely
> used.
> There is an ODBC for SAS.
> SAS is a database but it's not relational in that there isn't an
> intermediate 'table layer' in between you and the files in the backgorund.
> You work on the files directly rather than on tables with files behind
> them
> at a lower level.
> In effect you can still treat the files as tables so you have all the
> functionality of an RDB (except for the GUI type things that you have with
> SQLServer).
> SQL Server syntax is slightly different e.g. you can't do an UPDATE ...
> FROM
> SQL is also less efficient than SAS' own language so you wouldn't use it
> in
> a lot of circumstances, particularly for ETL processing of large amounts
> of
> data.
> Thinking strategically about what we will do between SQLServer and SAS:
> for
> the large volume, number crunching applications that we have I think these
> are best run in SAS (we currently extract most of our data from SAP using
> a
> SAS tool called ACCESS to R/3). Although these are held in the data
> warehouse
> they are effectively applications in themselves.
> For our 'smaller' data warehouse applications where we want to get data
> out
> to our users quickly and in a friendly way we may move to SQLServer.
> Gotta go now but if you have any more questions or tips for me I'd be glad
> to hear them
> Thanks
> Pete
>
> "Kent Johnson" wrote:
>|||Just to complete,
I'm quoting a thread above (Re: Analysis Services Cube Editor)about SQL + AS
null values management =
________________________________________
____________________________________
__
The most flexible way to do this is to create a view on the relational
database-side.
Analysis Services loads from a view as well as it can from a base table.
Remember however, that you should explicitly cast NULLs to specific values
from the outer join.
Analysis Services treats NULLs as 0's (in measures) and as unknown values
(as dimension keys).
In SQL Server 2005, we have expanded the semantics for handling NULLs, but
for now in SQL Server 2000, we strongly recommend that you specifically cast
NULLs to some pre-defined value prior to exposing the data to Analysis
Services. You will just be a lot happier if you do this.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
________________________________________
____________________________________
__
;) the null management !
"Jean Gillet" <j.gillet@.voila.fr> a crit dans le message de news:
41acd704$0$17704$afc38c87@.news.easynet.fr...
> Hi,
> I'm working with SAS as a DW + MDDB and SQL Server + AS.
> SQL server ETL is really poor and you will not have a lot func and
> performance on the same config. However, maybe SQL Server is cheaper ...
> TO do the same that I'm having in SAS, I'll buy 4 servers to split my DW
> under SQL, where I'm woking with a 4CPU under Ux.
> In term of cubes, if you are using SAS8, it's ok but too much propertary
> format but manageable. With SAS9, it uses MDX the same query language as
> AS. but today did not succeed to access SAS Cubes thrue a .net webapp.
> (with SAS OLE DB for OLAP provider). Unfortunately, SAS started to
> implemenet MDX only since 1 tear and you dn't have yet all the func
> available in thier cubes.
> Globally, SAS is a good ETL and can be easyly interfaced with SS2K via a
> SAS module ($$$ ;) ) calles "SAS/Access to SQL server".I manage an
> entreprise DW about 700 Gb under Unix, with only SAS Table and SAS cubes.
> (Have a relational system is not mandatory for BI, have a strong DSS
> engine is better).
> In term of cubes, the advantage is for MS, because SAS doesn't cover
> eveything today (but cover regular MD navigation , but no write back +
> someMDX functions)
> In ter of front end MS has partner ship with other comp.SAS is providing
> free front end (Visual Data Explorer).
> Both are providing API : .net & com for MS / Java for SAS + com + IOM (for
> integration into IT System).
>
> _______________________
> For tech questions, SAS supports regular SQL by proc SQL.
> Look after the data step wich is very efficient to manage , clean ,
> transform (transpose / format ...) datas
> The best to see, is to have a training on SQL Server and Base SAS. The
> criteria : how each system is managing null values on your keys, because
> in the real life, even your system are strong, you'll get some case where
> you cannot control everything.
> Hope it helps
>
>
>
> "P" <P@.discussions.microsoft.com> a crit dans le message de news:
> 05442E5A-4385-4426-816E-4F876EC4587B@.microsoft.com...
>