BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vachickenboy
Calcite | Level 5

Hi friends,
I am very new in sas va, i just start learning about sas by registering a account sas va on cloud that contains some apps like SAS vApp Data Manager, Sas management console, SAS Visual Analytic Hub and SaS vApp Ledger. i tried many times to find a way to connect to my data warehouse on oracle db from sas va cloud but i can not see any guide on that, so could anyone help me to draff a fastest way by listing preparation steps (including tools handled this tasks) to generate reports in sas va:
1. Connect to data source( my data warehouse)
2. Preparing data ( join, custom, append...ect.)

3. Design data model

4. Degisn report/dashboard

5. show report

 

I dont know exactly way that sas sa work, these steps  a lot of tool approachs to build step by step for starting.

i am so sorry for poor question.!

regards,
J. 

1 ACCEPTED SOLUTION

Accepted Solutions
rgreen33
Pyrite | Level 9

vachickenboy,

 

I too am new to SAS and I have been struggling to get my arms around this beast.  I am not using the cloud, but it sounds as if the same tools are available.  So, let me explain the steps that I have taken to connect to my Oracle db.

 

In Management Console (assuming that you have your account setup with the appropriate privileges for doing the following):

  1. Create a group that will use the Oracle Server and Connection...
    1. Click on the "User Manager" node to show Users, Groups, and Roles.
    2. Now, right-click on the "User Manager" node and select New >> Group.
    3. Give the Group a name, keeping in mind that this will be the group that will be allowed to access the connection to Oracle that we are about to setup (Display Name and Description are optional).
    4. Next, click on the Members tab.  Here, you will want to select the users and/or groups that will use the Oracle connection that we are about to setup.
    5. Next, click on the Accounts tab.  Here, we will define the account that is used to connect to Oracle (this would be an account that has already been defined in Oracle).
      1. Click the New button.
      2. Enter the User ID, Password, and Confirm Password (again, this is the credentials that have already been defined in Oracle).
      3. Next click the New button for Authentication Domain.
      4. Provide a name for this Authentication Domain.  This will need to be something that will indicate that these credentials are for your Oracle system (Description is optional).
      5. Click OK.
      6. Make sure that the Authentication Domain that you just created is selected.
      7. Click OK.
      8. Click OK to save the new group.
  2. Now, you are ready to create your Oracle Server and Connection in Management Console.
    1. Select "New Server...".
    2. In the "New Server Wizard" window, under Database Servers, select Oracle Server.
    3. Click "Next".
    4. Enter a Name for your server (Description is optional).
    5. Click "Next".
    6. You should be able to take the defaults on the next screen.  Click "Next".
    7. Enter the "Path" to the Oracle Server.  The Help button best describes this as..."specifies the Oracle driver, node, and database. Use the same Oracle path designation that you would use to connect to Oracle directly. Corresponds to the PATH= option on the SAS/ACCESS engine connection".
    8. Authentication type should be "User/Password".
    9. For Authentication domain, you will want to select the authentication domain that you created above.
    10. Next, click Finish.  Your Oracle server and connection should now be defined.
  3. Now, you are ready to create a Library.
    1. In Management Console, expand the Data Library Manager node.
    2. Expand the Libraries folder.
    3. Right-click on the Libraries folder and select "New Library..."
    4. In the New Library Wizard, scroll down and select the Oracle Library (should be under Database Data).
    5. Give the Library a meaningful name (Description is optional).
    6. Provide a location (or accept the default - whatever is appropriate for your setup).
    7. Click Next.
    8. Add SASApp to the Select servers.
    9. Click Next.
    10. Provide a Libref name.
    11. Click Next.
    12. Enter the Database Schema Name.
    13. Make sure that your Oracle connection (created above) is selected.
    14. Make sure that appropriate Default Login is selected (should be the account associated with the Authentication Domain that you created above).
    15. Click Next.
    16. Click Finish.
  4.  Now you can test to see if everything works by Registering your tables (making them available in SAS).
    1. Right-click on the Library that you just created.
    2. Select "Register Tables...".
    3. Accept the defaults and click Next.
    4. If everything is setup correctly, you should now see a list of available tables from Oracle.  Select the table that you wish to register.
      1. If you do not see a list of tables, then something is incorrect with your setup.  Go back and review the items that were setup in the above steps, making sure that all of your values appear to be correct.
    5. Modify the Location if necessary (depend on your setup).
    6. Click Next.
    7. Click Finish.
  5. Assuming that you were able to register your tables, it is now time to create your queries and load them into LASR memory.
    1. Open VA Data Builder.
    2. Using the graphical interface, create your query.
    3. Chose the correct options for loading the results to memory.
    4. Run your job.
    5. Once the data is in memory, you will be able to use VA Report Designer to create your dashboards/reports.

 

I hope this helps.  Good luck!

View solution in original post

5 REPLIES 5
rgreen33
Pyrite | Level 9

vachickenboy,

 

I too am new to SAS and I have been struggling to get my arms around this beast.  I am not using the cloud, but it sounds as if the same tools are available.  So, let me explain the steps that I have taken to connect to my Oracle db.

 

