how can I retrieve translated attributes using mdx query. I have translated my dimension's attribute in French(France) but not getting it in caption column. I used below query
SELECT * from $SYSTEM.MDSCHEMA_LEVELS
LEVEL_CAPTION column has same value as LEVEL_NAME. I ran this query after changing default language of analysis service to French(France).
How can we select translated text of cube translations, like measures, dimensions, dimension attributes. I have below query which return translations of dimension attribute's memebers
WITH
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]
I am getting translations of product category members but I want to get translated text of "Category"?
Is there any way to retrieve the weight % of some values?
A quick example:
A 10 -> Result i want is 10 / 60 (60 is the SUM of A,B and C)
B 20 -> 20 /60
C 30 -> 30/60
Code for number of subscriptions
EVALUATE
SUMMARIZE('Subscriptions',
'Subscriptions Type'[DS_SUBS_TYPE],
"Number of Subscriptions ",'Subscriptions'[NR_SUBSCRIPTIONS]).
I got a measure that is a calculated member Purchase-Invoice that give the NotBillable. When I query the cube in an Excel pivot I retrieve the zero as well:
I tried to create another calculated member in this way
If
([Measures].[Not_Billed]=0,blank(),[Measures].[Not_Billed])
but Error 2 MdxScript(Billing) (11, 5) The provided expression is not a valid MDX expression. 0 0
Good morning. I have an interesting scenario where one of the requirements of the application is to translate an application to different languages. Modelling the system to translate the UI went over without a hitch, however, we have now been told that the application will need to translate all the existing database tables. Has anyone ever had to do anything like this?
I was curious if anyone out there has any suggestions on how to approach this issue... ways in which it could be modeled, alternative approaches, anything at all would be appreciated. Many thanks in advance!
I'm importing data from a flat file into a SQL Server table. Yes, I have this thing mapped correctly, data converted correctly, data scrubbed correctly. I've tested this against 14 successive files dumped from the source system and everything works perfectly...until today. Now I'm getting the following gobbledygook.
I have absolutely no clue where to even begin looking. I've already inspected every single column in the input file that is converted to something other than a string. Absolutely nothing shows up as being an overflow or truncation issue. I've calculated the maximum length of every single text column being imported and none of them overflow the destination either. Where do I even start in this completely uninformative junk to even try to figure out where the issue is?
Error: 0xC02020C7 at Load Tables, MLBAMBio [1656]: The attempt to send a row to SQL Server failed with error code 0x80004005.
Error: 0xC0047022 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "MLBAMBio" (1656) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020C7. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread7" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0202009 at Load Tables, MLBAMBio [1656]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E57.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E57 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E57 Description: "String or binary data would be truncated.".
Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread7" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread9" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread9" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load Tables, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Load Tables, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Load Tables, Biography [951]: The processing of file "D:FTPtransfersourcecurbio.csv" has ended.
Error: 0xC02092B5 at Load Tables, MLBAMBio [1656]: A commit failed.
Error: 0xC0047018 at Load Tables, DTS.Pipeline: component "MLBAMBio" (1656) failed the post-execute phase and returned error code 0xC02092B5.
Hello Everyone,
I have defined a cube with translations on every dimension and measure group.
when I query this cube from BIDS, the captions of each column have text in the right language (whatever is chosen in BIDS at the time of query)
Now, my customers will be sending queries from SSRS. when i fire the MDX query from SSRS I want to extract the MEMBER_CAPTION dimension property (I think this will contain the translated text for each columns header)
My question is how can I extract this from SSRS?
regards,
Abhishek.
Code page translations are not supported for the text data type. From:1252 To: 950.I would like to know what this message means. I also installed thelanguage packs in advanced settings, Everything is set to English. Mywindows XP computer is XP English. For some reason I cant get an updateto go thru using ADO. like Recordset.UpdateAlthough other routines using .update workAny ideas?
View 6 Replies View RelatedI 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.
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,
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 ?
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies View RelatedI 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.
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 RelatedI 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 RelatedHi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedI am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
View 3 Replies View RelatedCould you help me out?
I am interested in retrieving certain record among outputs.
For example, if I use the following sql,
select * from info order by name asc
====================================
then I can retrieve 25 rows from info table.
What I want to do is that I want to retrieve 15th record only among possible 25 records.
How can I solve this problem?
Thanks in advance.
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
Hi all,
I want to retrieve the second maximum value of a column data present in SQL table.Please help....
A
----
10
25
23
15
here I want 23 as the result.
Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advance
i have a table called tblpictures which look something like this..
filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3
is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..
hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"
I want to get the same using bcp ,so that i can populate into a file
something like
set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd
I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Thanks.
Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanks
Hi, all.
I tried to get result of sp.
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.Open sp, cnn
doesn't get result.
when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex.
But, still I think it should work!
My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update
--- list is one string separated by '&' delimeter
--- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
CREATE procedure spGetListOfTableToDownLoad
@listOfUpdateTime varchar(500)
as
SET NOCOUNT ON
Declare @listOfTable varchar(300), @item varchar(300)
Declare @tbl varchar(50), @uptime datetime
Declare @list varchar(500)
Declare @sep varchar(1)
SET @list = ''
SET @sep = '&'
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT
FROM cur
INTO @item
Declare @re bit, @tp varchar(50)
WHILE @@FETCH_STATUS = 0
BEGIN
-- get tablename, update time
Declare cur2 CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@item, '=')
OPEN cur2
FETCH NEXT FROM cur2 INTO @tbl
Print @tbl
FETCH NEXT FROM cur2 INTO @tp
print 'tp:' + @tp
SET @uptime = CAST(@tp as datetime)
print @uptime
-- @re =1: true, 0: false
EXEC spIsUpdate @tbl, @uptime, @re output
IF @re = 1
SET @list = @list + @tbl + @sep
CLOSE cur2
DEALLOCATE cur2
FETCH NEXT
FROM cur
INTO @item
END
if LEN(@list) > 0
SET @list = LEFT(@list, Len(@list)-Len(@sep))
CLOSE cur
DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO
__________________
--- PARAM:: @tbl: table name,
--- @uptime : update time (passed from local db) that will be compared on HQ table
--- return 1 if Max(UpdateTime) of @table > @uptime
--- otherwise return 0
CREATE Procedure spIsUpdate
@tbl varchar(50), @uptime datetime, @result bit output
as
BEGIN
Declare @bit bit
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
declare @uptimeHQ datetime
/* Build the SQL string once.*/
SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
SET @ParmDefinition = N'@tp datetime OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime
SET @result = 1
ELSE
SET @result = 0
--RETURN @bit
END
GO