Power Pivot :: SSAS Tabular Live Connection Doesn't Work If Visual Studio Project Is Not Open
Sep 29, 2015
I have a ssas tabular model as a data source in a .pbix-file. I have uploaded the .pbix-file to Power BI Services and it works fine as long as my visual studio project is open. If I close my visual studio project, the objects in Power BI Services will say "The data recievement for this visual information was not successful."
I am trying to implement some powerview report in excel 2013 itself (not in sharepoint).
I have implemented a SSAS tabular model and deployed to server. Now my aim is to load this tables in the SSAS tabular database into powerpivot so that i can use this data for creating powerview reports. However when i tried to import data from SSAS, it asks database and MDX query. My requirement is to load the dimension and fact tables to power pivot as it is.
We have an Excel PowerPivot model that we are trying to move to SSAS Tabular. The model is complex and has over 300 measures. To group these in Excel, we pasted in dummy tables and defined the measures in logical groupings. Now when we are migrating to SSAS, we have to delete the pasted tables --that have ALL of the measures, in order for the import to work successfully.
How to migrate the model? We have a nice report of all of the measures from DAX Studio, but I am not looking forward to manually re-entering 300 measures through the user interface. Is there any way to define measures in batch? Or is there a way to change the pasted table to a file so the import will work? Will the import work in SSAS 2016?
I have a PowerPivot table setup based of a SQL Server stored procedure. I am kind of surprise that when I click the Refresh button on the PivotTable Tools ribbon bar above the report the report does not refresh. I have to open the PowerPivot window, click refresh and then go back to the worksheet with the PivotTable and click Refresh. Is that by design? It seems like the Refresh on the worksheet just gets the existing data from the table.
i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.
create endpoint testEndpoint authorization [FINTAN-PCFintan] state=STARTED as http ( path ='/sql', authentication=(INTEGRATED), ports = (CLEAR), site='SERVER' ) For SOAP ( webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'), WSDL=DEFAULT, SCHEMA=STANDARD, DATABASE='adventureworks', NAMESPACE='http://thenamespace.org' ); GO
the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?
Is it possible to config environments ( dev,tst,prd ) in VS Tabular solution , like it is done in SSIS ? i find Variable tab , but i dont find the way to set a connection (string) to be change dynamically between the environments.
I have created a tabular model with VS 2013 Ultimate. I deployed the model several times and everything works great with Power Pivot.
One of the dimension tables, "Age", has an integer field that is null. I updated that column in SSMS to have values, processed the table in VS and the "Age" column now has integer values.
I deployed the model again, connected with Power Pivot, and the "Age" column still has not data. I refreshed the Excel spreadsheet several times, started a new one, deployed several times and there is still not data in the "Age" column even though it clearly exists in VS.
I opened SSMS on the Analysis Service and queried the table in question and it also shows no data in the Age column even though it is in the data table and the VS model.
I can't find anything different about this column than others that contain data. How can this be happening?
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
how to combine measures and fields coming from an analytical model (tabular) along with some Excel calculations. Basically I want to provide users with a simple report (to be displayed in SharePoint Excel services) containing charts and slicers. The data comes from a tabular model, and most of the calculations are in the model as well.However there is some little tweaking that must be done. For example I might need additional calculated columns, but I don't feel the need to modify the tabular model for that. I was wondering if I could do this within Excel as well -- but without having to bring all the data through a pivot table, then manipulate it and then show it on the report. So to be clear I do not want any pivot tables lying around, even if on a hidden sheet.
I noticed that when selecting a pivot chart in Excel, at the ribbon menu under "PIVOTCHART TOOLS"/"ANALYSE" there is a group of buttons named "Calculations". One of them is named OLAP Tools.Is it fair to assume that these options will allow me to create new measures at the Excel side, without affecting my tabular model?
I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.
What might be the issue in such a small database? I would understand in a big database, but not on this one.
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
I have visual studio 2005 and sql server 2005 with integration service installed on my machine. Couple of days ago, I installed visual studio 2008 professional. When I go to create SSIS project I dont see it in visual studio 2008. What do I have to do to make it appear in visual studio 2008 so that I can create SSIS projects.
I have a projectstructure (DimProject) with FactDeliverables assign to to the bottom level of the Projectstructure. I would like to show the DimProject that doesn't have any FactDeliverables.
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer. The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.
This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc.
I'm using a Contoso database running on Power BI Desktop (Version: 2.28.4190.122). The databiase is actually a Contoso.pbix file provided for a book [URL]. I have a DAX query that summarizes a Products table by Product[Manufacturer] and gives a count of products by the manufacturers.My Problem is that the ORDER BY COUNTROWS doesn't work.
why the order by clause doesn't work when I'm confined to the Products table while the order by clause does work when I start with the Sales table? The numbers are correct in both queries.
This query doesn't sort
EVALUATE SUMMARIZE ( Product, Product[Manufacturer], "ProductCount", COUNTROWS ( Product ) ) ORDER BY COUNTROWS ( Product ) DESC
I'm having trouble with CLR based user-defined functions.
When I create a database C# project in Visual Studio 2005, the dialog to pick the database connection doesn't apprear. Except for that, the project seems to be created normally. I am able to write and build my project without a problem.
When I try to release, it says that the connection isn't defined. I do a Project ==> <projname> and then select the database tab on the side. I am unable to type in a connection string because it's grayed out. When I hit the browse button. Visual Studio crashes.
I have one thrid-party Visual Studio installed, Sybase PowerDesigner. I also have the SQL Server 2000 and 2005 client tools installed.
I tried uninstalling the PowerDesigner module. I tried reinstalling and repairing the SQL Server client tools and Visual Studio 2005. I tried uninstalling SQL Server 2000 and 2005 client tools and then intalling Visual Studio 2005 fresh.
Nothing seemed to fix the problem.
Has anybody seen this before? Any ideas on how to fix this? I found a work around to install the dll from SQL Server, but I would really like to deploy from Visual Studio.
Given a model in Power Pivot with a number of KPIs that are hidden with the 'hide from client tools' option. After restoring the model in SSAS and connecting to the model from Excel, the KPIs are visible under a separate category/table "KPIs".
Other hidden items in the model like columns and measures remain invisible.
Is this normal behavior and what can I do about this?
i need a regex support in SQL 2005 server. i've downloaded SQL regex support project from http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx?loc=en. but in order to enable regex support i needed to open VS 2005. compile the project and deploy it to SQL server. now i can use Regexp-s in SQL queries. However this is not elegant way to deploy everytime SQL Database project via Visual Studio to every MS SQL server i want. my question is : how can i deploy without using Visual Studio?
I have implemented SQL Server 2005 Developer Version at Windows XP Home. I'm working on localhost.
I want to do an assoziation analysis and creating data source and data source view is successful. But when the data mining wizard comes to the point where to choose the data mining algorithm, the visual studio crashes- the application is not responding. No error code is given, so I don't know where to start.
Analysis Server is running and the connection to the data source is made.
This may be a silly question but I've got a .NET 2.0 application with several projects/libraries. I'd like to have the benefits of a VS 2005 Data Project (deployment, code snippets, etc.) but I can't figure out how to take an existing C# project and allow it to also be a Database project (there should be the extra "Data" tabe in the project's properties).
I am unable to open e new project using SQL Server management Studio Express. On the File menu it displays only "Query with current connection" and "Data base Engine Query". Please help me on thi Do I have to Install SQL Server management Studio instead of using express version. Cant I use SQL Server and SQL server management studio for deploy to make a small database for my a wharehouse. Please help me
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
I created several packages (MyProjectPart1 - MyProjectPart9) which I execute on the server directly(right-click package in integration services and execute). These packages alI deployed the server with logging and configuration files. Before deploying I edited the configuration file (changed all dependent file names and sql server name from default on my workstation to those unique to the server I'm deploying it to) and then ran the manifest file which pushed it onto the server. I then ran the packages from the server an it ran beautifully. I then tried to build a package which executes all the packages in the correct order (by adding 9 execute package tasks). The dependen files are on the E: emp folder on the server.
I have included the error messg...but it looks like it's looking for dependent drives on the C drive on my workstation. I don't get this? How do I supply configuration files? Why is it executing differently when I call it from package execute task and when I execute it on SQL Server
SSIS package "RUN_MY_PACKAGES_ONSERVER.dtsx" starting. Information: 0x40016041 at ProjectInfo_NET_SSISA: The package is attempting to configure from the XML file "C:TempMyProjectPart1-03-2007-MyProjectPart1.dtsConfig ". Error: 0xC001404B at MyProjectPart1, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070015. The device is not ready. Error: 0xC001404B at MyProjectPart1, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070015. The device is not ready. Error: 0xC001404B at MyProjectPart1, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070015. The device is not ready. Error: 0xC002F304 at Drop Unecessary Tables, Execute SQL Task: An error occurred with the following error message: "The device is not ready.
". Task failed: Drop Unecessary Tables Warning: 0x80019002 at MyProjectPart1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Task failed: Execute Package Task MyProjectPart1 Warning: 0x80019002 at RUN_MY_PACKAGES_ONSERVER: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "RUN_MY_PACKAGES_ONSERVER.dtsx" finished: Failure.
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010 eibcactive.xls', 'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
To start Business Intelligence Development Studio, We need to go to the Start menu > Programs > Microsoft SQL Server 2005 > Business Intelligence Development Studio.
But when I go thru the procedure mentioned above, it gives me the following message. "Missing ShortCut, Windows is searching for devenv.exe. Please locate if u know the location".
I'm am deploying an application that uses SQL Express and wish to also deploy SSMSE along with it.
I have the .msi but I don't know how to get the setup project to deploy this as it does SQLExpress and .Net Framework 2.0. (and I am using VS 2005 SE)
There is an MS article on creating "Custom Prerequisites" (http://msdn2.microsoft.com/en-us/library/ms165429(VS.80).aspx) which speaks to creating product and project manifests.
Has anyone does this for SSMSE? Or am I going about this in the wrong way?
I have installed sql server 2005 and visual studio 2005 on windows server 2003. When I select business intelligence studio from sql server, visual studio editor opens up,but i cannot find business intelligence projects template in visual studio. Please suggest some solution or workaround.
I am connecting to an Access data source which is password protected. But when I set up the connection in PowerPivot what User name do I need to use for the connection. I have tried various log on user names but non have worked.
i export PowerPivot from Tabular "Analyse in excel" and i have a connection to the temporary tabular DB of my own user. when i close the Tabular Solution my DB disapire and it is not possible to work. i sopose that i have to create to the steady DB.
My PowerPivot report is using quite large data that can be split by subsidiary. I’d like to let user select subsidiary. The selection would trigger change of the PowerPivot’s data connection so that the SQL would change into “SELECT
* FROM dbo.ECSPURSL00 WHERE Subsdiary = 'selected subsidiary'”.
The Excel 2013 has Model class that should manage the PowerPivot data sources. The object explorer shows CreateModelWorkbookConnection method, but I can't figure out the VBA syntax.