Month: March 2020

Data: REST API

API: Application Program Interface

A. Operation

  • GET: Retrieve
  • POST: Create
  • PUT: Update
  • DELETE: Remove

B. Formats

  • HTML
  • XML
  • Plain text

C. Use

  • Social: Twitter, Facebook
  • Utilities: Dropbox, Google Maps
  • Commerce: Stripe, Mailchimp

D. Other services

  • HATEOAS: Hypermedia As The Engine Of Application State
  • Postman: API testing
  • Spring Data REST

E. Application

  • Python: request

SAS: Multiple Set statements to combine and summerize datasets

A. Code

  • Gather data
%let var = field1 field2 field3 field4 field5;
%let yr = 17;
%let nextyr = 18;
data data&yr.;
length group $1.; 
set Jun&yr. (keep=&var.)
jul&yr. (keep=&var.)
nov&yr. (keep=&var.)
feb&nextyr. (keep=&var.)
mar&nextyr. (keep=&var.);
where field1= "YES";
group = substr(field2, 1, 1);
count = field3;
run;
  • Summarize data
%macro sum (varname, cntvar, group, where, out);
%put &varname.;
%put &cntvar.;
%put &where.;
proc summary nway missing data =data&yr. (where= (field4=&where.));
class field1 &group.;
var &cntvar.;
output out=&out. (drop= field1 _freq_ _type_ ) sum (&cntvar.)=&varname.;
run;
%mend sum;
%sum ( var6, count, group, 'CAT1' , t1);
%sum ( var6, count, , 'CAT1' , sum1);
%sum ( var7, count, group, 'CAT2' , t2);
%sum ( var7, count, , 'CAT2' , sum2);
%sum ( var10, count, group, 'CAT1' and field5 = 'NOV' , t3);
%sum ( var10, count, , 'CAT1' and field5 = 'NOV', sum3);
%sum ( var11, count, group, 'CAT2' and field5 = 'NOV' , t4);
%sum ( var11, count, , 'CAT2' and field = 'NOV' , sum4);
  • Combine data
data data_new;
set t1 sum1 ;
set t2 sum2;
set t3 sum3;
set t4 sum4;
if group = '' then group = 'X';
run;

B. Output

T1 output
Sum1 output
combining T1, T2, T3, T4, Sum1, Sum2, Sum3, Sum4 by group alignment

PowerBI: App Access and Manage Roles

A. Manage roles – in PowerBI desktop

  • on the report, there is a “Language Course Subject Selection” Box
  • need to set up roles so user can access report information only for certain language.
  • under the “Modeling” tab, click “Manage roles”
  • In the “Manage roles” dialog box, click “Create” button to create new roles.
  • I have created 3 roles: “ESL”, “German”, and “Universal Access”.
  • Tables section listed the all tables in the powerBI file. I have two tables in this file: data1 and lang_dimension1. data1 is the student level demographic fact data and lang_dimenstion1 is the language course information.
  • “ESL” role will filter the results for language course description for only “English as a Second Language” courses.
  • “German” role will filter the results for only German language course.
  • “Universal Access” role has no filter.
  • The “Table filter DAX expression” section can be used to define the filter(s)
  • If the filter is applied to a particular role, the filter sign will appear next to the … (more) sign of the table .
  • Click … to access more options of the roles or tables.
The examples shows configuration of the “German” role with the DAX expression on the “lang_dimension1” table subjdesc field.

B. View as Roles – in PowerBI desktop

  • under the “Modeling” tab, click “View as”
  • check “German” and OK
View as “German” role
  • The visual will only show the results for German language courses and “Language Course Subject Selection” will only have “German” listed.
  • There is also a yellow warning message appeared on the top on the visual stating “Now viewing report as: German” and click the “Stop viewing” to stop viewing the report as German role.
  • after setting up the roles and viewing the roles to make sure the roles are functional, publishing the report to the PowerBI workspace.

C. App Access – in PowerBI service

  • Be careful with creating and assigning roles to the report. As soon as you create a role in report, all the end users of the report need to be assign to a role. If no role is assigned, even you add the end users to the app, they will not able to view the powerbi visuals, and all the visuals become blank boxes with a message that you don’t have permission to the underlying dataset.
  • In the PowerBI workspace where the PowerBI report was published, go to the “Datasets” tab and click … of the report and select “Security”
  • select row-level security and add the emails of the member and save.
  • update app.

Git: GitHub Desktop

A. Download

B. Steps

  • set up username and password in github.com
  • in github desktop, create a new repository
Provide name of the repository and local path.
  • Commit files to branch
Make the initial commit. The commit is in the master branch.
  • Publish repository to GitHub
Undo if there is anything wrong.
  • Review the commit in History tab
  • ignore files setup
    • in the same directory of the files being committed, set up a .gitignore text file.
    • *.* and !*.sas : ignore all files in the directory except for .sas files and files in the subfolders.
    • /foldername: ignore subfolders