Analysis :: Getting blank Value / If This Value Does Not Exist In Source?
Aug 5, 2015
I have a policy dimension with 100k rows. This dimension has an attribute called "Region". When I query source, I get 4 distinct values however when I drag this region attribute in excel pivot, I get an additional "blank" value in the result set.why am I getting this blank value, if this value does not exist in source? I do not see any property to hide this blank. I know you can do this in hierarchy using BIDS Helper but not for an individual attribute.
Sometimes, after cube processing this "blank" disappears automatically while next time (or sometimes) it comes back. At any point in time, there is only 4 distinct values for this attribute at source.When I remove all relationship that exist with this policy table in the model, this blank value disappears from excel pivot.
View 2 Replies
ADVERTISEMENT
Jan 20, 2006
Hi,
I use an excel datasource to populate some simple dimensions, but when i extract the excel file i get alot of blank rows from the excel files...
How can i overcome this issue? Is this normal?
I never had problems like this using DTS in the 2000 version
Best Regards,
Luis Simões
View 6 Replies
View Related
Nov 11, 2006
Hello,few users have problems with our application(ADP/MsAcces2000+SqlServer2000). Whenthey want to open a report which source is a stored procedure, they getmessage that "record source specified..... do not exist" - of course,it's not true.More details:- other users don't have this problem- they have WinXP- all users have the same permissions to SQL objects- I've checked Microsoft KB(http://support.microsoft.com/kb/243532/en-us) and they write about"dbo." before source name - I use that, so it's not a solution. Whatmore, in application there are lists which have SP as row source andwithout "dbo." and it works. (In lists a row source is defined as:"exec <sp_name>", in reports as: "dbo.<sp_name>").- computers have all service packs, new version of MDAC, etc.- I asked another user, who doesn't have this problem on his machine,to login on "bad" machine and he couldn't open reports too.Maybe someone could help? because I don't have more ideas.
View 1 Replies
View Related
Nov 28, 2007
Hi All, I've got a problem transferring objects between SQL Databases.
The source database is an SQL 2000 dbase, and the destination is a local SQL2005 instance. When trying to transfer I get the error:
0x80040E37 (table) does not exist at the source - but it does.
I am guessing its a schema related issue, I've tried setting the copyschema setting to true, but no joy.
The copyallschemas function doesn't work with an SQL 2000 box so its set to false.
Thanks,
Morgan.
View 1 Replies
View Related
Sep 23, 2007
Hello,
I am running a package that used to transfers data from one SQL2005 to another SQL2005. There are multiple schemas associated with the database. Until recently, this pacakage would work. Now I am getting the following error for all the tables not owned by dbo:
Any help on this would be appreciated.
Thanks, sck10
[Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source.
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600
View 5 Replies
View Related
Jul 31, 2015
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).
View 2 Replies
View Related
Jun 22, 2004
Hi ,
I have just downloaded an evaluation version of SQL server from the microsoft website and installed it in my PC.
Now I want to create file data source.
I followed these steps:
-From the control panel, ODBC datasource admin
-click the DSN tab
-click the add button
-select the sql server driver, enter the data source file name
-enter "local" in the server text box as I installed the sql server in my standalone PC.
-click next
After that I had this error.
"Connection failed. SQL server does not exist or access denied."
But The sql server service is running.
Can anyone help me where did my configuration go wrong??
Thanks...
Nov
View 2 Replies
View Related
Jan 17, 2006
Does anyone know what could be causing the error on Transfer SQL Server Objects Task? I tried to develope a SSIS project in the Business Intelligence studio to transfer table between databases on the same server. However, I have been getting the following error:
[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.
Is there a setting that I need to change to make this work? Thank you for your help.
View 31 Replies
View Related
Oct 14, 2004
I want the change the name of the Data source (see attached gif picture)
Thanks
View 1 Replies
View Related
Aug 28, 2006
Hello everybody.
I want to perform a Analysis Services Processing Task using a connection manager created from a Analysis Services data source. I create the data source using the Oledb provider for Analysis Services 9.0, and it works fine. Then I create a connection manager using the "New connection from data source" option, and it seems to create it ok.
The problem is that I can't then asign this connection to the Analysis Services processing task. I get the error:
"Connection Manager MyConectionManagerFromDataSource is not a Analysis Services Connection Manager"
However, if I create directly a connection manager using "New Analysis Services Connection" it works fine.
Is it at all possible to create a Analysis services connection from a Data Source to perform a Analysis Services Processing Task?
Thanks in advance,
View 4 Replies
View Related
Jul 6, 2015
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
View 2 Replies
View Related
Apr 10, 2006
Trying to go through the Analysis Services tuturial. Logged in as Administrator on a 64 bit W2K server. SQL Server 2000 AS and SP4.As I come to the Design Storage step I get a message sayingData source provider error: ; Time:2006-04-10 19:51:47There are no further details given and the Event Logger has nothing.Grateful for any help.Screenshot: http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem.pngAnd the version Screenshot:http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem2.png
View 7 Replies
View Related
Jun 12, 2015
MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.
View 4 Replies
View Related
Jul 14, 2015
I have a requirement to make all of my KPI goals configurable by the end user - I would like to add the data to my datawarehouse so that it is updated in the cube whenever the cube is processed. I'm thinking I will need a table for each KPI that includes two columns (I will be setting goal by year):
Year
Value
In my cube I then create a measure group for each KPI (for some I will add a calculations to break down the yearly goal to lower levels of detail).I will add each measure to my Dimension Usage and relate to each of my time dimensions at the Year level.I currently have around a dozen KPIs - my above approach seems a bit messy i.e. requiring a table and measure group for each KPI (I guess I could at least put them all into one table in my relational db).
View 2 Replies
View Related
Feb 21, 2007
Hello,
Our company is doing some basic research into using analysis services as a solution for our customers. I've taken our database and built an analysis services project in BI Studio and deployed it to Analysis Services on my local machine I connect to Reporting Services in Management Studio, create a folder for the cube, give my Windows user acct. Content Manager permission, create the Data Source (conn string = "Provider=SQLNCLI.1;Data Source=(local)SQLServer2k5;Integrated Security=SSPI;Initial Catalog=STI-PDB" -- copied from the Analysis Services project in BI Studio), and go to "Generate Model". What I get is an rsModelGenerationError, sub [Create Perspective From Cubes] with a connection issue at the base:
Either the user, <computername>Thomas, does not have access to the STI-PDB Database, or the database does not exist. () (Microsoft SQL Server 2005 Analysis Services)
Now, in the Database Engine, my Windows account has the db_owner database role for STI-PDB and it's default schema is dbo, which all the objects are created under. What am I missing here?
View 4 Replies
View Related
Jul 27, 2015
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B). Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
View 3 Replies
View Related
Apr 23, 2015
I have deployed my SSAS project to an Analysis services database on a SQL 2014 server instance using the "Development" build configration in Visual Studio. In order to avoid users accessing the development instance of the cube, I've created an additional "Release" build configuration and deployed my project to a different database on the same server. I've also created a production copy of the data source and changed the data source configuration of the production cube to point to this one.
I've provided the same domain service account on the "Impersonation information" tab of the data source for both the development as well as the production instance of the cube. This account has also been granted identical permissions for both data sources. While everything works fine with the development database, processing the production database fails with an error message saying that it cannot connect to its respective data source.
I'm using VS Ultimate 2013.4, SSDT 12.0.50318.0.
View 3 Replies
View Related
Jun 11, 2015
How to integrate current artifacts with TFS ?
For example : We have multiple applications X, Y, Z each has its own SSIS solution , SQL Script, MSTR report and documentation.
We move code from Dev to Test and then to PROD
How to structure the folders which will be easy to manage and easy to release ?
View 3 Replies
View Related
Jun 7, 2007
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
View 7 Replies
View Related
Jan 8, 2008
Hi ,
I am using sql server 2005 standard edition and i have installed MSXML core services 6 and also ADOMD.net for analysis services.
i am using visual studio 2005 version of 8.0.5072742 and the problem that i am facing is when i use to develop reports using Analysis services as a data source , the error message pops up and VS 2005 gets crashed.
i could define the dataset , layout part and when i prees the preview , it gets crashed.
could any one help me on this problem to sort out.
Regards
RMP
View 1 Replies
View Related
Apr 29, 2008
Hi,
I receive blanks for a column called value and i need to represent it as a blank or NA into a colum whose datatype is float in the datawarehouse.
how is this possible because in current schenario a blank is being converted to 0
which is not the right thing.
i would like to retain the blank in conversion from char to float ,
please explain me if it can be done or how to overcome this issue.
Thanks
View 1 Replies
View Related
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
View 1 Replies
View Related
Aug 20, 2011
I have SSAS 2008 Enterprise sp2 (not R2) running on a production server. One of the dimensions is defined as real-time Rolap - set to clear the cache whenever the table changes.
I placed triggers on that table to be sure, so I know no changes occur on that table sometimes for 10 or 15 minutes at a time - and yet I get 100's of change notifications (I noticed them on a profiler session). It seems as though the notifications come as often as the dimension gets queried.
I think this is causing one of my longer running queries to fail because of locking issues. I assume this is because the queries are getting killed when they're in middle of using an outdated cache.
The strange thing is that I cannot reproduce this on the dev servers. The main difference between the dev and production servers is that the production servers are behinfd a cluster. I don't know if that could make any difference.
I really need to know why I'm getting so many notifications.
View 2 Replies
View Related
May 15, 2007
Hi all,
Till now all the reports for our application was created in Reporting Services 2005 with SQL Server Database(OLTP) stored procedures.
Most of the reports take 10 to 15 report Parameters and all are multi valued.
Now because of the slow response time,we have decided to use OLAP cubes data source. We have created the OLAP cube in Microsoft Analysis Services.
Can someone tell me how to use the report parameters to query the Cube.
Any links,books for reference would be of great help..
Thanks,
Looking forward to your replies
View 3 Replies
View Related
Dec 3, 2013
I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
View 3 Replies
View Related
Nov 24, 2005
Hello all,
I have a branch a data, now i need to search through a database to check whther it is exist in that database or not, any syggestion?
Example:
Now i have data 123, 234, 345. Let say data 123 and 234 is exist in that database, but data 345 is not exist in that database.
What SQL am i suitable to use to get those result exist(123, 234) and those result not exist (345)?
The database structure is someting like this:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
3 345 345
1) The data consider 'exist' if it exist in column NAME or column O_NAME (either one).
2) The data consider 'not exist' if it not exist in neither NAME column nor O_NAME column
After the SQL query, someting is expected:
Exist:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
Not Exist:
ID NAME O_NAME
-- ----- --------
3 345 345
I'll appreciate if anyone can provide me a solution..Thanks!
View 3 Replies
View Related
Mar 5, 2015
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
View 10 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
Jun 12, 2015
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income (
ID int not null identity(1, 1)
[Date] datetime not null,
GoodID int not null,
PriceDeliver decimal(18, 2) not null,
PriceSalse decimal(18, 2) not null,
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID )
)
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
View 2 Replies
View Related
Feb 18, 2013
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies
View Related
Nov 24, 2015
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
View 2 Replies
View Related
May 30, 2015
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies
View Related
Aug 6, 2015
I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies
View Related
