Typically, cube / OLAP environments are used for exploratory analysis, in other words the person requiring information uses a tool such as the OLAP viewer in Enterprise Guide or Excel to dynamically drill around the cube looking for information. The language used to do this is MDX. If I understand your request correctly, you would like to extract data on a regular basis, using criteria such as "extract monthly summaries for dates between x and y". I would suggest the following approach: 1. Using Excel or Enterprise Guide, explore your cube to get exactly the information on the screen that you would like in your extract. 2. Capture the MDX code that retrieves that information. In EG, you "create a slice". If you examine the properties of the slice, you'll see the MDX code. 3. Create a new library for your cube, and send your MDX code to it as a "pass-through" query. That should return your desired data in the form of a SAS dataset, which can then be analyzed, reported on, distributed, etc. 4. Once all of this works, change the variable parts of your query (such as start and end dates) into parameters / macro variables, that can then be created as part of your scheduled ETL process. Tom ... View more

1. I suggest you look up the definitions on Wikipedia. The entries there are quite informative. SAS supports both with its OLAP server. 2. SAS OLAP stores are created and updated by running PROC OLAP. Therefore, anything that you can do with this procedure can be automated with scheduling software. Therefore, the answer is "yes", but it does require some work. Tom ... View more

I can give you some general comments on connecting to an ODBC source. 1. Assuming that SAS is running on your PC i) Ensure that the ODBC connection software for obiee is installed on your PC (when you create a new ODBC data source, you should see your obiee as one of the options). ii) Create a new data source for your obiee data using the Windows odbc facility. iii) In SAS, connect to your new data source using a LIBNAME statement. Documentation for this is under SAS/Access. If it works, great. If it doesn't, I suggest trying to connect to your ODBC data from another product, like Excel or Access, to see if the problem is with the ODBC or with SAS. 2. If you're running SAS on a server Same process, except that you'll have to install the ODBC data source on your server. This may involve you doing a remote desktop connection to your server, or getting your server admin. to do it. The rest of the steps are the same. Good luck! Tom ... View more

From "SAS 9.3 Stored Processes Developer's Guide" Hiding Passwords and Other Sensitive Data If you are creating a prompt for a password and want the text to be masked as the user is typing, use a text type prompt, and then select Masked single line (for password entry) as the text type. For more information, see the prompt help in SAS Management Console. Even if you decide not to use a masked prompt, the SAS log exposes programs and input parameters, which could pose a security issue. There are some actions that you can take to hide passwords and other sensitive data from the SAS log. Password values are hidden from the SAS log for any input parameters with the _PASSWORD suffix anywhere in the parameter name (for example, ABC_PASSWORD, _PASSWORDABC). You can disable the SAS log with the DebugMask Web application initialization parameter. For more information, see “Debugging in the SAS Stored Process Web Application ” on page 134. You can also use the prefix _NOLOG_ with macro variables to hide request variable values. The _NOLOG_ prefix enables you to create special macro variables that can be sent to the stored process server without publishing the macro variable values in the SAS log. The special macro variables must start with the prefix _NOLOG_. The prefix is not case sensitive. Here is an example of an input parameter with the _NOLOG_ prefix: http://yourserver/SASStoredProcess/do?_program=/WebApps/Sales/Employee+Salary&_nolog_salary=secretpw If _NOLOG_SALARY is displayed in the SAS logs, the log shows the following: _NOLOG_SALARY=XXXXXXXX;Note:The _NOLOG_ prefix and the _PASSWORD suffix are effective only if your stored process is running on a stored process server. Tom ... View more

SAS statistical procedures like PROC MEANS and PROC SUMMARY can be used for a variety of statistical operations, from very simple statistics that can easily be replicated in SQL, to sophisticated results that would be almost impossible to reproduce. I've never heard of any tools to do this kind of migration. All I can suggest is that you first examine the SAS code, to understand clearly what it is doing, and then search for the equivalent statistical operations using SQL. When you use the term MS SQL, do you mean that you want to implement these operations in the SQL language for Microsoft SQL Server, or do you intend to use more sophisticated tools like SQL Server Analysis Services? Tom ... View more

