Powerpivot data through SQL Server

[zuresh82]

Dear All,
I have generated the Power pivot from SQL data directly from SQL server. Some of tables contents duplicate/repetitive items and Power pivot not allowing to create the relationship between tables. Even i may not able to track which item gets duplicates. Is any one facing this issue? What is the solution?

Secondly, If i create the powerpivot data coming from server more than 1 mn rows which gives almost 80mb file. Can anyone suggest the solution to share this file through sharepoint/onedrive?

 

[ImkeF]

Many2many relations in PowerPivot are solved by using a bridge table (A mystifying and awesome solution for many 2 many « PowerPivotPro)

You can easily create this in Power Query (free COM add in). Simply create a table there on the SQL source tables, that will have the complete list of items. If they stay in more than one table, you can use an “append” operation to retrieve those values from more than 1 table.

Select the field with the key values and choose “remove duplicates”. Load it directly to the data model (if you’re using 2013). By default this will refresh every time you open the workbook.

If you’re using 2010, load to Excel and link the resulting table to your data model.

Imke

 

[zuresh82]

Dear Mr Imke,

Thank you so much...it works now.I can connect with various tables by using this option...

Also, i have raised one more issue....My SQL tables contents 2Mn record from sales table and if im using powerpivot file size is coming 185mb. This size cannot be shared and impossible to open in onedrive. Is there any possibility to reducing the powerpivot size without disabling the slicer benefits and graphs...

Many2many relations in PowerPivot are solved by using a bridge table (A mystifying and awesome solution for many 2 many « PowerPivotPro)

You can easily create this in Power Query (free COM add in). Simply create a table there on the SQL source tables, that will have the complete list of items. If they stay in more than one table, you can use an “append” operation to retrieve those values from more than 1 table.

Select the field with the key values and choose “remove duplicates”. Load it directly to the data model (if you’re using 2013). By default this will refresh every time you open the workbook.

If you’re using 2010, load to Excel and link the resulting table to your data model.

Imke

 

[ImkeF]

As far as I know, you can only significantly reduce size if you reduce the data.
In columnstore index it is particularly effective to reduce the number of columns with distinct values. So please check if you really need every field that you've imported from your source systems.

I'm no expert in SharePoint so lets hope that one of the other experts will have a look here and come up with some suggestions