DATASQUARAUS

An unofficial point of view about SAS Visual Analytics

[datasquaraus]

May the Parameter be with you

Once upon a time, an analytic user that we will call “Darth Vader” from now on, asked me to know the total gross-sales amount made by his souvenir shops on the Emperor’s Death Star. I summoned a pack of filthy blood-thirsted puppy-loving spatial bounty-hunters, we made a SAS Visual Analytics report, and then I could show Darth Vader: “250 million colons sir!”. For those who don’t know, colon is not just the ending part of your intestine, it is Costa Rica currency as well. You might wonder why the Death Star currency is Costa Rica currency too, however this is a mystery that historians are still struggling with. I think it must have something to do with workforce costs.

“Good job padawan, today you survive, now tell me the total gross-amount for vibrator souvenirs sales only”. An ambiguous Ewok that was looking quite familiar with the topic, proposed to split the gross-amount KPI on the merchandise category. Now you should know the range of products sold on the Death Star is quite wide, therefore we turned out to have one hundred useless extra-columns, talking about shit-flavored lollypops, wrong-holed inflatable dolls, Jabba the Hut porn movies, and such, and we could not find vibrators. Darth Vader made the Ewok become more familiar with vibrators.

A wooly wookie suggested then to apply a filter, so we did. When Darth Vader saw the report, he asked why he couldn’t see the vibrator’s total together with the overall total inside the same table. “Sir, if we make a filter, VA is not considering anything else falling outside the filter constraints, so this is the total in its perception, this is the sacred VA Verb!”. The wooly wookie was taken to a beautician and shaved to death.

Then it came the time for the Gungan. “…shall weesa build a calculat measure?” It took almost four hours for the Gungan to write down the formula, and when Darth Vader asked for the total for shit-flavored lollypops too, the Gungan committed suicide.

And it was only after they were all shaved, suicided and “vibrated” to death, that it came to my mind Yoda’s teaching: “Miiiiike… the Parameter runs strong inside you! Use the Parameter!”. I always thought that having a parameter valorized as “vibrators” is not very useful, however in that moment I realized that you can have the parameter bound to a selector such as a textbox or a combobox, and then having the calculated item using the parameter itself.

