Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
How to migrate data model created in Analysis Services / Azure Analysis Services to Power BI Desktop?
I have a hundreds of measures and do not want to manually recreate all ones
Solved! Go to Solution.
Hi @Anonymous,
I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment.
1. Install the DAX studio from here.
2. Connect to the SSAS tabular and run the query below.
select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES
3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.
4. Copy a few cells in a sheet and create a very simple data model in Excel.
5. Open the Visual Basic window and paste the code below and run it.
Public Sub add_measure() Dim Mdl As Model Dim tbl As ModelTable Dim nRows As Integer Dim i As Integer Dim measureName As String Dim measureContent As String Set Mdl = ActiveWorkbook.Model Set tbl = Mdl.ModelTables(1) nRows = Sheet1.UsedRange.Rows.Count 'get the total rows For i = 1 To nRows measureName = Sheet1.Cells(i, 1) measureContent = Sheet1.Cells(i, 2) ' MsgBox measureName ' MsgBox measureContent Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1) 'insert a measure to the model Next i End Sub
6. Now, we can see all the measures in the model and we can import all of them into Desktop.
Best Regards,
Dale
Hi @Anonymous,
You can connect to the SSAS models. But I'm afraid you can't import the SSAS model and turn it into Desktop Model directly. BTW, if it's SSAS tabular, the measures are created with DAX which is also used in the Desktop. So you can just copy the measure and paste it in Desktop.
Best Regards,
Dale
Thanks Dale,
Ya, it is SSAS tabular. Can I do copy / paste measures in a bulk? No one by one, because it will take too long.
I know I can document measures with Dax Studio but can I then push them to pbix some how?
If I create new pbix file and recreate tables, then
If I change pbix extention to zip, I will be able to find file with a data model:
Unfortunatelly I am not able to open it. Can I paste measures there somehow?
Hi @Anonymous,
I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment.
1. Install the DAX studio from here.
2. Connect to the SSAS tabular and run the query below.
select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES
3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.
4. Copy a few cells in a sheet and create a very simple data model in Excel.
5. Open the Visual Basic window and paste the code below and run it.
Public Sub add_measure() Dim Mdl As Model Dim tbl As ModelTable Dim nRows As Integer Dim i As Integer Dim measureName As String Dim measureContent As String Set Mdl = ActiveWorkbook.Model Set tbl = Mdl.ModelTables(1) nRows = Sheet1.UsedRange.Rows.Count 'get the total rows For i = 1 To nRows measureName = Sheet1.Cells(i, 1) measureContent = Sheet1.Cells(i, 2) ' MsgBox measureName ' MsgBox measureContent Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1) 'insert a measure to the model Next i End Sub
6. Now, we can see all the measures in the model and we can import all of them into Desktop.
Best Regards,
Dale
Good afternoon
I'm having this error, when I put the vba code.
("Object doesn't support this property or method”)
Tried several ways, but I can not make it work, does anyone have a solution?
I believe it is in this line, but I do not understand what is wrong.
("Md1.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1) 'insert a measure to the model")
thank you very much
Hi @Anonymous,
I would suggest you start with a new Excel workbook. Maybe the objects are used. Please download the demo from that attachment. You can see it clearly.
Best Regards,
Dale
Thanks Dale,
The workaround works. Really appreciate your help.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |