Hi All, The meeting notes of today's discussion on the 'ISSUE in AM_DB data consuming widgets' - Discussion on APIM Analytics integration with SP dashboard @ Tue Nov 27, 2018 3pm - 4pm (IST) Description of the issue
For the APPLICATIONS CREATED OVER TIME widget (In the middle of the discussion the widget use case changed. Will be detailed later in the doc), there is a need to join the two tables AM_SUBSCRIPTION and AM_APPLICATION on APPLICATION_ID got from the AM_SUBSCRIPTION table based on the conditions given in the two dropdowns shown in the above diagram. But the Siddhi Store Data Provider does not support the joining of two tables. Previous Suggestions Next, the previous suggestions given by both APIM and SP teams were discussed. 1. Writing a siddhi application by using a trigger to transfer data from one table to a stream and use a join to join the needed table with it. 2. Publishing the subscription data to SP from APIM side for each time an application subscribes an API The first approach was not accepted due to the memory constraints and the second approach was also not accepted due to Application deletion issues. Final Solution At the end of the meeting, we decided to use Siddhi aggregations as our solution. A siddhi application will be written in order to create aggregations inside STAT_DB which keeps the state of the subscription count. The approach will be a little similar to the IS_ANALYTICS_SESSION_DATA siddhi application. https://github.com/wso2/analytics-solutions/blob/master/features/is-analytics-feature/org.wso2.analytics.solutions.is.analytics.feature/src/main/resources/siddhi-files/IS_ANALYTICS_SESSION_DATA.siddhi#L286 This will not cause deletion issues since the state is maintained in the aggregation table and if an application got unsubscribed from an API, the count will be updated in the next record. The approach is considered better since it adds more business value to the charts it generates. Rather than previous incrementing charts, now it is possible to show the count decrements when an application unsubscribes an API. Chart Use cases got changed with their titles !!! During the meeting, we have found that the Applications_Created_OverTime chart and Subscriptions_OverTime charts look similar in the way they provide data. And later on, decided to flip the titles of the charts. Applications_Created_OverTime Will display only the application count filtered on the time range provided and the application creator. Subscriptions_OverTime Will display the subscription count filtered on the time range provided, the subscriber and the API it subscribes. So, the new solution will be tested using this widget and if the procedure is not difficult the solution will be applied to other overtime analytics graphs. Thanks. Best Regards, *Sandalu Kalpanee* | Software Engineer - Intern (M)+94 776931433 | (E) [email protected] On Tue, Nov 27, 2018 at 5:47 PM Niveathika Rajendran <[email protected]> wrote: > Hi all, as per the discussion held today[1], we have decided to use Siddhi > aggregations on solving this by calculating the current state count to show > the "Subscriptions over time" > > @Sandalu Kalpanee <[email protected]> Could you please add the meeting > notes? > > [1] Invitation: Discussion on APIM Analytics integration with SP > dashboard @ Tue Nov 27, 2018 3pm - 4pm (IST) > > Best Regards, > *Niveathika Rajendran,* > *Senior Software Engineer.* > *Mobile : +94 077 903 7536* > > > > > > On Fri, Nov 16, 2018 at 11:23 PM Sajith Dimal <[email protected]> wrote: > >> Hi, >> >> On Fri, Nov 16, 2018 at 10:33 AM Fazlan Nazeem <[email protected]> wrote: >> >>> If we are to publish those data to SP, it is going to introduce more >>> complexity. >>> >>> 1) If we are publishing an event to SP when a subscriber creates an >>> application, then when analytics is switched off, that information will not >>> be published, which will then lead to data inaccuracies. >>> >> Anyway, now also we publish events from APIM to SP, similar way can we >> add this information into zip file and publish, just to use as statistical >> information. >> >>> 2) Deleting an application and deleting a subscriber has to be handled >>> separately. >>> >> I think this should be two(delete/subscribe) events and correlate in >> siddhi? >> >>> I heard from Sandalu that someone suggested using a SQL View. I think >>> this could be a solution if Siddhi provider is capable of executing a query >>> against a view. WDYT? >>> >> We haven't tested this approach yet, but we can try it and see. We have >> to give a script to make table views. >> >> One other suggestion is, since we have current java implementation to get >> this data from table, shall we make it as microservice and put into SP >> dashboard runtime(to avoid CORS)? >> Then using axios react component we can access the API through the widget. >> >> On Thu, Nov 15, 2018 at 6:00 PM Tishan Dahanayakage <[email protected]> >>> wrote: >>> >>>> +1 for SajithD's suggestion. Gadget layer should only do minimal amount >>>> of processing such(i.e.filtering). All other complex operations should be >>>> pre-handled by event-processing layer IMO. Can't we enrich the STAT table >>>> with application ID beforehand at Siddhi level before persisting? >>>> >>>> Thanks, >>>> /Tishan >>>> >>>> On Thu, Nov 15, 2018 at 5:36 PM Sajith Ravindra <[email protected]> >>>> wrote: >>>> >>>>> Hi Fazlan, >>>>> >>>>> In general, we should be trying to fetch the required data using >>>>> Siddhi provider. >>>>> >>>>> Going forward our recommendation is to use Siddhi provider due to >>>>> reasons such as it's not coupled with a specidic database and provides >>>>> protection for vulnerabilities such as SQL injection. >>>>> >>>>> Thanks >>>>> *,Sajith Ravindra* >>>>> Associate Technical Lead >>>>> WSO2 Inc.; http://wso2.com >>>>> lean.enterprise.middleware >>>>> >>>>> mobile: +94 77 2273550 >>>>> blog: http://sajithr.blogspot.com/ >>>>> <http://lk.linkedin.com/pub/shani-ranasinghe/34/111/ab> >>>>> >>>>> >>>>> On Thu, Nov 15, 2018 at 5:13 PM Niveathika Rajendran < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi Fazlan, >>>>>> >>>>>> As per the offline discussion with Sandalu, we have decided to go >>>>>> with 2 step query approach for now since, we do not have a feature which >>>>>> allows running queries on top of a database from Widget level, as this >>>>>> is a >>>>>> security vulnerability. In this case, the entire database would have to >>>>>> be >>>>>> exposed rather than a single table. >>>>>> >>>>>> As of now, the issue was that *applicationId* needed for the query >>>>>> is available in a separate table. This table "AM_SUBSCRIPTION" can be >>>>>> queried first to get the applicationId, after which the needed table can >>>>>> be >>>>>> queried for the data needed in the widgets. >>>>>> >>>>>> >>>>>> Best Regards, >>>>>> *Niveathika Rajendran,* >>>>>> *Senior Software Engineer.* >>>>>> *Mobile : +94 077 903 7536* >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Nov 15, 2018 at 4:55 PM Fazlan Nazeem <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> Analytics team, >>>>>>> >>>>>>> Will we have a solution for joining multiple tables from a widget >>>>>>> config in a future release? If not, is there an alternative solution. >>>>>>> >>>>>>> On Thu, Nov 15, 2018 at 11:58 AM Sandalu Kalpanee <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi all, >>>>>>>> >>>>>>>> A small addition to the above mail. >>>>>>>> >>>>>>>> For the above graph, I just need to get the count(APPLICATION_ID) >>>>>>>> and CREATED_TIME from AM_APPLICATION table which will be filtered using >>>>>>>> AM_API, AM_SUBSCRIBER, AM_SUBSCRIPTION tables. >>>>>>>> >>>>>>>> *AM_API (API_ID int,API_PROVIDER string,API_NAME string,API_VERSION >>>>>>>> string,CONTEXT string,CONTEXT_TEMPLATE string,API_TIER >>>>>>>> string,CREATED_BY >>>>>>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string)* >>>>>>>> >>>>>>>> *AM_APPLICATION (APPLICATION_ID int,NAME string,SUBSCRIBER_ID >>>>>>>> int,APPLICATION_TIER string,CALLBACK_URL string,DESCRIPTION >>>>>>>> string,APPLICATION_STATUS string,GROUP_ID string,CREATED_BY >>>>>>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string,UUID >>>>>>>> string,TOKEN_TYPE string)* >>>>>>>> >>>>>>>> *AM_SUBSCRIPTION (SUBSCRIPTION_ID int,TIER_ID string,API_ID >>>>>>>> int,LAST_ACCESSED string,APPLICATION_ID int,SUB_STATUS >>>>>>>> string,SUBS_CREATE_STATE string,CREATED_BY string,CREATED_TIME >>>>>>>> string,UPDATED_BY string,UPDATED_TIME string,UUID string)* >>>>>>>> >>>>>>>> *AM_SUBSCRIBER (SUBSCRIBER_ID int,USER_ID string,TENANT_ID >>>>>>>> int,EMAIL_ADDRESS string,DATE_SUBSCRIBED string,CREATED_BY >>>>>>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string)* >>>>>>>> >>>>>>>> Thanks. >>>>>>>> >>>>>>>> On Thu, Nov 15, 2018 at 8:36 AM Sandalu Kalpanee <[email protected]> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hi All, >>>>>>>>> >>>>>>>>> I came up with an issue while working on my publisher >>>>>>>>> dashboard widgets creation. >>>>>>>>> >>>>>>>>> *Issue:* No option to join tables in siddhi queries. >>>>>>>>> *Widget:* Applications Created Over Time + (Other widgets which >>>>>>>>> will consume AM_DB data) >>>>>>>>> *Data source:* I am using Siddhi store data provider with the >>>>>>>>> shared AM_DB. (In the deployment.yaml file I have added a data >>>>>>>>> source referring the AM_DB at APIM side.) >>>>>>>>> >>>>>>>>> ************ sample providerConfig in widgetConf.json >>>>>>>>> **************** >>>>>>>>> "type": "SiddhiStoreDataProvider", >>>>>>>>> "config": { >>>>>>>>> "siddhiApp": "@App:name('App Name') @primaryKey('Primary Key') >>>>>>>>> @store(type=\"rdbms\" , datasource=\"AM_DB\") define table >>>>>>>>> TableName (Column Headings with Types) ;", >>>>>>>>> "queryData": { >>>>>>>>> "query": "Query" >>>>>>>>> }, >>>>>>>>> >>>>>>>>> * >>>>>>>>> **************************************************************************** >>>>>>>>> >>>>>>>>> *Required Widget View: * >>>>>>>>> >>>>>>>>> According to the below figure the first combo(All APIs or My APIs) >>>>>>>>> will provide the user an option to filter the API list in the third >>>>>>>>> combo. >>>>>>>>> API list inside the third combo will be populated by AM_API table >>>>>>>>> using a >>>>>>>>> separate query in the widgetConf.json. The second combo will list >>>>>>>>> down the >>>>>>>>> APP creators which will be populated by AM_subscriber table using >>>>>>>>> another >>>>>>>>> query. And according to the selected options in these combos the data >>>>>>>>> will >>>>>>>>> be filtered from AM_APPLICATION table. >>>>>>>>> >>>>>>>>> [image: App-Registrations.png] >>>>>>>>> >>>>>>>>> *Query used to generate the graph in APIM 2.6 version: * >>>>>>>>> https://github.com/wso2/carbon-apimgt/blob/v6.4.50/components/apimgt/org.wso2.carbon.apimgt.usage/org.wso2.carbon.apimgt.usage.client/src/main/java/org/wso2/carbon/apimgt/usage/client/UsageClient.java#L331 >>>>>>>>> >>>>>>>>> Ex: If a user has selected options other than 'All' for both >>>>>>>>> second and third combos, the data should be filtered from >>>>>>>>> AM_APPLICATION >>>>>>>>> table joining with 3 other tables AM_API, AM_SUBSCRIBER, and >>>>>>>>> AM_SUBSCRIPTION. >>>>>>>>> >>>>>>>>> But as there is no way to join tables using siddhi queries (And as >>>>>>>>> RDBMS batch data provider will not be OK with changing DB types), >>>>>>>>> there >>>>>>>>> will be an issue to generate widgets which consumes data from AM_DB >>>>>>>>> (This >>>>>>>>> issue doesn't count for the widgets generated by STAT_DB since the >>>>>>>>> data >>>>>>>>> will be extracted from a single table). >>>>>>>>> >>>>>>>>> Highly appreciated if you can suggest any solution for this. >>>>>>>>> Thanks in advance. >>>>>>>>> >>>>>>>>> Best Regards, >>>>>>>>> *Sandalu Kalpanee* >>>>>>>>> *Software Engineer - Intern* >>>>>>>>> *WSO2* >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *Sandalu Kalpanee* >>>>>>>> *Software Engineer - Intern* >>>>>>>> *WSO2* >>>>>>>> _______________________________________________ >>>>>>>> Architecture mailing list >>>>>>>> [email protected] >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Thanks & Regards, >>>>>>> >>>>>>> *Fazlan Nazeem* >>>>>>> Senior Software Engineer >>>>>>> WSO2 Inc >>>>>>> Mobile : +94772338839 >>>>>>> [email protected] >>>>>>> _______________________________________________ >>>>>>> Architecture mailing list >>>>>>> [email protected] >>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>> >>>>>> >>>> >>>> -- >>>> *Tishan Dahanayakage* | Associate Technical Lead | WSO2 Inc. >>>> (m) +94716481328 | (w) +94112145345 | (e) [email protected] >>>> GET INTEGRATION AGILE >>>> Integration Agility for Digitally Driven Business >>>> >>>> Disclaimer: This communication may contain privileged or other >>>> confidential information and is intended exclusively for the addressee/s. >>>> If you are not the intended recipient/s, or believe that you may have >>>> received this communication in error, please reply to the sender indicating >>>> that fact and delete the copy you received and in addition, you should not >>>> print, copy, re-transmit, disseminate, or otherwise use the information >>>> contained in this communication. Internet communications cannot be >>>> guaranteed to be timely, secure, error or virus-free. The sender does not >>>> accept liability for any errors or omissions. >>>> _______________________________________________ >>>> Architecture mailing list >>>> [email protected] >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>> >>> >>> -- >>> Thanks & Regards, >>> >>> *Fazlan Nazeem* >>> Senior Software Engineer >>> WSO2 Inc >>> Mobile : +94772338839 >>> [email protected] >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >> >> >> -- >> <http://wso2.com/signature> >> Sajith Dimal >> Senior Software Engineer >> Email: [email protected] >> Mobile: +94783101496 >> WSO2 Inc. | http://wso2.com >> lean.enterprise.middleware >> >> >> Disclaimer: This communication may contain privileged or other >> confidential information and is intended exclusively for the addressee/s. >> If you are not the intended recipient/s, or believe that you may have >> received this communication in error, please reply to the sender indicating >> that fact and delete the copy you received and in addition, you should not >> print, copy, re-transmit, disseminate, or otherwise use the information >> contained in this communication. Internet communications cannot be >> guaranteed to be timely, secure, error or virus-free. The sender does not >> accept liability for any errors or omissions. >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
