Showing posts with label Data analysis. Show all posts
Showing posts with label Data analysis. Show all posts

Wednesday, January 13, 2021

Install Python on Windows


Python is the most famous development language today.

This is because is open-source, scalable and robust. It can run in almost any device with a processor (or microprocessor, we will see that in coming posts) and is very simple to use and learn, even if you don't have any programming backgroud or experience.

Start using it is very simple and you can learn the basics in the official site.

 

Installing Python

For windows systems, you can download the latest version here.

Once downloaded, open the executable file as administrator.

Then, for the uses we will give on the articles on this site, please install it this way:

  • Make sure you have selected the add Pyton to PATH option - This will allow us to run the Python engine from multiple sources (As is intended for use on multiple applications).
 
  • Make sure you install Python for all users - This will allow usto run the script commands.
 
  • Select PIP feature - This is used to install adittional libraries and functionalities, one of the main reasons for using Python and benefit from it.

 

Run the Installer as administrator and select the Customize installation option:

 
 
In my case since I am just upgrading, the install for all users option cannot be changed, but make sure to select it the first time you install it.

 
 
If everything is ok, you will see this message after the setup completes.


Test it!

Ok, but how do we actually use it? 

There are plenty of integrated development environments (IDE) for Python, depending on what are you planning to do with it.

But just to make sure is working ok, just open a CMD window and type python, then you can start using it right away.



Ok, once you have checked everything is working as expected, a good IDE to start working with Python right away is to use VSCode, you can download it here.

Then, add the Python extension.


 

Python files ends with the .py extension, so for VScode, just create any new file, and then save it as .py extension, the IDE automatically will load the Python extension and you can start working with it right away!

 


Happy coding! Stay tuned for the next things we will buid using Python.

 



Thursday, November 8, 2018

Enable Machine Learning Services on SQL Server

R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.

You can learn a little more about what you can do in the SQL Server blog.

On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports.

To install it

You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python)



Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window.

Continue the setup as usual and finish it.

After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services.




I recommend you to patch the instance with the latest update available in case you encounter any issue with the service startup.


To configure it


Just connect to the instance where the Machine Learning Services was added, using your favorite tool, and run the following T-SQL to enable the code execution:


EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

You must see the following output:



After that, you have to restart the SQL Server service for it to work.

our last step is just to test it...


Testing the script execution


To execute scripts you must use the sp_execute_external_script procedure, you can find the official documentation here.

This is the most basic command I can think you can execute to see if everything is ok, displaying the license information of the R language:


EXEC sp_execute_external_script  
@language =N'R',
@script= N'license()'

Note that the first execution after a service restart will take some time to complete, but after waiting you should see the license information in the Messages window:


And now... a simple example with real data


For this example, we will take Application.Countries table from WideWorldImporters test database and display it sorted by population in descendent order with an R script, running this T-SQL:



USE WideWorldImporters
GO


EXEC sp_execute_external_script  @language =N'R',
@script=N'
dsTest<- InputDataSet
OutputDataSet <- dsTest[order(-dsTest$LatestRecordedPopulation),]
',
@input_data_1 =N'
SELECT 
 CountryName,
 FormalName,
 LatestRecordedPopulation,
 Continent
FROM [Application].[Countries]
WHERE Region = ''Americas'''
WITH RESULT SETS 
 (
  ( 
  [CountryName] [nvarchar](60) NOT NULL,
  [FormalName] [nvarchar](60) NOT NULL,
  [LatestRecordedPopulation] [bigint] NULL,
  [Continent] [nvarchar](30) NOT NULL
  )
 );
GO


If you have the database installed and run it, you can see the output with the data sorted from the R script:



You have learned how to execute simple R scripts against SQL server tables from the database engine, so now you can make more complex examples and do your own data analysis scripts.

For more in-depth information visit this link.

Tuesday, July 17, 2018

PowerBI: Implement a What If parameter

Our Guinea Pig Dashboard
PowerBI is focused on creating interactive, analytical reports, from a lot of different data sources and with the ability to access them via mobile devices.

One of the cool options that allow your reports to be interactive, is the What If parameters: they allow you to visualize how your data could/should behave under different scenarios.
On this post, I will show you a super simple way to use a what if parameter.

Note: This test was done using PowerBI desktop and the Import option in the data source selection.



We will use a simple Dashboard representing sales by region in a specific year, as this:



Let's say we want to plan next year sales, we want to add a line representing the next year target by region, for this example just as a value between 0 and 200% of this year sales.

Adding the What If Parameter


Click on the Modeling Tab and select the New Parameter option.



A new window will open, here you configure the values for your parameter.
For this example, we configure a decimal value between 0 and 2.
Leave the Add Slicer option checked to add the value selection box to your dashboard.


The parameter visualization is added, but as a multiple select option.



We change the type of slicer to Single value (you can select other option if you want).




Adding a new measure


We will demonstrate how the newly created parameter works using a measure, it will multiply the year sales by the value we select in the slicer.
Of course you can use the parameter in more complex formulas, that modify data values more in depth, that are not so obvious as our example, but for now, we will keep it simple.

We proceed to create a new measure on our data table, select the header of the table and select the New Measure option.



Then enter the formula you want to use, for our example the sum of the sales of the year, multiplied by our parameter, to simulate next year sales.



You can see how the measure is visualized as any other column in our table



Modify your visualizations


Now, just modify the dashboard by adding the new column to any visualization you want and save it.
Then you can change your slicer and the column will change its value in real time.

A simple analytical report with a few clicks :)


Sources:

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...