It’s not spring-time, but it’s still worth giving a thought to the cleanliness of your environment, for the benefit of reducing complexity and of reducing space usage. Angela Hall posted a great article about Cleaner OLAP Cube Physical Folder Structu…
Tag: OLAP
Use OLAP data sources from SAS Stored Processes
So are you now dreaming big? Coming up with other ideas for how SAS Stored Processes can be leveraged in your installation of SAS? As I mentioned in last week’s post, you could use OLAP cubes as a data source for your SAS Stored Process. Here is how I …
SAS OLAP Cubes: Automatically Collect ARM Performance Logs
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 […]
Selecting multiple individual dates in prompted reports
The date prompt from SAS Prompt Framework provides the options of single selection or a range, however what if you need to select multiple individual dates such as Monday (May 28), Wednesday (May 30), and Friday (June 1) of last week? There are a few a…
Change your passwords without locking out your connection
Did you change your LinkedIn password yesterday? (If you didn’t, you should!) But did it happen to be the same as your corporate password? First, tisk tisk. Second, change your corporate password NOW! Ok, now that this is done ~ don’t let your save…
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. […]
MDX: Dynamically Hiding Measures for Compliance
OLAP cubes are great for summarizing data very fast – I love them . In certain environments, HIPAA compliance for personally identifiable data might be a concern. Even at the lowest grain of a cube it might be possible to identify the specific detail data beneath the surface if the summarized number is small enough. For example, if a report shows the number of people that work for a company in a county of a state that are over the age of 50 – it may be beneficial to hide the statistic to protect the identity of that sample size. Using MDX logic, we can dynamically hide measures that meet a certain criteria. In the MDX code below, I have added an additional measure to the OLAP cube I used for SAS Global forum in 2011. It will aggregate the Total Sales measure like normal but if the Total Sales measure is less than 50,000 it will show nothing. DEFINE Member “[SGF2011].[Measures].[Restricted Total Sales]” AS ‘iif([Measures].[Total Sales]<50000,NULL,[Measures].[Total Sales]),FORMAT_STRING = “DOLLAR20.0″‘; The Enterprise Guide screenshot to the right shows the output! Secure the Original Measure with Metadata Permissions One concept I always promote with writing MDX code is […]