cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbobadilla Frequent Visitor
Frequent Visitor

Database capacity when enabling Historic Data Analysis - Streaming

Enabling Historic Data Analysis creates a sql azure database with all records pushed it from an application.

 

I want to use this option, i'll push 50.000 rows/day. Are there any limitations about the maximum size of the stored database table ?

 

I read here about rest api limitations, and it says that i can save at max 5M rows stored per table. Anyway, i'm not going to use the rest api via code, instead, i want to use the easily UI into PBI Service. Do both cases have same limitations?

 

If this is the limit in the intern database generated, my question is: what would happen after the row #5.000.000??

Does this act like a LIFO queue, deleting older rows, and inserting the new ones, or what?

4 REPLIES 4
Community Support Team
Community Support Team

Re: Database capacity when enabling Historic Data Analysis - Streaming

Hi @rbobadilla,

 

>>i'm not going to use the rest api via code, instead, i want to use the easily UI into PBI Service. Do both cases have same limitations?

In fact, easily UI also based on rest api(it will convert page records to request with specific rest api), so rest API limitation also works for it.

 

>>If this is the limit in the intern database generated, my question is: what would happen after the row #5.000.000??

 

Nope, I don't think it will auto replace old data, it will return command error message to alert request amount are over the limit.

Common REST API Error Codes

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
rbobadilla Frequent Visitor
Frequent Visitor

Re: Database capacity when enabling Historic Data Analysis - Streaming

Hi @v-shex-msft, thank you for your answer.

 

I found here some information

 

https://community.powerbi.com/t5/Service/History-Data-Analysis-option-in-Streaming-dataset/m-p/38027...

 

There is a "FIFO Dataset" option.


Coding i can edit this option, but using the UI actually i don't know how to change the policy restriction to became to a FIFO dataset.

 

Any idea of this?

Regards

Rodrigo

Community Support Team
Community Support Team

Re: Database capacity when enabling Historic Data Analysis - Streaming

Hi @rbobadilla,

 

>>Coding i can edit this option, but using the UI actually i don't know how to change the policy restriction to became to a FIFO dataset.
I don't think this is a editable option(for easily UI) and also not found any related document told how to change this.

 

BTW, current power bi rest api not contain any update methods for rows, you need to delete old records and add new records to achieve update operation.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
mthsdiniz Frequent Visitor
Frequent Visitor

Re: Database capacity when enabling Historic Data Analysis - Streaming

I'm using the following code to push my data do the API:

 

(New-Object System.Net.WebClient).Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
Add-Type -Path "Oracle Path"
$datasource = 'DataSourceName';
$username = "*******"
$password = "*****"

$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$query = "sql"

##you would find your own endpoint in the Power BI service
$endpoint = "API ENDPOINT"

#Fetch data and write out to files
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString);
$connection.open();
$command=$connection.CreateCommand();
$command.CommandText=$query;
$reader=$command.ExecuteReader()
$objects = @(
while ($reader.Read()) {

[pscustomobject]@{
"API COLUMNS" = $reader['Database_Column'];
}

}
)
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($objects))
$objects.Clear()
$connection.Close();
$connection.Dispose();

 

What should i do to delete my dataset before posting it? (I've tried this link but i could not manage to do it https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_deleterows)

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors