|
|
You're in the
sql server data warehouse
group:SAS Datawarehouse..?
sql server data warehouse:
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 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 to it's more mainstream position in this area, it's ease of use and availability of skills Thanks P [quoted text, click to view] "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. > > >
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 [quoted text, click to view] "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 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 > > 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. [quoted text, click to view] "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 > 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. > > > > > > > >
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... [quoted text, click to view] > 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: > >> 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 >> > 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. >> > > >> > > >> > > >> > > >> >> >>
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... [quoted text, click to view] > 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... >> 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: >> >>> 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. >>> >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |