Analysis :: Translate Named Sets
May 19, 2011I have some Named Sets that I want to translate. I have done exactly the same way as with the Calculated Members but the Sets don´t use the translations? Any ide why?
View 2 Replies
ADVERTISEMENT
Analysis :: Adding / Removing / Modifying Named Sets
May 25, 2015Is it possible to add, remove or modify a named set in an already deployed cube? I don't want to modify anything except the sets. If it is possible at all, are there any example of doing it?Second is technical. I have redefined set "myRowsSet" to "myRowsSet2". What I did is I removed the calculated members and placed their code directly inside the set. Why isn't it working?
with
-- road bikes in france
member [Product].[Product Line].myMemberRoad as aggregate(
existing(
intersect(
[Product].[Product Line].[Product Line].members
,[Product].[Product Line].&[R] -- road
[code]...
Named Sets???
Dec 18, 2004Hi,
Can someone briefly explain which this feature of Analysis services is and how canit be of use.
Also, besides BOL, I'll appreciate if someone can recommend a good reading on this.
Thanks.
Analysis :: Partitioning Sets Of Data Dynamically
Jul 17, 2015I have a situation where i have a transactional fact table which consists of date, row type, order number and value. Simple example below
Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90
What I need to be able to do is somehow select based on a day, the total value of open orders. I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.
Analysis :: Two Sets Specified In UNION Function Have Different Dimensional
May 14, 2015I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".
WITH
MEMBER [Measures].[Data Value1]
AS [Measures].[Average Value]
SELECT
{UNION(
([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data
[code]....
Analysis :: Create A Calculated Set From Union Of Values In Two Sets?
Oct 26, 2015I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
Cost Centre
Location
Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up with
Division
NewSet
Dollars
AA
CustomType1
$166.64
AA
CustomType3
$64.24
BB
[code]....
Analysis :: Using Named Set To Filter On Dimension Attribute
Jun 4, 2015I am unable to find solution for the problem while writing a Named Set in my cube.
I have a calculated measures which gives me difference in Sales in PERCENTAGE (%).
When I try to filter out those product codes which went a less than 5 %, I get no records.
I have also tried to filter direct values lets say - Products with sales > 100000 which is working fine.
Following is sample of my Named Set
FILTER([X].[Products Code].members, [Measures].[Diff in Sales]<5)
I believe as the values are in percentage, I am facing this issue.
Analysis :: SSAS Named Set With A Two Dimension Attribute Tuple
Jul 15, 2015In my SSAS Cube I have created a dynamic named set "top 10 e-learnings by language" which consists out of a set of tuples. Each tuple has two attributes out of the same base dimension "training": attribute 1 is "sprache" (language) and attribute 2 is "training text".
CREATE DYNAMIC SET CURRENTCUBE.[Top 10 eTrainings pro Sprache]
AS Generate(
{ [Training].[Sprache].[Sprache].Members },
TopCount(
EXISTING { [Training].[Sprache].CurrentMember * [Training].[Training Text].[Training Text].Members },
10,
[Measures].[Teilnahmen eTraining]
)
), DISPLAY_FOLDER = 'Training' ;
Normally a named set would be automatically visible in Excel Pivot under the dimension you used to create the named set, but it seems that named sets with tuples which have more than one attribute are placed in a separate folder "Sets" in between the measures and dimensions.Additionally in the SSAS cube browser this named set is not visible at all.Is there any way to tell the named set in which dimension it should appear or any workaround?
Translate Function
Nov 8, 2007Can i use Translate function in sql server query? How i ll use? In my oracle, i m using a Translate function, but i want to run that in sql server. How to do that?
View 3 Replies View RelatedTranslate Function?
Jul 20, 2005Hi allIs there a TSQL function like Oracle's Translate function? Where:Translate('13,000Miles','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP QRSTUVWXYZ, ',' ') wouldreturn '13000'?I'm trying to extract a number from a string. REPLACE doesn't quite dowhat I want.Thanks
View 1 Replies View RelatedHow To Translate Into SSIS??
Mar 12, 2007Hi everyone,
My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:
sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"
rs.execute sql
WHILE NOT RS.EOF
STUFF
ANOTHER SELECT AND ANOTHER LOOP
...
...
INSERT AND DELETE STUFF
END WHILE
Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.
I don't want to do cursors or something like that with T-SQL.
Thanks in advance for your inputs/help and regards,
Can I Use Translate In Sql Server,is It A Keyword
Oct 3, 2006im trying to find the equivalent of the below oracle code in sql server.But to my knowledge i feel "translate "is not supported by sql server. so please send me the equivalent sql server query for this.SELECT 'http://ploft.com/ploft?y='||translate(comp,' ','+')||',+'||translate(california,'','+')||',+'||ca||'+'||pzINTO :ls_MAP FROM project WHERE portno=:dbno;
View 3 Replies View RelatedTranslate Numbers To Words?
Aug 30, 2013In the below query i need to display the amount in words also. How to do that in sql server 2008 R2?
SELECT ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS[TOTAL_CONCESSION_GIVEN] FROM BILL_MASTER BM
Translate Sql Statement Into SQL-92 Standards
Jul 20, 2005Can anyone help me translate this statement from using the legacyouter joins to the SQL-92 standards?Select CA.* From Customer C, Shipper S, Customer_Order CO,Cust_Address CA Where CA.Customer_ID =* CO.Customer_IDand CA.Addr_No =* isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)and C.ID = CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Try as I may, I simply can't find a working left, right, or full outerjoin statement that would give me the same results as the abovestatement gives. I thought this was suppose to work but don't knowwhy it doesn't. Anybody care to try or perhaps tell me why thestatement below doesn't work:Select CA.* From Customer C, ((Customer_Order CO left outer joinCust_Address CA on CA.Customer_ID = CO.Customer_ID) left outer joinShipper S on CA.Addr_No =isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)) Where C.ID =CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Thanks,Tony
View 4 Replies View RelatedHow To Translate Varchar Into Varbinary?
Apr 3, 2007Hi everyone,
We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".
Does anyone have any idea about that?
Thanks for your time and inputs,
How To Translate Oracle Decode
Aug 2, 2006Hi,
How to translate oracle Decode without changed code I mean:
I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I
Wrote this function
select dbo.fnDecode( 1 , 2 , 3 )
first parameter always int, and the others parameters could be char, int or float.
I would like to test first value and return second or third value
-> sql_variant for all parameters, ok
but I can use + or -
I can't do this
select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )
If I put cast ok, but I is there another way, overload this call?
With clr doesn't work, because Sql Server doesn't accept function overload calls from C#
Any ideia????????????????????
cheers,
How To Translate DTSStepScriptResult_DontExecuteTask Into SSIS?
Mar 15, 2007Hi everyone,
We're struggling ourselves with this and we are stuck.
Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?
Main = DTSStepScriptResult_ExecuteTask
Thanks for your input and regards,
How To Internationalize And Translate RDL Files
Aug 10, 2007Hello,
Im new to this forum and my knowledge about RDL is very limited. We are developing software localization tools and some of our customers have requested a solution to translate RDL files.
Our tool has a generic XML parser so I reviewed the RDL documentation but didnt found any information about which XML elements do have translatable content. If there anyone who knows this or is there any documentation around describing which elements must be translated?
I also saw some proposals regarding the internationalization of RDL files. It seems that the proposed solutions were designed for developers and not for professional translators. In an environment where you have to translate several hundreds of RDL files into lets say 20 languages you cant expect that translators start to code RDL files. Are there any other approaches that can be used for a more efficient translation of RDL files?
Any feedback is appreciated.
Achim Herrmann
Translate DSTX File
Dec 4, 2007Is it possible to translate a DTSX/XML file into VB.Net or C# code, like you can do with a WSDL file? If so, is there a SSIS utility or third party available? Thank you.
JGL
Translate Equivalent In Sql Sever
Apr 24, 2008
Hi,
I have an alphanumeic string as one of the column values.I want to get numeic value out of the string.
I was planning on translating all the characters of the alphanumeric sting to spaces and then using ltrim and rtrim on it should give me the required numeric sring.But there is no translate function in sql server
Example: abcd123xy 123
xyz45 45
Can someone provide me a way to get the numeric value out of the strng.
Thanks
Bobby
Translate Rows To Columns - SQL2000
Apr 30, 2008Hi
I want to translate the following
SERIALNO TYPE AMOUNT
1111 1 251111 1 5 1111 1 7 1111 1 3 1111 1 101111 3 1 1111 3 2 1111 3 4 1111 3 3
2222 0 1
2222 0 7
2222 0 1
2222 1 5
2222 1 1
2222 1 2
2222 1 3
to output in the following format
SERIALNO SUM of MIN SUM of MAX
1111 50 10
2222 9 11
For each SERIALNO I want to sum the AMOUNT, spliting the columns into 2 based on TYPE
There are only ever 2 values for TYPE for eachs serial no.
I don;t need to track the value of TYPE.
I assume you would use a MIN & MAX function but Im not sure how to write this into a view.
thanks...
Oracle Translate Function Equivalent In SQL Server
May 5, 2006Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
How To Compile/translate A SSIS Package In Japanese
Jan 16, 2007Hi,
I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?
Thanks in advance.
Translate This Stored Procedure Call To DDL Statement
Jan 30, 2008 Hi,
to fully support SQL server 2005, I'd like to use DDL statements instead of deprecated stored procdure calls, I'm having trouble "translating" this one :
sp_fulltext_catalog 'catalogName', 'start_incremental';
The only option available with ALTER FULLTEXT CATALOG are REORGANIZE and REBUILD.
Any idea ?
Thanks for your help
Oracle Translate Function Equivalent In SQL Server
May 5, 2006Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
Outer Join Oracle Query Translate In Sql Server
Apr 16, 2004Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.
SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1
Any Help will be appreciated.
Transact SQL :: Strategy To Translate Column Data Into Distinct Rows
Aug 27, 2015I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:
PrimaryID SKUChange DateChange LocationIdChange StateChange
10003 TRUE FALSE TRUE FALSE
etc...
The output I'd like to see would be like this:
PrimaryID Field Changed Previous Value New Value
10003 SKUName SKU12345 SKU56789
10003 LocationId Den123 NYC987
etc...
The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.
Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error
Mar 5, 2015I 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.
Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?
May 13, 2014I 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,
Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services
Jun 12, 2015How 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 ?
Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?
Feb 18, 2013It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies View RelatedAnalysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?
Nov 24, 2015I 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.
Analysis :: Running Analysis Service In Terms Of Port Usage?
May 30, 2015I 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