IF ( ProductCategory'n = '<parameter>'p )

RETURN 'value'n

ELSE 0

Darth Vader types the product category inside the textfield, and then the  calculated item re-calculates on that very category.

In particular, parameters are very handy for what-if analysis (e.g.: what if Darth Vader asks for a new report?). Having complex KPIs with dynamic definitions means that by switching the parameters values we can have the metrics re-calculated on the fly basing on different possible scenarios. Many of you already know the Bridget Jones use-case: a drop-down list showing panties types and two KPIs: “% probability to get to bed”, “% probability to have him running after he sees your panties”. By selecting “granny-style panties” or selecting “tangas” the effect is quite different, and KPIs evidently invert their trends.

In the end, I want to emphasize just one aspect about this VA feature, but I have to be very clear about it: I watched “The Bridget Jones Diary” just once, and just for Visual Analytics’ sake. I swear.

May the Parameter be with you.

[datasquaraus]

Little pimples on the huge smiling BI Face

As we all know, humanity is split in two great families. The ones  that used to play “Connect the dots”  when they were children, and the ones that used to play “Coloring pictures” when they were children. History is nothing but the never-ending tragedy of those two peoples fighting and struggling for making their own faction prevailing and annihilating the other one. Modern historian agree that Acheans were skilled dots connectors, whereas Troyans used to color pictures in their religious ceremonials.  Ancient Romans used to paint clothes in purple and other nuances based on their social rank, while Barbarians used to torture their victims by joining their face pimples with a marker.

Now the big question is: “Is SAS Visual Analytics a dots connector or is it a pictures colorer?”. The answer is both. SAS Visual Analytics is not a single application, it is a whole suite of webapps. For example, Report Editor is more focused on layout styles than any other VA component, for it is supposed to provide information to non-technical color-seduceable users like Troyans and Ancient Romans. On the other hand, Query Builder is about drawing lines among tables like Achaeans and Barbarians did. Of course there is almost no issue in coloring numbers (except when you are painting in an optimistic cheerful green your huge company losses). Matters arise when you are trying to connect dots, for dots joining implies logics, and datamart tables do not have numbers on it.

Most of the logics I’m talking about have to do with common data modeling, that is defining joins and keys. However SAS Visual Analytics adds one more level of complexity, that is about the Query Builder standing between your staging area (DBMS, SAS plain datasets, Hadoop,..) and your LASR server. Just think of your porn movies anagraphical table. Yeah, the one containing “Star Whores”, “In Diana Jones”, “Breast Side Story”, “A Beautiful Behind” and such. You might consider it as a dimensional table shared among several separate datamarts at once. You first use it in your cultural soirees star schema. Then you decide to re-use it to make anal-ytics for your own porn website. However, when you are designing this second query, connecting the accesses table with the movies  table, VA will complain: the porn movies table is already in the LASR server. You might then take the second LASR table in a different library, but it means you are duplicating data. You might think that having two “Beautiful Behind” is good, but in general data duplication is not a good idea. Or you might re-use the LASR table you included in the first query. But it means you always have to run the first query before  you run the second, that is creating an implicit dependency, for the second query won’t update data from the staging area to the LASR server. VA schedules queries one by one, making it quite hard to establish formal dependencies.

So what? By the end of the day, Query Builder is good to design the query. It generates reusable code, it creates metadata for the LASR tables, but when you have several datasets, queries and dependencies, scheduling proper ETL is much better, maybe copying and pasting code from your visually generated queries. You might have phase one making all of your necessary tables loaded to the LASR server, and then you can have phase two running your imstat procedures, knowing the data you get is up to date. Even more important, you can have it all called by a %include statement placed right in the end of your main ETL flow, making  it sure that you are loading data into the LASR server as soon as all of your data has been properly processed and your staging area is filled with fresh data.

You connecting the dots?

[datasquaraus]

The Interphone Paradox

When it comes to talk about optimization and user-experience, philosophy and science meet in a gray area of mistery where time relativity becomes a major issue, a contradiction in terms which is usually called "the Interphone Paradox":

-Bzz... Hello my love... Bzzz... I'm downstairs, are you coming? -Bzz... Yeah, I'll be there in a minute.

Philosophers usually explain that with the legend of the wise janitor. Once upon a time in Naples there was a lawyer company. They had an issue: the lift was too slow and little. Replacing the old lazy lift with a modern one was far too complicated and expensive, and employees were  suffering. In a situation where science could not help, the wise janitor came with the right solution: let's put a mirror next to the lift entrance. Employees were pushing the lift button, then looking at the mirror, checking if the makeup was ok, tightening their ties, making sure the hickey they got the night before in the disco was not that evident and such. And it was like magic: they pushed the button and it was like the lift was taking no time to arrive.

Information technologies call it "progress bar", "loading wheel", "porn animated gif", that is some graphical trick telling the user "no, seriously: I'm really doing something...", rising the user expectations and making him confident that something good is about to happen.

Taking it down to SAS Visual Analytics and Stored Processes, the problem is about the STP execution flow: when entering the report, the STP is launched and an empty frame is displayed until execution is completed. No way to put a porn animated gif in it. Take this you wise stinky janitor! So, what if your STP is expected to take a lot of time to execute? A nice solution might be using two STPs instead of one (would you have ever had thought of it my poor janitor? MHUAHAUHAUHAUHAUHAUAHAUHAUHAUAHUAHUAHAUHAUHAUHA).

First thing you have to do is about creating a "Launcher" STP, containing just the loading wheel and a hidden iframe pointing the "Core" STP. Then, you add a javascript callback on the iframe loading completion event, hiding the wheel and showing the iframe. This way the flow is completely asynchronous: the launcher STP is shown immediately, for it takes almost no time to load the gif. The core STP has no relation with the rest of the GUI, so Visual Analytics won't bother making you waiting, and the launcher is the only responsible for the output to be displayed.

How do I execute the core STP? You just need to have your iframe src referencing the SAS Stored Process Web Application, which is usually bundled with the SAS Web infrastructure Platform you get with Visual Analytics.

How do I pass prompt parameters from the launcher STP to the core STP? Simply by passing the core stp the same query string as the one for the launcher (except the _program parameter, that is the STP to be executed).

How do I accept being smarter than the janitor? There is no way: you are just far too cool, that's all.

data _null_; file _webout; [...] put '$(document).ready(function() {'; put 'var queryString = window.location.href.substring(window.location.href.indexOf("?"));'; put 'queryString = queryString.replace("STPLauncher","STPCore");'; put 'var ifr=$("<iframe/>", {'; put 'src:"http://<host>/SASStoredProcess/do"+queryString,'; put 'style:"display:none",'; put 'load:function(){'; put '$("#waitingDiv").addClass("hidingClass");'; put '$(this).show();'; put '}'; put '});'; put '$("body").append(ifr);'; put '});'; [...] run;

[datasquaraus]

I can’t STP loving you

One of the  most underestimated controls in Visual Analytics Report Designer is Stored Process Control. Probably, this is more of a psychological bias, some kind of deep fear in post-SAS-9.1.3 users souls: what is an STP for in a Visual Reporting Tool? Why should I run some kind of SAS code when the GUI is already doing everything I need? Should we really awake and unleash the ancient demons resting behind the execution of plain user written SAS code?

They say the Dark Side of the Force is evil, but they don’t tell you about freebies. Just think of your nastiest end user accessing a troublesome report. Wouldn’t Darth Vader use the Force to fill the user mailbox with porn spam right in that moment, misleading him and taking his attention away from the report? Just embed in your report an STP using proc http to call youPorn APIs and the job is done.

The light side of the Force benefits as well from STPs. Main uses comprehends tricky layouts, advanced functionalities, LASR server management, and filling browsers with furry chubby heart-shaped-eyed pets.

On one hand, by using STPs you can show in a frame any kind of output. It doesn’t just mean you can show a number you wouldn’t be able to show in a table. It means you can have the STP writing out your own HTML output, including images, advanced css, javascript interactions, you can even include external js libraries such as jquery. It means having a real web application inside your report, providing advanced functionalities, including external content, calling other STPs, handling advanced side effects.

On the other hand, you can have the STP managing the LASR sword. Err…  I was meaning LASR server, of course. An STP natively has the capability to present the user with prompts. By asking the user which kind of data he is going to use, you can have LASR specific procedures such as proc imstat to dynamically load in-memory just the tailored portion of data that is needed, maybe setting constraints on the number of rows you are going to load, resulting in dramatic performance boosts when you deal with huge amounts of data being held on unfit architectures. By the end of the story, you have the elephant getting into the matchbox. One bit at a time. Jabba the Hut would approve.

  “Fear is the path to the dark side. Fear leads to anger. Anger leads to hate. Hate leads to suffering. Suffering leads to STP.”

– Yoda

[datasquaraus]

Twinkle Twinkle Little Star Schema

When I was I little child, I used to think a star schema is some kind of query, and when a star schema falls down from the sky, you have the right to make a wish. Actually, when a star schema falls down, the only wish you have to make is about not having too much data in it. Don't tell anyone, or your wish won't come true. In particular, don't tell your SAS Visual Analytics.

Sometimes reports and data explorations lay on very big datasources, and your machine is not sized the right way to handle that much effort. Just think about the number of times you got blown off by a girl. Every single facepalm is a fact. Around facts, you place dimensions: the girl anagraphical information, the place you got bounced, the brothel she works in, err...

On a two years window I'm talking about 400 millions refusal events (35GB table), 6.000 girls, 800 locations, 250 approaching tactics, 80 denial excuses. If  you are using Visual Analytics query builder to join these tables, it might mean you are loading on your LASR server a 270GB denormalized dataset as your analysis datamart. Multiply this by the number of your alcohol self-support group mates asking you to have their own analysis. I'm telling you: first thing you'll do is using the compress option. I mean you are using it on your data! The second one is changing your psychiatrist, because compress option might save you 10-15 times the RAM consumption, however when using the datamart, decompression translates to  "CPU time", which might become a major problem. What's the use of strategical analysis if the query ends up when I'm 80?

In this scenario, one good option might be avoiding building your datamart as a query and creating it as a star schema, having the "create as view" option turned on. Be warned: in star schemas it really does what it means, in queries it's just a sadistic joke. In this case you are not loading the full denormalized table on your LASR server, for you are just loading the very single tables, consuming just the arithmetic sum of the datasets size. The tables join is performed by blocks (that is: just a few girls at a time saying you are not tall enough to speak with them). On big volumes, the time it takes to join the blocks is much less than the time it would take to decompress the denormalized table.

Twinkle, twinkle, schema-star How I wonder what you are Up above the world so high Like a snow-flake in the sky

[datasquaraus]

HTTP 503: Service unavailable for holiday got stuck on highway mqueue

503 is a quite common http error. It points out a “Service unavailable“ issue. Basically it is just like a notice on your coffee shop door saying “We’ll be right back”. But they don’t tell you why, they don’t tell you when, they don’t tell you if. Problem is, you can think of changing your coffee provider, but you can’t think of switching to a different BI platform, for it will cost you much more than planning a reasonable revenge, burning the coffee shop and look for another one.

So, when your SAS Visual Analytics is not available, where is he? He is on holiday, I think. I make this assumption because most of the times, this issue is caused by your VA mqueue getting stuffed like a turkey on the 4th of July. Therefore, when they say “your SAS  Visual Analytics is stucked in a queue”, I just imagine my VA on his new Camaro, running for holiday and getting stuck in a traffic jam of lazy VAs running to the seaside.

Basically This problem occurs because the broadcastTopic messages in ActiveMQ do not expire. As a result, the dead-letter queue (DLQ) fills up,leading to filled KEBAB db_*.log files. This broadcast topic is used by the distributed in-process (DIP) scheduler, the DIP job runner, and the job execution services (JES) job runner.

SAS has come up with a SAS note ( http://support.sas.com/kb/52/315.html ) explaining some workarounds for solving the problem :

1.       Spill napalm on the DLQ: basically it is about stopping the services and deleting the mqueue files (SAS-configuration-directory/Levn/Web/activemq/data/*). Don’t forget to stop the services or your application server will evoke Satan.

2.       Use the Discarding DLQ Plug-in:  you are warned, this plugin is buried deep in an ancient Egyptian tomb, and very few have survived the curse. None survived  the installation instead.

3.       Stop the DIP Scheduler Process. Gently asking won’t stop the service, so you’d better try shooting his legs.

Option one has always been working for me, but I can understand you do not always have napalm available.

(Special thanks to Mr. Faccin, our soul savior: he gave us this advice one year ago, and we brought back to life up to 20 VA corpses. Beware: the living-dead Visual Analytics era is about to come!)

[datasquaraus]

It Hurts When IP

Sometimes your SAS Visual Analytics gets famous. Sometimes the information you publish might become strategic for many more people, and you need to share. If you are keeping statistics about your farts in terms of energy production, pollution, calculating the YTD trend, annual growth and smell, information may attract new business from the natural gas or chemical warfare market,  and you’ll want to show your potential partners what the farting projections for 2015 are. You might also rebrand your SAS Visual Analytics in an ASS Visual Analytics version as well.

 In such a perspective, there’s plenty of things you can do: publishing and delivery are just a science. One thing you can do is publishing Visual Analytics on the Web. Most of the steps you need to take are the very same steps you would take for publishing on the internet any other website, so I won’t bother with that, except for one thing: be cautious when you set the IP and DNS. You don’t want your CEO to type the Visual Analytics URL in his home browser and enter YouPorn: this wouldn’t be Visual Analytics, this would just be Visual Anal.

 Now the SASsy thing about the story. The thing you need to keep in mind is: VA is not a single web application, it is a whole suite. The entry point is SASLogon. When you successfully enter credentials, it will forward you to the module you intended to access (Report Designer, Report Viewer, Data Explorer, Saint John’s Apocalypse quotations…). In order to do this, SASLogon needs to know WHERE those things are. It’s like when you enter a club privè: there is a guy at the entrance, and you don’t want him to send you to the little chapel they have right after the kitchen. Or maybe you do. So, SASLogon will use the hostname, or IP, or even localhost to look for the other modules, but all of these things make sense just within your local intranet. And this is why you have to go and substitute all of these occurrences with a public reference such as the public IP or (even better) the domain. Those occurrences are both metadata and phisycal.

 For metadata you need to go on your SAS Management Console -> Configuration Manager -> Visual Analytics. In here you will find all of the modules configuration sets. Open the ones you intend to access form the web, click Properties, move to the External Connection Tab, and modify the hostname with a name which is a valid reference from outside. Pornhub.com is not a good choice, thought it could make a lot of fun.

 You need to do the same thing on the physical side, that is the web application server. Things may vary depending on the server you use, for example in JBoss it’s just about modifying the proxyname in  server.xml with your public domain.

 Once it’s all done, don’t forget the software engineer golden rule: stop and restart. EVERYTHING. And when I say “everything”, I mean you should turn on and off your office lights as well. Remember: Karma is watching you…

[datasquaraus]

Alt+Ctrl+BIBBIDIBOBBIDIBU

You all know SAS does the job, and it is probably the best option on the market for what it is supposed to do. However, It is common knowledge that SAS has its own sense of humor as well. A very sharp sense of humor, actually. It’s plenty of stories about housewives having a strict delivery for dinner, maybe a dynamic dashboard about carbohydrates contained in their husband meal, and it often happens that five minutes before husband arrives, she’ll get an XML reference error in her report. Actually, this is not SAS. This is Karma. Try asking the plumber.

The average Joe  housewife will redo the dashboard from scratch. The smart housewife will take her husband to the best Italian restaurant in town, so that maybe he won’t care about the business intelligence side of it. But then the husband will surely get suspicious about the plumber.  A good thing about SAS Visual Analytics is that when the visual editor goes head over heels, you have a screwdriver to open the report belly for a quick surgery.

By ENERGICALLY clicking Alt+Ctrl+B you get the XML physical structure of the report: layout, filters, dependencies, references. If you plan to try to beat Visual Analytics in speed by changing object interactions faster than your eyes can see, if you are pushing one million views in the very same report section, if you are giving your tables blasphemous names and a pack of rats is gnawing your server rack cables, your object references might break. By opening the trunk, you can rewire object references and save the day. Of course, being as good as Neo in reading the Matrix, is definitely mandatory.

Anyway… they never told me what the “B” stands for. BIBBIDIBOBBIDIBU I suppose…