topic Re: Disconnected Time Dimension in VA in SAS Visual Analytics

Disconnected Time Dimension in VA

mshparber — Mon, 15 Jun 2015 06:48:25 GMT

Hello friends,

Is there a way to implement a following task in VA

In my table I have debts records:

CustomerID, Amount, DebtStartDate, PaymentDate

I would like to show for EACH DAY over a specific period the total of debts amount (which is the sum of all records that DebtStartDate<=EACH DAY<=PaymentDate)

For those of you that also work with Microsoft's DAX - it's similar to a disconnected Time Dimension

Thank you

Michael

Re: Disconnected Time Dimension in VA

TejaSurapaneni — Wed, 17 Jun 2015 07:33:07 GMT

Hi Michael,

Can you explain more (with example) for better understanding.

Thanks & Regards,

Teja Surapaneni

Re: Disconnected Time Dimension in VA

mshparber — Sun, 21 Jun 2015 11:34:35 GMT

Hi Teja,

Attached is Excel sample.

https://drive.google.com/file/d/0BxwE8Gt6ONVteDJDZWdMY2VXMWs/view?usp=sharing

My table only contains the debt START and END dates, as well as Amount and customer number

What I would like to see - is time line chart that shows me all my OPEN DEBTS on each date in any timerange that I choose

That's it.

Thanks a lot!

Michael Shparber

Re: Disconnected Time Dimension in VA

Sam_SAS — Mon, 22 Jun 2015 19:51:54 GMT

Hi Michael,

I asked around about this, and I don't think VA can generate or infer the date values based on your start and end dates.

I think you will need to create a DATE column as part of your data preparation.

A do loop like the following was suggested:

proc sort data=debt out=debt_sorted;
by customer_id date_start date_end;
run;

data debt_new;
   set debt_sorted;
   by customer_id;
format _date_ date.;
   label _date_ = "user inserted";

   if(customer_id.first) then do;
        _date_ = date_start;
        debt_amount = debt_amount;
        date_start = date_start;
        date_end = date_end;
        customer_id = customer_id;
       output;
   end;
   else do;
       label:         _date_ = _date_ + 1;
                     if(_date_ GT end_date ) {
                                goto label 2;
                      }
                      debt_amount = debt_amount;
                     date_start = date_start;
                     date_end = date_end;
                     customer_id = customer_id;
                    output;
                goto label;
   end;
   label2:

retain _date_;
run;

I hope that will help,

Sam

Re: Disconnected Time Dimension in VA

mshparber — Sun, 28 Jun 2015 10:19:21 GMT

Thanks Sam,

As I understand, you propose for each customer to create all records in between START and END dates...

Imagine just 100,000 customers having debt with average age of one year.

Creating a record for each day for each customer would create 36.5 million records

It is not that large of a database but it's much more rows without any new information at all

Now imagine that debt history contains 1.5 million customers. Now we're talking about dealing with half of billion rows instead of just 1.5 million.

Microsoft has a very elegant solution for this using Measures and a Disconnected Time Dimension in their Tabular SQL Server Technology

It would be so useful to have a similar feature in SAS VA

I am sure that lots of Data Analysts will find it useful in many fields such as Debts, Inventory, etc.

Whom can I talk to in SAS R&D to explain the need and the solution in detail?

Thanks,

Michael

Re: Disconnected Time Dimension in VA

LinusH — Sun, 28 Jun 2015 19:08:05 GMT

Yes, as you already notices, this technique could "explode" your data.

A starting point is to create an idea in this forum, which is one open way to communicate desired developments to the product. You'll have my vote!

In the mean time, you could either try to aggregate your data (if possible), or create an application outside VA.

Re: Disconnected Time Dimension in VA

Sam_SAS — Sun, 28 Jun 2015 22:52:56 GMT

Hi Michael,

As Linus recommends, you should create an "Idea" in the Visual Analytics community. All ideas are reviewed by product management, which should mean a good chance that the feature will be considered for a future release.

Thanks,
Sam