Collecting ARM logs can be extremely useful when tuning OLAP cubes for faster response. The ARM log (which stands for Application Response Measurement) collects vital usage information for OLAP cubes using the SAS OLAP Server logging facility. SAS OLAP Cube studio can analyze these logs to assist with aggregation building to make OLAP cubes return faster results. Angela Hall discusses this and other ways to tune OLAP cubes in this post. SAS Support has excellent information on tuning OLAP cubes using ARM logs as well. Enable ARM Logging Interactively You can start collecting ARM logging by setting a value in SAS Management Console. By default the log is placed in the following location on the server: [SAS CONFIG]/Lev1/SASApp/OLAPServer/Logs Enable ARM Logging Automatically When developing OLAP cubes I use ARM logging to build key aggregations I find important during testing or to ensure the initial performance is acceptable for end users. Since a development environment can go up and down (especially on my own laptop ) I find myself continually going into SAS Management Console to enable ARM logging. Rather than going through this manual approach every time the OLAP server is restarted, I recommend enabling ARM logging by default in […]
Tag: SAS OLAP Cubes
SAS OLAP Cubes: Viewing Member Properties in Excel and Enterprise Guide
As a follow-up to my earlier post on taking advantage of OLAP member properties, you can also display OLAP member properties through the Add-in for Microsoft Office as well as SAS Enterprise Guide. I’m a huge fan of Enterprise Guide, so it’s nice to have that ability but even nicer when the more common information consumer can display member properties through a Pivot Table in Excel. One Click to More Information Simply right-click the level and select Show Properties in Report. From there you can check the properties you have defined. I like the way the Pivot Table displays member properties as each column in Excel. This information gives the user a little more insight into the data. Also Works in SAS Enterprise Guide To display member properties through SAS Enterprise Guide the approach is similar, right-click the level and select Show Member Property. From there you can check the member properties you want to display. More about OLAP Member Properties Check out my earlier post to see how these same OLAP member properties are defined and displayed in SAS Web Report Studio. Related content: SAS OLAP Cubes: Taking Advantage of OLAP Member Properties SAS Business Intelligence Tools Overview SAS […]
SAS OLAP Cubes: Taking Advantage of OLAP Member Properties
Have you ever had a requirement to display additional details about data in a report but couldn’t find a good way to do so? Showing OLAP Member Properties in a SAS Web Report could be a useful way to sneak more details about data into a crosstab table. Crossing Multiple Dimensions with High Cardinality Creates Data Headaches I recently ran into a data challenge with a large OLAP cube (NWAY of about 12 million records) where I needed to summarize a simple count by a “group ID” crossed with a “group name” crossed with another insignificant dimension. There were a large number of “groups” – over a 1000 distinct group IDs with matching group names. Since the cardinality between the “group ID” and “group name” with the source data was pretty high, the OLAP query took a very long time to run and ultimately failed due to a lack of memory. Rather than figuring out how to cross two dimensions with high cardinality (which is not going to be realistic), I used the “group name” to define a member property of the “group ID”. Since the group name had a one-to-one relationship with the group ID, the member property makes sense. […]
OLAP Cube Studio: Looking at PROC OLAP Code
If you have never worked with OLAP code – then you might be curious how it works and what it looks like. Some developers use SAS OLAP Cube Studio to start their cube building process since it saves some of the typing required. Let’s review some of the code. [Adv Users: check out Steve’s posts about OLAP Code] Exporting PROC OLAP Code There are two methods to export your cube to a SAS program from SAS OLAP Cube Studio. Use Method 1 after you have added custom measurements. Click cube name > Export Code. The Export Code window appears. Use Method 2 after you have completed the Cube Designer wizard. Click the Export Code button. The Export Code window appears. From the Export Code window you can select if you want standard (long) OLAP procedure code or the shorten code. Either way – just add the path and program name. [More at SAS Support about when to use short/long form. ] Under the Hood After opening the BUILD_GA_BLOGSITE.sas program in Enterprise Guide, you see how the OLAP procedure code appears. You can learn to code your own cubes, just by reviewing how the OLAP wizard wrote the code. For instance, […]
Excel and OLAP Viewer: Not enough room for the Cube!
When you use the OLAP Viewer in Microsoft Excel you may have run into the window borders – literally. Your cube and analysis needs more space! Scroll, Scroll, and Scroll to See the Cube Data When you open the OLAP cube in the MS Excel OLAP Viewer the default cube size dictates the area used. You may get 20 rows by 20 columns or you might get 2 rows by 2 columns. So when you want to do some analysis, you expand a dimension and scroll, scroll this way and then scroll, scroll that way to see the coveted data! We need space to do proper analysis!! Oh have the data gods forsaken us?!? Answer to Your OLAP Prayers Obviously I would not bring up this point if I did not have an answer. So here’s how you work yourself out of this tight spot. Trick is to turn on the MS Excel Developer Ribbon. It may already be turned on – for instance, in the figure below you can see the Developer menu between the View and Excellent Analytics menus in my MS Excel menu. If you have the Developer menu – then skip to the next step. Otherwise, […]
Peek Inside SAS BI Book: Building Business Intelligence with SAS
You may be aware by now, our new SAS BI book Building Business Intelligence with SAS:Content Development Examples launched last week on the SAS Press site. That’s right, SAS Press or Amazon will be delighted to bring a copy of this SAS BI book to your door. When Angela and I set out to write this book, one of the first things we discussed was how new users get access to the SAS BI toolset and have no idea of its astonishing capabilities. This book guides you through each one so you understand it. Intermediate and advanced users will also find this book as a valuable reference. Several times, Angela & I have both found ourselves pulling out the draft copy to reference an advanced technique. This book fulfills a unique spot in the marketplace; there is no other single book that leads you through each SAS BI component with step-by-step examples along with hundreds of tips and techniques to make you successful! SAS Institute offers over nine SAS BI courses to cover all the topics we have jammed packed into this book. Making this an excellent study guide for those planning to take the SAS BI Content Development Exam. Your SAS […]
SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
SAS PROC SQL has a lot of neat ways you can extract and manipulate data. In my experience, SAS users typically write PROC SQL to query SAS datasets or relational tables in databases. You can also…
[[ This is a content summary only. Visit my webs…
Best Practices: To Write or Not to Write PROC OLAP… That is the Question
There are 2 ways to develop OLAP cubes in the SAS EBI platform: using SAS OLAP Cube Studio or by writing the PROC OLAP statement by hand and submitting the SAS program. There are pros and cons for both that I will go through in this post. Personall…