Hi, Sheeba Email me at gishpuppy.x0k@gishpuppy.com and we can discuss your requirements further offline. Tom ... View more

There are two issues here. One is what you're trying to do. I strongly recommend you post a bit of data and a description of what your desired result is, with a description of what you want to do, as I don't think your SQL code is doing what you want. Based on that, the experts on this forum can make suggestions to how you can use the SAS tools to accomplish it, which I believe will be pretty easy. Then, we can walk you through how to run your code in Enterprise Guide, which is very easy. Tom ... View more

I think that the idea behind this is that your operational systems maintain a current snapshot of the data; i.e. if you are a bank and your customer closes his account, you delete his information from your databases. In your data warehouse, by contrast, you want to keep all of your data; it might be very useful to study information about customers who close their accounts. Therefore, the general rule is that operational systems delete obsolete data in real time, while all operational data moves over to and is added to the data warehouse, which accumulates everything. You can see that the most useful data warehouse would keep all of the data, as you can't predict what you might need to analyze at a later date. That being said, however, organizations have to balance the cost of maintaining the data with the benefits they derive from them. Various strategies are used to purge data from warehouses. A common one is to delete the oldest data, based on the thinking that current data is the most useful. The warehouse then becomes a "rolling snapshot" of the company's operations. Other options exist, though. One is to summarize data; instead of keeping the full address for a customer, just retain the ZIP as being useful enough. Another is to stage the data, keeping the most useful on fast (and expensive) disk, and moving less useful data to slower and cheaper storage. There are very few hard and fast rules in BI, but there are useful principles that you can use to make cost-effective decisions. Tom ... View more

It's a little unclear how your data is set up. Adding some sample data might clarify things. If you have more than one row with the same "Application_ID" in eith t1 or t2, I don't think this approach will work. SQL is inherently difficult for logic that involves using data from more than one record. You'd probably find it much easier to use a data step. The "multiplication" of records that you mention is exactly the correct processing for SQL. Tom ... View more

Hi, Quentin Let me suggest that you give EG a try with the log incorporated in the project. I'm a longtime EG user, and personally I find it preferable to have the code, log, and results all together in the project. I agree that it's different from DM, but if you give it a try you might come to prefer it as well. And, of course, if after giving it a fair try you still prefer the old mechanism, you can implement Chris' suggestions. Either way, you win! Tom ... View more

My feeling is that your server is adequately powerful for what you're using is for. Depending on what you're running, your memory might be a bit low; keep an eye on it with PerfMon, and augment if necessary. If you run short of disk, up it as well; 450 GB isn't much for a SAS server, and disk is pretty cheap these days. Using a 2MB dataset should be absolutely trivial for a server like that. Even a 3GB dataset should be quite doable, although you may notice more of a delay. 5 users is no problem at all; keep in mind, usually they'll just be looking at the screen, and won't actually be running anything, so on a quad-core processor if there's even one SAS process per core I'd be surprised. One thing that catches my eye is "run several reports (up to 9000 reports) at a time". Is this a typo? If not, 9000 reports being submitted concurrently isn't a few!?! What kind of reports are you running, and what do you mean when you give a size for them? (40 MB, 500 MB). SAS is very efficient, but rendering reports, particularly in HTML, can be a real headache, nothing to do with SAS. I've had a lot of experience advising users about how to change their requests to get rid of large problem reports. A couple more suggestions: Doc's suggestion is 100% correct; make sure that what looks like slow response isn't just data passing over the network. Also, make sure you schedule regular use of the SAS utility to clean up work dataset space. And finally, if your Enterprise Guide users are new, they may tend to cancel their EG sessions and leave SAS programs running on the server, which can be a resource suck. You can watch this with Task Manager on the server, and if it's a problem these zombie sessions can be cancelled. Good luck, and post back to let us know what happens!   Tom ... View more