Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Casey_Harless
New Member

Autotask Data Warehouse Integration

As a person with little database experience, and first time user to PowerBI, I would like some lay-person-level advice on how to import data from Autotask. Autotask has a Data Warehouse that, according to them, "Any reporting tool that can access Microsoft SQL Server views can use the Autotask Report Data Warehouse." (source) It seems the AS Connector is running into an issue and doesn't seem to be much of an option (I am in contact with Autotask about this). I cannot imagine this is an obscure request, perhaps there is already a work around?

 

1 ACCEPTED SOLUTION

@Casey_Harless AutoTask won't be able to assist you, as you will need to build a Tabular model and deploy it to an SSAS (SQL Server Analysis Services) instance in your domain. The AS Connector then points at that SSAS instance.

If you are trying to use the connector - you will need to leverage the additional technologies Visual Studio (SQL Server Data Tools) and have a SQL SSAS instance.

I have been unable to direct connect to AutoTask from Power BI Desktop, so I had to pull the data I wanted into my own database using SSIS (Sql Server Integration Services - also a Visual Studio thing). I did this also so that I can leverage other company information as well. Then I built a tabular model, and deployed to my SSAS instance. Then I connected the AS Connector to my SSAS instance.

 

That is alot of info, so feel free to ask questions.

 

TLDR; You can't just hook up the AS Connector to AutoTask. It's not a model, and not in your domain.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

22 REPLIES 22
Mukhliskhoir
Regular Visitor

hi, i want to make an olap datawarehouse from oltp live database, i have to make ssis for data integration and ssas for analysis, and power bi for dashboard and reporting. I ask for help, suggestions and solutions. Thanks.
charliemaclean
New Member

Hi Casey

 

I use Autotask datwarehouse and have managed to use the SQl connector to import directly to my power Bi giving me the custom dashboards I need. If this is still somehting you are having issue with then I am more than happy to do my best to help.

Hi Casey,

 

I am looking at integrating AT with PowerBI and was seeking some help in howbest to do this? Are you able to give me sone guidance?

 

Kind regards

 

Marc

Hi Marc,

 

Power BI can be connected directly to the AT Data Warehouse although this is not recommended by Autotask due to query speed and delays.  The connection process is pretty simple :

 

Get Data -> SQL Server ->

 

Server Name : As provided by Autotask - they use the default port of 1433 so not required to be specified

Database Name : As provided by Autotask - this *must* be entered

 

Username : As provided

Password : As Provided

 

Note :- This can only be used from a limited number of locations, ensure your public IP is not changing.  Consider how the design of the solution works to promote automation where possible.  Consider a separate SSIS instance to periodically pull down the latest data

 

 

"Autotask datwarehouse and have managed to use the SQl connector to import directly..."

Can you please explain the process (preferably in steps) and tools/technologies required. A simple sample app can help a lot if you can upload.

 

Many Thanks

@HHassan The Autotask warehouse is based in SQL Server. If you want to, you could directly connect from Power BI to that SQL Server, provided you are paying for the access to the database.

Get Data -> SQL Server -> Enter the Server -> Select your provisioned Database -> Enter SQL Credentials and you can access your views.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer Thanks for the details.

I just wanted to confirm: Following the Get Data, where do you get the option of SQL Server?

Did you mean: Get Data -> Databases -> SQL Server Analysis Services?

@Pierre1007 My guess is that you are in the Power BI Service. You need to use the Power BI Desktop. Selecting "SQL Server" from the dropdown, should look like this:

sql.png


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi Seth,

 

We have the AT Report Data Warehouse working well with Power BI Desktop but when we publish to Power BI Service we are having trouble refreshing the data (probably becuase the Azure IPs are not whitelisted). How did you get around this?

 

Thanks

@HaydenR@MarcDoran Due to having to whitelist IPs to Autotask we don't directly hit it with Power BI, because that just adds to the headache. We used an SSIS process to ingest the information we wanted out of Autotask and imported it to a local SQL DB. Then we connect to that from Power BI. The frequency doesn't matter because the Autotask warehouse is only updated 1x per day anyway.

So, my recommendation is to extract it from Autotask and push it to a database that you own. Then you won't have any issues trying to directly connect.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