In Management Console (assuming that you have your account setup with the appropriate privileges for doing the following):

  1. Create a group that will use the Oracle Server and Connection...
    1. Click on the "User Manager" node to show Users, Groups, and Roles.
    2. Now, right-click on the "User Manager" node and select New >> Group.
    3. Give the Group a name, keeping in mind that this will be the group that will be allowed to access the connection to Oracle that we are about to setup (Display Name and Description are optional).
    4. Next, click on the Members tab.  Here, you will want to select the users and/or groups that will use the Oracle connection that we are about to setup.
    5. Next, click on the Accounts tab.  Here, we will define the account that is used to connect to Oracle (this would be an account that has already been defined in Oracle).
      1. Click the New button.
      2. Enter the User ID, Password, and Confirm Password (again, this is the credentials that have already been defined in Oracle).
      3. Next click the New button for Authentication Domain.
      4. Provide a name for this Authentication Domain.  This will need to be something that will indicate that these credentials are for your Oracle system (Description is optional).
      5. Click OK.
      6. Make sure that the Authentication Domain that you just created is selected.
      7. Click OK.
      8. Click OK to save the new group.
  2. Now, you are ready to create your Oracle Server and Connection in Management Console.
    1. Select "New Server...".
    2. In the "New Server Wizard" window, under Database Servers, select Oracle Server.
    3. Click "Next".
    4. Enter a Name for your server (Description is optional).
    5. Click "Next".
    6. You should be able to take the defaults on the next screen.  Click "Next".
    7. Enter the "Path" to the Oracle Server.  The Help button best describes this as..."specifies the Oracle driver, node, and database. Use the same Oracle path designation that you would use to connect to Oracle directly. Corresponds to the PATH= option on the SAS/ACCESS engine connection".
    8. Authentication type should be "User/Password".
    9. For Authentication domain, you will want to select the authentication domain that you created above.
    10. Next, click Finish.  Your Oracle server and connection should now be defined.
  3. Now, you are ready to create a Library.
    1. In Management Console, expand the Data Library Manager node.
    2. Expand the Libraries folder.
    3. Right-click on the Libraries folder and select "New Library..."
    4. In the New Library Wizard, scroll down and select the Oracle Library (should be under Database Data).
    5. Give the Library a meaningful name (Description is optional).
    6. Provide a location (or accept the default - whatever is appropriate for your setup).
    7. Click Next.
    8. Add SASApp to the Select servers.
    9. Click Next.
    10. Provide a Libref name.
    11. Click Next.
    12. Enter the Database Schema Name.
    13. Make sure that your Oracle connection (created above) is selected.
    14. Make sure that appropriate Default Login is selected (should be the account associated with the Authentication Domain that you created above).
    15. Click Next.
    16. Click Finish.
  4.  Now you can test to see if everything works by Registering your tables (making them available in SAS).
    1. Right-click on the Library that you just created.
    2. Select "Register Tables...".
    3. Accept the defaults and click Next.
    4. If everything is setup correctly, you should now see a list of available tables from Oracle.  Select the table that you wish to register.
      1. If you do not see a list of tables, then something is incorrect with your setup.  Go back and review the items that were setup in the above steps, making sure that all of your values appear to be correct.
    5. Modify the Location if necessary (depend on your setup).
    6. Click Next.
    7. Click Finish.
  5. Assuming that you were able to register your tables, it is now time to create your queries and load them into LASR memory.
    1. Open VA Data Builder.
    2. Using the graphical interface, create your query.
    3. Chose the correct options for loading the results to memory.
    4. Run your job.
    5. Once the data is in memory, you will be able to use VA Report Designer to create your dashboards/reports.

 

I hope this helps.  Good luck!

vachickenboy
Calcite | Level 5

@rgreen33
I really thank for your tutorial, its very clear and helpfull! I know i need to start with management console firstly, i am practicing SAS VA, its harder than other tools.
I have experienced with IBM cognos, it requires to build some OLAP cubes before pushing a package to the web portal, Is SAS requires like that? Did you evaluate the SAS OLAP cubes?

Once, thank you so much!.
Regards,
J.

rgreen33
Pyrite | Level 9

No, we are not using SAS OLAP Cube Studio and SAS OLAP Server.  Our environment is setup with SAS (with Hadoop).  We are using the VA tools, Data Integration Studio, and Enterprise Guide (that's more than enough for us now Smiley Happy).  In learning this system, I have found that the thing that helped me most was to simply get a couple tables registered and then play with the tools...using the few table that I had loaded.

 

I have found that when using VA Designer, you need to spend a good bit of time planning your data as a first step.  In other dashboard/reporting systems that I have used, I was able to do joins at the dashboard/report level.  With VA Designer, it seems that the data needs to be joined and flattened already. It seems that the more time spent on data preparation, the easier the dashboard/reporting development will be.

 

Oh...and then there is scheduling.  You have to plan your schedules so that your data will be loaded and refreshed appropriately.

 

Good luck!

keane76
Obsidian | Level 7

Hi Vachichkenboy

 

Can you be more specific on what you want to do with OLAP cubes. In SAS we do have the OLAP cube studio, where you can visually design your OLAP cube, and then use it as is on the web tools or generate the corresponding MDX code and schedule it for a batch run.

 

Regards,

Keane76

keane76
Obsidian | Level 7

@rgreen33

 

Thanks for these detailed notes on setup in SMC, comes very handy..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1262 views
  • 5 likes
  • 3 in conversation