danrofohio
New Member
- Joined
- Mar 14, 2012
- Messages
- 4
Hello,
I want to create a data model based using two Excel workbooks, then I want to save the data model and use it in other workbooks. Basically, I want to be able to add measures, columns, tables, relationships, etc. in the future and have all my workbooks using that data model reflect the changes I make. I think I need to create the data model in "Get and Transform Data" by clicking on Get Data/From File/From Workbook. Then in the pop up window, I select the first worksheet (tab) that the data is on, then click "Load To" button. On the next pop up window, I select "Only Create Connection" and check "Add this data to the Data Model".
Then I am going to the Power Pivot Window where I add the second workbook by clicking on "From Other Data" in the "Get External Data" section, selecting "Excel Files" at the bottom and going through the steps to select the correct table. However, I am never sure that Excel is adding that second file to the data model. Then in Power Pivot, I am adding columns, measures, and relationships between the files. Next, I click out of Power Pivot, and I never feel confident that my changes are being saved or added to the model since there is no message or dialogue to indicate that.
After that, I go back to my worksheet where I am creating the data model and bring up Queries and Connections. There I only see the first worksheet I imported listed under queries. Under Connections, I see listed ThisWorkBookDataModel and the second worksheet that I imported. I right click on ThisWorkBookDataModel, Properties, Definition, Export Connection File. When the Explorer windows pops up, I change the file name to "TestNewModel" and hit save.
After that, I create the new workbook where I want to use the saved datamodel, and I go to Get and Transform Data. From there, I click on "Get Data Using Existing Connections". In the pop up screen, I see "ThisWorkBookDataModel" under Connections on this Computer, but nothing with the name of TestNewModel, I saved earlier. (After repeating the test three times, I saw three "ThisWorkBookDataModel" datamodels listed with identical names.) After I click on that, I always get the same error message:
I want to create a data model based using two Excel workbooks, then I want to save the data model and use it in other workbooks. Basically, I want to be able to add measures, columns, tables, relationships, etc. in the future and have all my workbooks using that data model reflect the changes I make. I think I need to create the data model in "Get and Transform Data" by clicking on Get Data/From File/From Workbook. Then in the pop up window, I select the first worksheet (tab) that the data is on, then click "Load To" button. On the next pop up window, I select "Only Create Connection" and check "Add this data to the Data Model".
Then I am going to the Power Pivot Window where I add the second workbook by clicking on "From Other Data" in the "Get External Data" section, selecting "Excel Files" at the bottom and going through the steps to select the correct table. However, I am never sure that Excel is adding that second file to the data model. Then in Power Pivot, I am adding columns, measures, and relationships between the files. Next, I click out of Power Pivot, and I never feel confident that my changes are being saved or added to the model since there is no message or dialogue to indicate that.
After that, I go back to my worksheet where I am creating the data model and bring up Queries and Connections. There I only see the first worksheet I imported listed under queries. Under Connections, I see listed ThisWorkBookDataModel and the second worksheet that I imported. I right click on ThisWorkBookDataModel, Properties, Definition, Export Connection File. When the Explorer windows pops up, I change the file name to "TestNewModel" and hit save.
After that, I create the new workbook where I want to use the saved datamodel, and I go to Get and Transform Data. From there, I click on "Get Data Using Existing Connections". In the pop up screen, I see "ThisWorkBookDataModel" under Connections on this Computer, but nothing with the name of TestNewModel, I saved earlier. (After repeating the test three times, I saw three "ThisWorkBookDataModel" datamodels listed with identical names.) After I click on that, I always get the same error message:
Unable to open or read this connection file. Either the file has been damaged or the format is not valid.
What am I doing wrong? Is there a better approach to this that I should be using?