thanks a lot Seth

@Seth_C_Bauer That's great. Thanks for the help.

"Autotask datwarehouse and have managed to use the SQl connector..."

Can you please explain the process (preferably in steps) and tools/technologies required. A simple sample app can help a lot if you can upload.

 

Many Thanks

@Casey_Harless AutoTask won't be able to assist you, as you will need to build a Tabular model and deploy it to an SSAS (SQL Server Analysis Services) instance in your domain. The AS Connector then points at that SSAS instance.

If you are trying to use the connector - you will need to leverage the additional technologies Visual Studio (SQL Server Data Tools) and have a SQL SSAS instance.

I have been unable to direct connect to AutoTask from Power BI Desktop, so I had to pull the data I wanted into my own database using SSIS (Sql Server Integration Services - also a Visual Studio thing). I did this also so that I can leverage other company information as well. Then I built a tabular model, and deployed to my SSAS instance. Then I connected the AS Connector to my SSAS instance.

 

That is alot of info, so feel free to ask questions.

 

TLDR; You can't just hook up the AS Connector to AutoTask. It's not a model, and not in your domain.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hello, please would you mind providing details of the connector you used to get the data from Autotask via SSIS into a SQL database, I have a similar use case I need to explore, thanks in advance.

@Seth_C_Bauer Thank you very much for the reply! I've used what you explained as a starting point and have spent some time getting familiar with the tools you described. What additional steps need to be taken to automate this process (going from the Autotask Warehouse to the the SSAS instance to the the Power BI application)  on a daily or weekly basis?

@Casey_Harless If you are following the above, then next steps would be something like this:

1) Get data from AutoTask into your database - Your SSIS process can be a scheduled SQL Agent Job

2) Create your tabular model based on the data in your database / Deploy model to SSAS Tabular SQL Instance

3) Create SSIS process to update your model - Schedule refresh of model by using SQL Agent Job

4) Download the PBI Analysis Services connector on to the instance of your SSAS Tabular model (Can be anywhere in your domain, but if you do it on the SSAS Tabular instance you don't have additional hops across your network.)

5) Connect to model in desktop or service

6) Data in your reports is automatically refreshed whenever you update your model with the SQL Agent Job

 

The great thing about the AS Connector is that if you connect to the model in the desktop tool, when you push that file to the Service the connection stays "live", it doesn't treat the PBIX file like an on-prem source like all the other datasources. It inherits the connection to the model and continues to just send DAX queries back and forth.

 

You can schedule the update of the model as often as you want, and I don't see why you couldn't create 1 SQL Agent Job with 2 steps. The first to load the data from AT, and the second to update the tabular model.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_BauerThis does not appear to be a "solution", rather a high level framework that has not been tested or verified by any other community member to date.

 

The first step #1 has no information on how to get the data from AutoTask (a remote SaaS service).  That's the hard part that got completely skipped over.   No data,  then all the other theortical infomation on analyzung the data is not helpful.

 

How are you actually connecting to and pulling the AutoTask data into PowerBI?   REST Query, support ticket to export data to tab files, etc.???

 

 

@AgileJohn If you read the begining of the thread we discuss that data is accessed via the Autotask provided Warehouse...

To restate:

#1 - Autotask data is accessible via their Warehouse. From my understanding this may be an additional fee. They grant you access to this SQL instance where you can access a layer of Views in the Database that is assigned to your Org. This access grants you the ability to use SSIS to automate the extraction of data into your own environment to manipulate/control and generally do as you will with it.

 

As to your "solution" comment, you are correct, this is a high level framework that outlines how the initial poster can access data in Autotask, as well as utilize a Tabular model for use with the On Premises Data Gateway (Called the Analysis Services Connector back in 2015). You don't have to use a tabular model, and you can choose to connect to the warehouse directly if that suites you. And since we're just talking about how to connect to a data source to extract information, I don't see how testing/relevance has anything to do with this.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thank you for correcting me.  I was not aware that AutoTask had a direct DB access option.   I've contacted them and found this is $250/mo and limited to 3 IP addresses.

 

Thank you for sharing!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors